注意: 此插件只是在测试环境论证过,如若在生产环境使用,请自行评估风险
参考:
https://mp.weixin.qq.com/s/uU-D6vdv6Nsi3xP1QRpRww

系统版本:
CentOS Linux release 7.3.1611 (Core)

准备工作:

★进入gpadmin用户

  1. >#su - gpadmin

★获取postgis包

  1. >$ git clone https://github.com/greenplum-db/geospatial

★进入postgis

  1. >$ cd geospatial/postgis/build/postgis-2.1.5/

★安装第三方库文件
install gdal 1.11.1:

  1. wget http://download.osgeo.org/gdal/1.11.1/gdal-1.11.1.tar.gz
  2. tar zxf gdal-1.11.1.tar.gz
  3. cd gdal-1.11.1
  4. ./configure --prefix=$GPHOME (or other place you want, default is /usr/local)
  5. make
  6. make install

install geos-3.4.2:

  1. wget http://download.osgeo.org/geos/geos-3.4.2.tar.bz2
  2. tar jxvf geos-3.4.2.tar.bz2
  3. ./configure --prefix=$GPHOME
  4. make
  5. make install

install proj-4.8.0:

  1. wget http://download.osgeo.org/proj/proj-4.8.0.tar.gz
  2. tar zxvf proj-4.8.0.tar.gz
  3. ./configure --prefix=$GPHOME
  4. make
  5. make install

★编译

  1. >$ ./configure --with-pgconfig=$GPHOME/bin/pg_config --with-raster --without-topology --prefix=$GPHOME --with-projdir=$GPHOME
  1. PostGIS is now configured for x86_64-unknown-linux-gnu
  2. -------------- Compiler Info -------------
  3. C compiler: gcc -g -O2
  4. C++ compiler: g++ -g -O2
  5. SQL preprocessor: /bin/cpp -w -traditional-cpp -P
  6. -------------- Dependencies --------------
  7. GEOS config: /home/gpadmin/gpdb/bin/geos-config
  8. GEOS version: 3.4.2
  9. GDAL config: /home/gpadmin/gpdb/bin/gdal-config
  10. GDAL version: 1.11.1
  11. PostgreSQL config: /home/gpadmin/gpdb/bin/pg_config
  12. PostgreSQL version: PostgreSQL 8.3.23
  13. PROJ4 version: 48
  14. Libxml2 config: /bin/xml2-config
  15. Libxml2 version: 2.9.1
  16. JSON-C support: yes
  17. PostGIS debug level: 0
  18. Perl: /bin/perl
  19. --------------- Extensions ---------------
  20. PostGIS Raster: enabled
  21. PostGIS Topology: disabled
  22. SFCGAL support: disabled
  23. -------- Documentation Generation --------
  24. xsltproc: /bin/xsltproc
  25. xsl style sheets:
  26. dblatex:
  27. convert:
  28. mathml2.dtd: http://www.w3.org/Math/DTD/mathml2/mathml2.dtd

★安装

  1. >$ make USE_PGXS=1 clean all install
  2. >$ make USE_PGXS=1 clean all
  3. >$ make USE_PGXS=1 install

★导入环境变量

  1. >$ export GDAL_DATA=$GPHOME/share/gdal
  2. >$ export POSTGIS_ENABLE_OUTDB_RASTERS=0
  3. >$ export POSTGIS_GDAL_ENABLED_DRIVERS=DISABLE_ALL

★重启数据库

  1. >$ gpstop -a
  2. >$ gpstart -a

★激活PostGIS

  1. >$ psql -d mydatabase -f ${GPHOME}/share/postgresql/contrib/postgis-2.1/postgis.sql
  2. >$ psql -d mydatabase -f ${GPHOME}/share/postgresql/contrib/postgis-2.1/postgis_comments.sql
  3. >$ psql -d mydatabase -f ${GPHOME}/share/postgresql/contrib/postgis-2.1/rtpostgis.sql
  4. >$ psql -d mydatabase -f ${GPHOME}/share/postgresql/contrib/postgis-2.1/raster_comments.sql
  5. >$ psql -d mydatabase -f ${GPHOME}/share/postgresql/contrib/postgis-2.1/spatial_ref_sys.sql

★PostGIS版本

  1. mydatabase=# select postgis_version();

★PostGIS简单使用
参考: https://blog.csdn.net/gyfang/article/details/11661575

  1. mydatabase=# SELECT srid,auth_name,proj4text FROM spatial_ref_sys LIMIT 10;

手工建立空间数据表格

  1. mydatabase=# CREATE TABLE cities ( id int4, name varchar(50) );
  2. mydatabase=# SELECT AddGeometryColumn ('cities', 'the_geom', 4326, 'POINT', 2);
  3. mydatabase=# select * from cities ;

为添加记录,需要使用 SQL 命令。对于空间栏,使用 PostGIS 的 ST_GeomFromText 可以将文本转化为坐标与参考系号的记录:

  1. mydatabase=# INSERT INTO cities (id, the_geom, name) VALUES (1,ST_GeomFromText('POINT(-0.1257 51.508)',4326),'London, England');
  2. mydatabase=# INSERT INTO cities (id, the_geom, name) VALUES (2,ST_GeomFromText('POINT(-81.233 42.983)',4326),'London, Ontario');
  3. mydatabase=# INSERT INTO cities (id, the_geom, name) VALUES (3,ST_GeomFromText('POINT(27.91162491 -33.01529)',4326),'East London,SA');
  4. mydatabase=# select * from cities ;

空间查询:
这里的坐标是无法阅读的 16 进制格式。要以 WKT 文本显示,使用 ST_AsText(the_geom) 或 ST_AsEwkt(the_geom) 函数。也可以使用 ST_X(the_geom) 和 ST_Y(the_geom) 显示一个维度的坐标:

  1. mydatabase=# SELECT p1.name,p2.name,ST_Distance_Sphere(p1.the_geom,p2.the_geom) FROM cities AS p1, cities AS p2 WHERE p1.id > p2.id;

输出显示了距离数据。注意 ‘WHERE’ 部分防止了输出城市到自身的距离(0)或者两个城市不同排列的距离数据(London, England 到 London, Ontario 和 London, Ontario 到 London, England 的距离是一样的)。尝试取消 ‘WHERE’ 并查看结果。
这里采取不同的椭球参数(椭球体名、半主轴长、扁率)计算:

  1. mydatabase=# SELECT p1.name,p2.name,ST_Distance_Spheroid(
  2. p1.the_geom,p2.the_geom, 'SPHEROID["GRS_1980",6378137,298.257222]'
  3. )
  4. FROM cities AS p1, cities AS p2 WHERE p1.id > p2.id;

0 评论  
添加一条新评论