「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组件：

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