本次我们就从能找到的一些基础来做一些 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,对于在一个范围内的查询非常有用。