「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