2011年5月4日 星期三

[記事] PostgreSQL/PostGIS fast installation

安裝 PostgreSQL
# wget http://wwwmaster.postgresql.org/download/mirrors-ftp/source/v9.0.4/postgresql-9.0.4.tar.gz
# tar zxvf postgresql-9.0.4.tar.gz
# cd postgresql-9.0.4
# ./configure --prefix=/home/pgsql
# make; make install
# cd ..

安裝 geos
# wget http://download.osgeo.org/geos/geos-3.2.2.tar.bz2
# bzip2 -d geos-3.2.2.tar.bz2
# tar xvf geos-3.2.2.tar
# cd geos-3.2.2
# ./configure --prefix=/home/pgsql/3party/geos
# make; make install
# cd ..

安裝  proj
# wget http://download.osgeo.org/proj/proj-4.7.0.tar.gz
# tar zxvf proj-4.7.0.tar.gz
# cd proj-4.7.0
# ./configure --prefix=/home/pgsql/3party/proj
# make; make install
# cd ..

安裝  PostGIS
# wget http://postgis.refractions.net/download/postgis-1.5.2.tar.gz
# tar zxvf postgis-1.5.2.tar.gz
# cd postgis-1.5.2

# ./configure --with-pgconfig=/home/pgsql/bin/pg_config --with-geosconfig=/home/pgsql/3party/geos/bin/geos-config --with-projdir=/home/pgsql/3party/proj

  PostGIS is now configured for x86_64-unknown-linux-gnu

 -------------- Compiler Info -------------
  C compiler:           gcc -g -O2
  C++ compiler:         g++ -g -O2

 -------------- Dependencies --------------
  GEOS config:          /home/pgsql/3party/geos/bin/geos-config
  GEOS version:         3.2.2
  PostgreSQL config:    /home/pgsql/bin/pg_config
  PostgreSQL version:   PostgreSQL 9.0.4
  PROJ4 version:        47
  Libxml2 config:       /usr/bin/xml2-config
  Libxml2 version:      2.6.26
  PostGIS debug level:  0

 -------- Documentation Generation --------
  xsltproc:             /usr/bin/xsltproc
  xsl style sheets:
  dblatex:
  convert:              /usr/bin/convert

# make; make install

建立 postgresql 的使用者及相關設定
# useradd pgsql

# mkdir -p /home/logs/pgsql
# chown -R pgsql /home/logs/pgsql

# mkdir -p /home/pgsql/data/
# /home/pgsql/bin/initdb -D /home/pgsql/data/
# /home/pgsql/bin/pg_ctl -D /home/pgsql/data -l /home/logs/pgsql/db.log start
# /home/pgsql/bin/createdb test
# /home/pgsql/bin/createlang plpgsql test

設定 proj 及geos 的library path,不然執行下面 postgis的sql會噴錯誤訊息

# vi /etc/ld.so.conf
include ld.so.conf.d/*.conf
/home/pgsql/lib/
/home/pgsql/3party/proj/lib
/home/pgsql/3party/geos/lib


# ldconfig

安裝 postgis 相關函式及資料型態等等
# /home/pgsql/bin/psql -d test -f /home/pgsql/share/contrib/postgis-1.5/postgis.sql
# /home/pgsql/bin/psql -d test -f /home/pgsql/share/contrib/postgis-1.5/spatial_ref_sys.sql

測試是否有裝進去
# /home/pgsql/bin/psql -d test -c "\dfn '*AddGeo*'"

測試是否可以建立  spatial 資料
# su - pgsql
# psql -d test
test=# create table test_geo (id serial not null);
NOTICE:  CREATE TABLE will create implicit sequence "test_geo_id_seq" for serial column "test_geo.id"
CREATE TABLE

test=# SELECT AddGeometryColumn('test_geo','geom',4326,'POINT',2);
                 addgeometrycolumn
---------------------------------------------------
 public.test_geo.geom SRID:4326 TYPE:POINT DIMS:2
(1 row)

test=#  INSERT INTO test_geo (geom) VALUES( GeomFromText( 'POINT(121.010829 25.03226506496702)',4326 ) );
INSERT 0 1

安裝MID/MIF 匯入工具 : FWTool
# cd /home
# wget http://home.gdal.org/fwtools/FWTools-linux-2.0.6.tar.gz
# tar zxvf FWTools-linux-2.0.6.tar.gz
# mv FWTools-2.0.6 FWTools

# vi /etc/ld.so.conf
include ld.so.conf.d/*.conf
/home/pgsql/lib/
/home/pgsql/3party/proj/lib
/home/pgsql/3party/geos/lib
/home//FWTools/lib/

# /home/FWTools/bin/ogr2ogr -h

匯入範例
# export PGCLIENTENCODING=BIG5
# /home/FWTools/bin/ogr2ogr -f "PostgreSQL" PG:"host=localhost user=pgsql dbname=test" /tmp/test.mif

Notes,
如果裝了CentOS內預設的MySQL又依上面那樣設 /etc/ld.so.conf 那 ogr2ogr 會噴
/home/FWTools/bin/ogr2ogr: relocation error: /home//FWTools/lib/libgdal.so: symbol __divdi3, version libmysqlclient_15 not defined in file libmysqlclient.so.15 with link time reference
看一下 link 到的library 會是

# ldd /home/FWTools/bin/ogr2ogr | grep mysqlclient
        libmysqlclient.so.15 => /usr/lib/mysql/libmysqlclient.so.15 (0xf6b06000)

懶人解決方 :
# export LD_LIBRARY_PATH=/home/FWTools/lib/:$LD_LIBRARY_PATH
# ldd /home/FWTools/bin/ogr2ogr | grep mysqlclient
        libmysqlclient.so.15 => /home/FWTools/lib/libmysqlclient.so.15 (0xf6c43000)

# /home/FWTools/bin/ogr2ogr -h

Usage: ogr2ogr [--help-general] [-skipfailures] [-append] [-update]
               [-select field_list] [-where restricted_where]
               [-sql <sql statement>]
               [-spat xmin ymin xmax ymax] [-preserve_fid] [-fid FID]
               [-a_srs srs_def] [-t_srs srs_def] [-s_srs srs_def]
               [-f format_name] [-overwrite] [[-dsco NAME=VALUE] ...]
               dst_datasource_name src_datasource_name
               [-lco NAME=VALUE] [-nln name] [-nlt type] [layer [layer ...]]

 -f format_name: output file format name, possible values are:
     -f "ESRI Shapefile"
     -f "MapInfo File"
     -f "TIGER"
     -f "S57"
     -f "DGN"
     -f "Memory"
     -f "BNA"
     -f "CSV"
     -f "GML"
     -f "GPX"
     -f "KML"
     -f "GeoJSON"
     -f "Interlis 1"
     -f "Interlis 2"
     -f "GMT"
     -f "PostgreSQL"
     -f "MySQL"
     -f "Geoconcept"
 -append: Append to existing layer instead of creating new if it exists
 -overwrite: delete the output layer and recreate it empty
 -update: Open existing output datasource in update mode
 -select field_list: Comma-delimited list of fields from input layer to
                     copy to the new layer (defaults to all)
 -where restricted_where: Attribute query (like SQL WHERE)
 -sql statement: Execute given SQL statement and save result.
 -skipfailures: skip features or layers that fail to convert
 -spat xmin ymin xmax ymax: spatial query extents
 -dsco NAME=VALUE: Dataset creation option (format specific)
 -lco  NAME=VALUE: Layer creation option (format specific)
 -nln name: Assign an alternate name to the new layer
 -nlt type: Force a geometry type for new layer.  One of NONE, GEOMETRY,
      POINT, LINESTRING, POLYGON, GEOMETRYCOLLECTION, MULTIPOINT, MULTILINE,
      MULTIPOLYGON, or MULTILINESTRING.  Add "25D" for 3D layers.
      Default is type of source layer.
 -a_srs srs_def: Assign an output SRS
 -t_srs srs_def: Reproject/transform to this SRS on output
 -s_srs srs_def: Override source SRS

 Srs_def can be a full WKT definition (hard to escape properly),
 or a well known definition (ie. EPSG:4326) or a file with a WKT
 definition.

另外一個一樣是OS內預設 MySQL的問題
預設的MySQL執行後的mysql.sock 是在 /var/lib/mysql/mysql.sock,但ogr2ogr 預設是去找 /tmp/mysql.sock
解決方法 :
export MYSQL_UNIX_PORT=/var/lib/mysql/mysql.sock

Enjoy it! 啊,不對..ogr2ogr import 到mysql 中文亂碼的問題要怎麼解決啊..煩 (抓頭)

Ref :
http://blog.xuite.net/misgarlic/weblogic/21740085
http://blog.xuite.net/misgarlic/weblogic/39899059

0 意見:

張貼留言