在每一個節(jié)點上安裝PostGIS 安裝PostGIS yum源： wget -P /etc/yum.repos.d/ $獲取的repo地址 在oushum1上，通過“hawq scp”命令統(tǒng)一安裝其他節(jié)點的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組件： 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組件，對數(shù)據(jù)庫中數(shù)據(jù)進(jìn)行分析。 安裝確認(rèn) PostGIS默認(rèn)安裝在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 |
|
應(yīng)用舉例 首先建立一個常規(guī)的表格存儲有關(guān)城市（cities）的信息。the_geom表示存儲二維空間坐標(biāo)： 1 | CREATE TABLE cities ( id int4, name varchar(50) ,the_geom geometry); |
|
插入城市數(shù)據(jù)： 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'); |
|
標(biāo)準(zhǔn)的 SQL 操作都可以用于 PostGIS 表單，但坐標(biāo)是無法閱讀的16進(jìn)制數(shù) 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) 顯示一個維度的坐標(biāo) 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 |
|
