greenplum中使用postgis的版本是v2.0.3

安装

安装插件

在如下位置下载postgis gppkg
https://network.pivotal.io/products/pivotal-gpdb/#/releases/4540/file_groups/493
安装插件

gppkg -i /home/gpadmin/postgis-ossv2.0.3_pv2.0.1_gpdb4.3orca-rhel5-x86_64.gppkg

注入函数和表

在数据库中注入函数，下面两个sql文件可以在源码中找到https://github.com/greenplum-db/gpdb

psql -d db2 -f postgis.sql
psql -d db2 -f spatial_ref_sys.sql

postgis.sql在安装后的环境gpdb/share/postgresql/contrib/postgis-2.0/postgis.sql可以找到
spatial_ref_sys.sql在源码中可以找到
完成后，可以在数据库db2中看到如下表spatial_ref_sys和另外2张视图

db2=# \d
                   List of relations
 Schema |       Name        | Type  |  Owner  | Storage 
--------+-------------------+-------+---------+---------
 public | geography_columns | view  | gpadmin | none
 public | geometry_columns  | view  | gpadmin | none
 public | spatial_ref_sys   | table | gpadmin | heap
(3 rows)

其中 spatial_ref_sys 存储着合法的空间坐标系统

db2=# SELECT srid,auth_name,proj4text FROM spatial_ref_sys LIMIT 10;
 srid | auth_name |                             proj4text                             
------+-----------+-------------------------------------------------------------------
 3889 | EPSG      | +proj=longlat +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +no_defs 
 4001 | EPSG      | +proj=longlat +ellps=airy +no_defs 
 4009 | EPSG      | +proj=longlat +a=6378450.047548896 +b=6356826.621488444 +no_defs 
 4025 | EPSG      | +proj=longlat +ellps=WGS66 +no_defs 
 4033 | EPSG      | +proj=longlat +a=6378136.3 +b=6356751.616592146 +no_defs 
 4041 | EPSG      | +proj=longlat +a=6378135 +b=6356750.304921594 +no_defs 
 4081 | EPSG      | +proj=longlat +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +no_defs 
 4120 | EPSG      | +proj=longlat +ellps=bessel +no_defs 
 4128 | EPSG      | +proj=longlat +ellps=clrk66 +no_defs 
 4136 | EPSG      | +proj=longlat +ellps=clrk66 +no_defs 
(10 rows)

基础测试

创建表

db2=# CREATE TABLE cities ( id int4, name varchar(50) );
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
db2=# SELECT AddGeometryColumn ('cities', 'the_geom', 4326, 'POINT', 2);
                  addgeometrycolumn                  
-----------------------------------------------------
 public.cities.the_geom SRID:4326 TYPE:POINT DIMS:2 
(1 row)

db2=# SELECT * from cities;
 id | name | the_geom 
----+------+----------
(0 rows)

插入数据并查看

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

db2=# SELECT * FROM cities;
 id |      name       |                      the_geom                      
----+-----------------+----------------------------------------------------
  3 | East London,SA  | 0101000020E610000040AB064060E93B4059FAD005F58140C0
  1 | London, England | 0101000020E6100000BBB88D06F016C0BF1B2FDD2406C14940
  2 | London, Ontario | 0101000020E6100000F4FDD478E94E54C0E7FBA9F1D27D4540
(3 rows)

空间查询

多数空间操作以 ST（spatial type）开头，以米为单位并假设地球是完美椭球，上面三个城市相互的距离是多少？

db2=# 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;
      name       |      name       | st_distance_sphere 
-----------------+-----------------+--------------------
 East London,SA  | London, England |   9789680.59961472
 East London,SA  | London, Ontario |   13892208.6782928
 London, Ontario | London, England |   5875787.03777356
(3 rows)

参考：http://blog.csdn.net/gyfang/article/details/11661575

转载自：https://blog.csdn.net/micklf/article/details/77479152