(转)postgresql+postgis空间数据库使用总结
转载地址:https://blog.csdn.net/qq_36588972/article/details/78902195
参考资料:
pgrouting路径导航
在线转换shp和json格式图形化工具
pgsql语法
pgsql几何类型的使用
pgsql判断点是否在面内
PostGIS的Geometry数据类型
Geometry可以说是PostGIS最重要的一个概念,是“几何体”的意思,由于PostGIS很
好地遵守OGC的”Simple Feature for Specification for SQL”规范,目前支持的几何体类型包含其实例有WKT格式如下:
POINT(1 1)
MULTIPOINT(1 1, 3 4, -1 3)
LINESTRING(1 1, 2 2, 3 4)
POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))
MULTIPOLYGON((0 0, 0 1, 1 1, 1 0, 0 0), (5 5, 5 6, 6 6, 6 5, 5 5))
MULTILINESTRING((1 1, 2 2, 3 4),(2 2, 3 3, 4 5))
而geometry具体表现又有两种形式,一种叫做WKT(Well Known Text)形式,如上面的
例子。或者使用如下SQL语句浏览:
select AsText(pt) from test1;
另一种叫做“Canonical Form”形式,看上去是一串古怪的数字,其实是一种增强的十六
进制编码,使用如下SQL语句就可以浏览了:
select pt from test1;
EWKT、EWKB和Canonical格式
EWKT和EWKB相比OGC WKT和WKB格式主要的扩展有3DZ、3DM、4D坐标和内嵌空间参考支持。
以下以EWKT语句定义了一些几何对象:
POINT(0 0 0) ——3D点
SRID=32632;POINT(0 0) ——内嵌空间参考的点
POINTM(0 0 0) ——带M值的点
POINT(0 0 0 0) ——带M值的3D点
SRID=4326;MULTIPOINTM(0 0 0,1 2 1) ——内嵌空间参考的带M值的多点
以下语句可以使用EWKT格式插入一个点要素到一个表中:
INSERT INTO table ( SHAPE, NAME )
VALUES ( GeomFromEWKT('SRID=4326;POINTM(116.39 39.9 10)'), '北京' )
Canonical格式是16进制编码的几何对象,直接用SQL语句查询出来的就是这种格式。
postgis3d空间语法以及函数应用:
网上关于postgis的函数有很多,这里简单使用几个我用的到的函数进行测试
在执行几何类型的sql语句的时候一定要注意类型的转换、表空间列的属性或者使用适当的类型,不然执行会报错,下面的sql语句亲测没有任何问题
插入:
insert into SSS(geometryType) values(ST_GeomFromText('POINTZ(116.39 21 39.9)', 4326));
修改:
UPDATE eee SET geometryType = ST_GeomFromText('POINTM(116.39 21 39.9)',4326)
新增:
CREATE TABLE weq (id serial PRIMARY KEY, details VARCHAR(10000), template VARCHAR(255), create_time VARCHAR(255), modeUrl VARCHAR(255), geoJson VARCHAR(255), geometryType geometry(POINTM,4326))
A是否包含B,包含返回t,不包含返回f
SELECT ST_Contains(st_astext(geometrytype),'POINTZ(151 50 30)') FROM videodata
找到 POINT(1000 1000) 100米范围内的查询如下:
SELECT * FROM geotable
WHERE ST_DWithin(geocolumn, ‘POINT(1000 1000)’, 100.0);
查询点在面内,包含返回t,不包含返回f
SELECT ST_Contains(st_astext(ST_MakePolygon(ST_GeomFromText('LINESTRINGZ (0 0 1,0 2 1,2 2 1,2 0 1,0 0 1) '))),'POINTZ(1 1 5)')
或者
SELECT ST_Contains( ST_MakePolygon(ST_GeomFromText(ST_ASTEXT(geometrytype))),st_pointz(0,0,0)) FROM videodata
点是否在面内,包含返回t,不包含返回f
sql语句如下:
select * from tb_data_region_polygon0 where ST_Within(st_geomfromtext('point(114.186684 30.512029)',4326),geom);
select * from tb_data_region_polygon0 where ST_DWithin(st_geomfromtext('point(114.186684 30.512029)',4326),geom,0);
select * from tb_data_region_polygon0 where ST_Covers(geom,st_geomfromtext('point(114.186684 30.512029)',4326));
postgis 中 geomery 使用 ST_distance 计算出来的单位竟然是弧度,不是米,找了一段时间问题,发现是没有使用投影坐标引起的
于是在计算的时候,先将点变成投影坐标,再来计算。
使用ST_Transform(geom, 2346)转换之后就是以米为单位的数据了
结果以米为单位(计算长度):
SELECT ST_Length(ST_Transform(geom, 2346)) FROM ditie
这里的2346中国西安80高丝克里投影的编号,是分带的,使用的中央经线在南京。
加上这个,我们能正确计算出来距离,也是米。
但是如果把应用发到别的城市,比如苏州,就会出现问题,解决方法:
查询ditie表点到每个线的距离,条件是在80000米之内的点
select
(ST_distance(
geom::geography,
ST_GeomFromText('POINT(121 32)', 4326)::geography))
from ditie
where
ST_dwithin(geom::geography,
ST_GeomFromText('POINT(121 32)', 4326)::geography, 80000)
这样把geometry转成geography,转换之后,全球都可以使用了
下面这种办法查出来的结果也是以米为单位的两点间的距离
SELECT ST_Distance(
ST_Transform(ST_GeomFromText('POINT(-87.734087560562 43.770129071141)',4326),26986),
ST_Transform(ST_GeomFromText('POINT(-87.747382933006 43.759234252055)', 4326),26986)
);
在项目中,我们也可以直接把坐标存成 geography格式的,这样就能避免数据转来转去的了:
ST_GeographyFromText('SRID=4326;POINT(-110 30)')
改变表的空间字段srid为4326
update table_name set the_geom = st_geomfromtext(ST_AsText(the_geom),4326)