安装PostGIS (可选)

在每一个节点上安装PostGIS

安装PostGIS yum源:

wget -P /etc/yum.repos.d/ http://yum.oushu-tech.com/oushurepo/yumrepo/release/postgis/centos7/1.2.3/oushu-postgis.repo

在oushum1上,通过“hawq scp”命令统一安装其他节点的YUM源:

source /usr/local/hawq/greenplum_path.sh


hawq scp -f hostfile /etc/yum.repos.d/oushu-postgis.repo =:/etc/yum.repos.d

安装PostGIS:

hawq ssh -f hostfile -e 'yum install -y oushu-postgis'

注册PostGIS组件

在需要的database上注册PostGIS组件:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
su - gpadmin

source /usr/local/hawq/greenplum_path.sh

psql -d postgres -f $GPHOME/share/postgresql/contrib/postgis-2.0/postgis.sql

psql -d postgres -f $GPHOME/share/postgresql/contrib/postgis-2.0/postgis_comments.sql

psql -d postgres -f $GPHOME/share/postgresql/contrib/postgis-2.0/spatial_ref_sys.sql

psql -d postgres -f $GPHOME/share/postgresql/contrib/postgis-2.0/legacy.sql

psql -d postgres -f $GPHOME/share/postgresql/contrib/postgis-2.0/legacy_minimal.sql

注册完成后,即可使用PostGIS组件,对数据库中数据进行分析。

安装确认

PostGIS默认安装在public schema下:

1
2
3
4
5
6
7
8
9
postgres=# \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 | append only

应用举例

首先建立一个常规的表格存储有关城市(cities)的信息。the_geom表示存储二维空间坐标:

1
CREATE TABLE cities ( id int4, name varchar(50) ,the_geom geometry);

插入城市数据:

1
2
3
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');

标准的 SQL 操作都可以用于 PostGIS 表单,但坐标是无法阅读的16进制数

1
2
3
4
5
6
7
8
SELECT * FROM cities;

 id |      name       |                      the_geom
----+-----------------+------------------------------------------------------
  1 | London, England | 0101000020E6100000BBB88D06F016C0BF1B2FDD2406C14940
  2 | London, Ontario | 0101000020E6100000F4FDD478E94E54C0E7FBA9F1D27D4540
  3 | East London,SA  | 0101000020E610000040AB064060E93B4059FAD005F58140C0
(3 rows

可以使用 ST_X(the_geom) 和 ST_Y(the_geom) 显示一个维度的坐标

1
2
3
4
5
6
7
8
SELECT id, ST_AsText(the_geom), ST_AsEwkt(the_geom), ST_X(the_geom), ST_Y(the_geom) FROM cities;

 id |          st_astext           |               st_asewkt                |    st_x     |   st_y
----+------------------------------+----------------------------------------+-------------+------------
  2 | POINT(-81.233 42.983)        | SRID=4326;POINT(-81.233 42.983)        |     -81.233 |    42.983
  3 | POINT(27.91162491 -33.01529) | SRID=4326;POINT(27.91162491 -33.01529) | 27.91162491 | -33.01529
  1 | POINT(-0.1257 51.508)        | SRID=4326;POINT(-0.1257 51.508)        |     -0.1257 |    51.508
 (3 rows