Ganos Geometry是对象关系型数据库PostgreSQL的一个空间几何扩展,Ganos Geometry遵循OpenGIS规范,使PostgreSQL增加了存储和管理2D(X,Y)、3D(X,Y,Z)、4D(X,Y,Z,M)空间几何数据的能力,并提供了空间几何对象、索引、函数和操作符等丰富功能。
概述
几何模型完全兼容PostGIS接口,支持已有应用的平滑迁移。
快速入门
- 创建扩展 12345678
--创建几何扩展
Create extension ganos_geometry cascade;
--创建几何拓扑扩展
Create extension ganos_geometry_topology;
--创建sfcgal插件扩展
Create extension ganos_geometry_sfcgal;
- 创建几何表 123456
--方式一:直接创建带geometry字段的表
CREATE TABLE ROADS ( ID int4, ROAD_NAME varchar(
25
), geom geometry(LINESTRING,
3857
) );
--方式二:先创建普通表,再附加几何字段
CREATE TABLE ROADS ( ID int4, ROAD_NAME varchar(
25
) );
SELECT AddGeometryColumn(
'roads'
,
'geom'
,
3857
,
'LINESTRING'
,
2
);
- 添加几何约束 1
ALTER TABLE ROADS ADD CONSTRAINT geometry_valid_check CHECK (ST_IsValid(geom));
- 导入几何数据 123456789101112
INSERT INTO roads (id, geom, road_name)
VALUES (
1
,ST_GeomFromText(
'LINESTRING(191232 243118,191108 243242)'
,
3857
),
'北五环'
);
INSERT INTO roads (id, geom, road_name)
VALUES (
2
,ST_GeomFromText(
'LINESTRING(189141 244158,189265 244817)'
,
3857
),
'东五环'
);
INSERT INTO roads (id, geom, road_name)
VALUES (
3
,ST_GeomFromText(
'LINESTRING(192783 228138,192612 229814)'
,
3857
),
'南五环'
);
INSERT INTO roads (id, geom, road_name)
VALUES (
4
,ST_GeomFromText(
'LINESTRING(189412 252431,189631 259122)'
,
3857
),
'西五环'
);
INSERT INTO roads (id, geom, road_name)
VALUES (
5
,ST_GeomFromText(
'LINESTRING(190131 224148,190871 228134)'
,
3857
),
'东长安街'
);
INSERT INTO roads (id, geom, road_name)
VALUES (
6
,ST_GeomFromText(
'LINESTRING(198231 263418,198213 268322)'
,
3857
),
'西长安街'
);
- 查询几何对象信息 123456789101112
SELECT id, ST_AsText(geom) AS geom, road_name FROM roads;
--------------------------------
id | geom | road_name
--------+-----------------------------------------+-----------
1
| LINESTRING(
191232
243118
,
191108
243242
) | 北五环
2
| LINESTRING(
189141
244158
,
189265
244817
) | 东五环
3
| LINESTRING(
192783
228138
,
192612
229814
) | 南五环
4
| LINESTRING(
189412
252431
,
189631
259122
) | 西五环
5
| LINESTRING(
190131
224148
,
190871
228134
) | 东长安街
6
| LINESTRING(
198231
263418
,
198213
268322
) | 西长安街
(
6
rows)
- 创建索引 123456789101112131415
--GiST索引
CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] );
CREATE INDEX [indexname] ON [tablename] USING GIST ([geometryfield] gist_geometry_ops_nd);
VACUUM ANALYZE [table_name] [(column_name)];
--举例
Create INDEX sp_geom_index ON ROADS USING GIST(geom);
VACUUM ANALYZE ROADS (geom);
--创建BRIN索引
CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geometryfield] );
CREATE INDEX [indexname] ON [tablename] USING BRIN ([geometryfield] brin_geometry_inclusion_ops_3d);
CREATE INDEX [indexname] ON [tablename] USING BRIN ([geometryfield] brin_geometry_inclusion_ops_4d);
--创建指定大小的brin索引
CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geometryfield] ) WITH (pages_per_range = [number]);
- 几何对象存取 123456789101112131415161718192021222324
---判断空间几何对象是否是简单要素类型
SELECT ST_IsSimple(ST_GeomFromText(
'POLYGON((1 2, 3 4, 5 6, 1 2))'
));
st_issimple
-------------
t
(
1
row)
SELECT ST_IsSimple(ST_GeomFromText(
'LINESTRING(1 1,2 2,2 3.5,1 3,1 2,2 1)'
));
st_issimple
-------------
f
(
1
row)
--查询地形中拥有环岛且面积最大的城市
SELECT gid, name, ST_Area(the_geom) AS area
FROM bc_municipality
WHERE ST_NRings(the_geom) >
1
ORDER BY area DESC LIMIT
1
;
gid | name | area
-----+--------------+------------------
12
| 安宁市 |
257374619.430216
(
1
row)
- 空间测量、空间分析和空间关系判断 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110
--创建表bc_roads
Create table bc_roads (gid serial, name varchar, the_geom geometry);
--创建表bc_municipality
Create table bc_municipality(gid serial, code integer, name varchar, the_geom geometry);
--长度计算
SELECT sum(ST_Length(the_geom))/
1000
AS km_roads FROM bc_roads;
km_roads
------------------
70842.1243039643
(
1
row)
--面积计算
SELECT ST_Area(the_geom)/
10000
AS hectares FROM bc_municipality WHERE name =
'PRINCE GEORGE'
;
hectares
------------------
32657.9103824927
(
1
row)
--使用ST_Contains函数
SELECT m.name, sum(ST_Length(r.the_geom))/
1000
as roads_km
FROM
bc_roads AS r, bc_municipality AS m
WHERE
ST_Contains(m.the_geom,r.the_geom)
GROUP BY m.name
ORDER BY roads_km;
name | roads_km
----------------------------+------------------
SURREY |
1539.47553551242
VANCOUVER |
1450.33093486576
LANGLEY DISTRICT |
833.793392535662
BURNABY |
773.769091404338
PRINCE GEORGE |
694.37554369147
--使用ST_Covers函数
SELECT ST_Covers(smallc,smallc) As smallinsmall,
ST_Covers(smallc, bigc) As smallcoversbig,
ST_Covers(bigc, ST_ExteriorRing(bigc)) As bigcoversexterior,
ST_Contains(bigc, ST_ExteriorRing(bigc)) As bigcontainsexterior
FROM (SELECT ST_Buffer(ST_GeomFromText(
'POINT(1 2)'
),
10
) As smallc,
ST_Buffer(ST_GeomFromText(
'POINT(1 2)'
),
20
) As bigc) As foo;
--Result
smallinsmall | smallcoversbig | bigcoversexterior | bigcontainsexterior
--------------+----------------+-------------------+---------------------
t | f | t | f
(
1
row)
--使用ST_Disjoint函数
SELECT ST_Disjoint(
'POINT(0 0)'
::geometry,
'LINESTRING ( 2 0, 0 2 )'
::geometry);
st_disjoint
---------------
t
(
1
row)
SELECT ST_Disjoint(
'POINT(0 0)'
::geometry,
'LINESTRING ( 0 0, 0 2 )'
::geometry);
st_disjoint
---------------
f
(
1
row)
--使用ST_Overlaps函数
SELECT ST_Overlaps(a,b) As a_overlap_b,
ST_Crosses(a,b) As a_crosses_b,
ST_Intersects(a, b) As a_intersects_b, ST_Contains(b,a) As b_contains_a
FROM (SELECT ST_GeomFromText(
'POINT(1 0.5)'
) As a, ST_GeomFromText(
'LINESTRING(1 0, 1 1, 3 5)'
) As b)
As foo
a_overlap_b | a_crosses_b | a_intersects_b | b_contains_a
------------+-------------+----------------+--------------
f | f | t | t
--使用ST_Relate函数
SELECT ST_Relate(ST_GeometryFromText(
'POINT(1 2)'
), ST_Buffer(ST_GeometryFromText(
'POINT(1 2)'
),
2
),
'0FFFFF212'
);
st_relate
-----------
t
--使用ST_Touches函数
SELECT ST_Touches(
'LINESTRING(0 0, 1 1, 0 2)'
::geometry,
'POINT(1 1)'
::geometry);
st_touches
------------
f
(
1
row)
SELECT ST_Touches(
'LINESTRING(0 0, 1 1, 0 2)'
::geometry,
'POINT(0 2)'
::geometry);
st_touches
------------
t
(
1
row)
--使用ST_Within函数
SELECT ST_Within(smallc,smallc) As smallinsmall,
ST_Within(smallc, bigc) As smallinbig,
ST_Within(bigc,smallc) As biginsmall,
ST_Within(ST_Union(smallc, bigc), bigc) as unioninbig,
ST_Within(bigc, ST_Union(smallc, bigc)) as biginunion,
ST_Equals(bigc, ST_Union(smallc, bigc)) as bigisunion
FROM
(
SELECT ST_Buffer(ST_GeomFromText(
'POINT(50 50)'
),
20
) As smallc,
ST_Buffer(ST_GeomFromText(
'POINT(50 50)'
),
40
) As bigc) As foo;
--Result
smallinsmall | smallinbig | biginsmall | unioninbig | biginunion | bigisunion
--------------+------------+------------+------------+------------+------------
t | t | f | t | t | t
(
1
row)
- 删除扩展 1
Drop extension ganos_geometry cascade;
SQL参考
详细SQL手册请参见PostGIS官方手册。