PostgreSQL 技术之家: PostGIS for Greenplum安装

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

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

准备工作:

★进入gpadmin用户

>#su - gpadmin

★获取postgis包

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

★进入postgis

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

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

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

install geos-3.4.2:

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

install proj-4.8.0:

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

★编译

>$ ./configure --with-pgconfig=$GPHOME/bin/pg_config --with-raster --without-topology --prefix=$GPHOME --with-projdir=$GPHOME

  PostGIS is now configured for x86_64-unknown-linux-gnu
 -------------- Compiler Info ------------- 
  C compiler:           gcc -g -O2
  C++ compiler:         g++ -g -O2
  SQL preprocessor:     /bin/cpp -w -traditional-cpp -P
 -------------- Dependencies -------------- 
  GEOS config:          /home/gpadmin/gpdb/bin/geos-config
  GEOS version:         3.4.2
  GDAL config:          /home/gpadmin/gpdb/bin/gdal-config
  GDAL version:         1.11.1
  PostgreSQL config:    /home/gpadmin/gpdb/bin/pg_config
  PostgreSQL version:   PostgreSQL 8.3.23
  PROJ4 version:        48
  Libxml2 config:       /bin/xml2-config
  Libxml2 version:      2.9.1
  JSON-C support:       yes
  PostGIS debug level:  0
  Perl:                 /bin/perl
 --------------- Extensions --------------- 
  PostGIS Raster:       enabled
  PostGIS Topology:     disabled
  SFCGAL support:       disabled
 -------- Documentation Generation -------- 
  xsltproc:             /bin/xsltproc
  xsl style sheets:     
  dblatex:              
  convert:              
  mathml2.dtd:          http://www.w3.org/Math/DTD/mathml2/mathml2.dtd

★安装

>$ make USE_PGXS=1 clean all install
或
>$ make USE_PGXS=1 clean all
>$ make USE_PGXS=1  install

★导入环境变量

>$ export GDAL_DATA=$GPHOME/share/gdal
>$ export POSTGIS_ENABLE_OUTDB_RASTERS=0
>$ export POSTGIS_GDAL_ENABLED_DRIVERS=DISABLE_ALL

★重启数据库

>$ gpstop  -a
>$ gpstart -a

★激活PostGIS

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

★PostGIS版本

mydatabase=# select postgis_version();

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

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

手工建立空间数据表格

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

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

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

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

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’ 并查看结果。
这里采取不同的椭球参数（椭球体名、半主轴长、扁率）计算：

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

PostGIS for Greenplum安装

用户登录