HashData之PostGis实验（一）

本次我们就从能找到的一些基础来做一些 PostGis 熟悉性的实验。

首先说明一下添加 postgis 插件的方法：

• 切换到HashData管理用户下，默认情况下是（gpadmin）
• 执行以下 SQL：

psql -d <DATABASE_NAME> -f /opt/gpsql/share/postgresql/contrib/postgis-2.1/postgis.sql
psql -d <DATABASE_NAME> -f /opt/gpsql/share/postgresql/contrib/postgis-2.1/spatial_ref_sys.sql
psql -d <DATABASE_NAME> -f /opt/gpsql/share/postgresql/contrib/postgis-2.1/rtpostgis.sql

执行完以上两步之后就可以在 HashData 中使用 PostGis 相关的功能了。

执行之后你会在数据库中发现多了两个视图一个表：

warehouse=# \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 | heap
(3 rows)

spatial_ref_sys：存储着合法的空间坐标系统。
geometry_columns：存储数据库中有空间信息的列。点到点的直线
geography_columns：存储数据库中有地理数据类型的列。点到点的弧线

geography 空间索引将正确地处理覆盖极点或国际日期变更线的要素的查询，而 geometry 空间索引则不会。

实验一

-1- 创建表cities，包含编号和名称

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

-2- 增加存储空间位置的列，它记录了数据类型（点），维度（二维），及空间坐标系统（EPSG:4326）。

SELECT AddGeometryColumn ('cities', 'the_geom', 4326, 'POINT', 2);

-3- 添加数据，其中，ST_GeomFromText可以将文本转化为坐标与参考系号。

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');

-4- 查询数据。

warehouse=# select * from cities;
 id |      name       |                      the_geom
----+-----------------+----------------------------------------------------
  3 | East London,SA  | 0101000020E610000040AB064060E93B4059FAD005F58140C0
  1 | London, England | 0101000020E6100000BBB88D06F016C0BF1B2FDD2406C14940
  2 | London, Ontario | 0101000020E6100000F4FDD478E94E54C0E7FBA9F1D27D4540
(3 rows)

–4.1–由于列 the_geom 以16进制表示，不方便阅读。可以使用 ST_AsText(the_geom) 或ST_AsEwkt(the_geom) 函数显示坐标。也可以使用 ST_X(the_geom) 和 ST_Y(the_geom) 显示一个维度的坐标。

warehouse=# 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
----+------------------------------+----------------------------------------+-------------+-----------
  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
  2 | POINT(-81.233 42.983)        | SRID=4326;POINT(-81.233 42.983)        |     -81.233 |    42.983
(3 rows)

-5- 计算城市间的距离。其中，where 条件是为了防止输出城市到自身的距离（0）或者两个城市不同排列的距离数据。单位为米

warehouse=# SELECT p1.name,p2.name,ST_Distance_Sphere(p1.the_geom,p2.the_geom) FROM cities AS p1, cities AS p2 WHERE p1.id > p2.id;
      name       |      name       | st_distance_sphere
-----------------+-----------------+--------------------
 London, Ontario | London, England |   5875787.03777356
 East London,SA  | London, England |   9789680.59961472
 East London,SA  | London, Ontario |   13892208.6782928
(3 rows)

相关函数说明

AddGeometryColumn(<schema_name>, <table_name>, <column_name>, , , )
作用：给一个已存在属性数据表增加一个几何字段(geomtry column)。

• schema_name 指表的模式的名字，
• srid 必须是一个整数指对应于 SPATIAL_REF_SYS 表，
• type必须是一个大写的字符串，用来描述几何类型，例如：‘POLYGON’ 或者 ‘MULTILINESTRING’。
• dimension 坐标维度编码：0 = 点 | 1 = 线 | 2 = 面

DropGeometryColumn(<schema_name>, <table_name>, <column_name>)
从一个空间数据表中删除一个几何字段。
ST_SetSRID(geometry, integer)
给一个几何对象(geometry)设置一个整型的SRID，对于在一个范围内的查询非常有用。