「OushuDB」命令行安装之 PostGIS

在每一个节点上安装PostGIS
安装PostGIS yum源：

wget -P /etc/yum.repos.d/ $获取的repo地址

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

source /usr/local/hawq/greenplum_path.shhawq 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组件：

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下：

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表示存储二维空间坐标：

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

插入城市数据：

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进制数

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) 显示一个维度的坐标

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