55  
Postgresql,postGis 数据库范围查询数据
作者: 井沿博 于 2022年05月19日 发布在分类 / 人防组 / 人防后端 下,并于 2022年05月20日 编辑

PostGis 常用函数

postGis 是Postgresql 的扩展,内置了许多有助与地理信息操作的数据库函数,主要使用的函数包括:


  1. ST_Centroid:获取范围中心面
  2. st_disjoint:确认区域是否相接
  3. st_intersects:确认区域是否相交
  4. st_distance:两点之间距离
  5. st_dwithin:长度距离内是否包含某面
  6. st_touches:两面是否接触
  7. st_overlaps:两面是否重叠
  8. st_area:面面积
  9. st_length:面长度
  10. st_numpoints:面点数
  11. st_geometrytype:面类型
  12. st_astext:面数据转为坐标系维度数据
  13. st_srid:查看面坐标系数
  14.  st_isclosed:面是否闭合
  15. st_isempty:面是否为空
  16. st_isring:判断起始点和终点坐标是否相同
  17. st_isvalid:面是否可用
  18. st_issimple:面是否有特殊点
  19. st_exteriorRing:提取面边界
  20. st_polygonize:根据线坐标构建面,不好用
  21. ST_IsValidDetail:判断数据有效性
  22. UpdateGeometrySRID:修改面坐标系
  23. st_buffer:计算范围缓冲
  24. 面数据格式:geometry

使用实例

--select * from planet_osm_polygon where name like '%区%'
--SELECT st_disjoint(a.way, b.way) from planet_osm_polygon a,planet_osm_polygon  b where a.name='南山区'  and b.name='福田区';
 
--SELECT st_disjoint(a.way, b.way) from planet_osm_polygon a,planet_osm_polygon  b where a.name='南山区'  and b.name='天心区';
2. 是否相交
--SELECT st_intersects(a.way, b.way) FROM planet_osm_polygon a,planet_osm_polygon b where a.name='南山区'  and b.name='福田区';

--SELECT st_intersects(a.way, b.way) from planet_osm_polygon a,planet_osm_polygon  b where a.name='南山区'  and b.name='天心区';
3. 求距离
--SELECT st_distance(a.geom, b.geom) FROM china a,china b where a.name='海淀区'  and b."name"='朝阳区';
SELECT st_distance(a.way, b.way) FROM planet_osm_polygon a,planet_osm_polygon b where a.name='南山区'  and b.name='福田区';
4. 是否距离包含
--SELECT st_dwithin(a.geom, b.geom,2) FROM china a,china b where a.name='海淀区'  and b."name"='朝阳区';
5. 是否接触
--SELECT st_touches(a.geom, b.geom) FROM china a,china b where a.name='海淀区'  and b."name"='石景山区';
6. 是否重叠
--SELECT st_overlaps(a.geom, b.geom) FROM china a,china b where a.name='丰台区'  and b."name"='海淀区';
7. 求面积
--Geometry Accessors查询
--SELECT st_area(geom) FROM china where china.name='丰台区' ;--
8. 求长度
--SELECT st_length(geom) FROM china WHERE china.name='海淀区';
9. 求线上的点数
--SELECT st_numpoints(geom) FROM china where china.name='海淀区';
10. 判断几个类型
--SELECT st_geometrytype(geom) FROM china where china.name='漠河县';
11. 几何空间数据转换成空间数据文本格式
--SELECT st_astext(geom) FROM china WHERE china.name='海淀区';
12. 返回当前几何空间数据的SRID值
SELECT st_srid(geom) FROM china WHERE china.name='海淀区';
13. 判断是否闭合
--SELECT st_isclosed(geom) FROM china where china.name='海淀区';--
14. 判断是否为空
--SELECT st_isempty(geom) FROM china where china.name='海淀区';--
15. 判断起始点和终点坐标是否相同
--SELECT st_isring(geom) FROM china where china.name='海淀区';
16. 判断是否可用
--SELECT st_isvalid(geom) FROM china where china.name='海淀区';
17. 判断几何对象是否不包含特殊点(比如自相交)
--SELECT st_issimple(geom) FROM china where china.name='海淀区';
18. postgis在传统行业重点应用(优化查询分析性能)
https://yq.aliyun.com/download/3193?spm=5176.11156381.0.0.20de7775tzg7VW&do=login&accounttraceid=22a1e0a0-3c00-4f10-94df-38b246515a91
19、获取几何类型St_GeometryType(geom)
20、获取单个记录的几何个数ST_NumGeometries(geom)
select St_GeometryType(geom) GeomType,ST_NumGeometries(geom) GeomCount from rain;
21. point 和srid
select st_setsrid(st_makepoint(random()*360-180, random()*180-90), 4326)
22. 空间分析实现
将polygon和农田的polygon做st_intersection就行
实例你可以看看这里 https://www.bostongis.com/postgis_intersection_intersects.snippet

23 创建postgis扩展
create extension postgis;
CREATE EXTENSION postgis_topology;
create extension postgis_sfcgal;
create extension pgrouting;
create extension fuzzystrmatch;
create extension postgis_tiger_geocoder;
24 shp2pgsql
通过shape文件导出sql语句

shp2pgsql -s 2353 -c -W "GBK" C:\shape\650102jctb.shp>650102jctb.sql
详细信息

25 shp2pgsql直接将Shape数据导入到数据表
shp2pgsql -s 2353 -c -W "GBK" C:\shape\650102jctb.shp public.jctbtest | psql -d fhadmin_a -U postgres -W
追加

- shp2pgsql -s 4490 -a -W "GBK" E:\testdata\jbntbhtb\JBNTBHTB_XJ2000.shp public.lxdw | psql -d test -U postgres -W
创建

shp2pgsql -s 4326 -c -W "UTF-8" E:\迅雷下载\china-latest-free.shp\gis_osm_buildings_a_free_1.shp public.osm_buildings | psql -d test -U postgres -W
详细信息

导出shape文件

示例:pgsql2shp -f C:/WP/wp_dktb.shp -h localhost -u postgres -P 123456 -p 5433 fhadmin_a public.wp_dktb


pgsql2shp -f E:/shp/nyc_streets.shp -h localhost -u postgres -P 123456 -p 5433 Testpg  "SELECT * from  nyc_streets";
26 坐标转换st_transform
select ST_Transform(geom,4326);

//修改表中geom为4517的几何数据修改为4490的数据
update tempjbnt set geom= st_transform(st_setsrid(geom,4517),4490) 
27 大地坐标系面积计算
select st_area(geom,true); ---与平面坐标的面积有误差

SELECT st_area(ST_Transform(st_geometryfromtext('POLYGON((116.4679312706 39.9482801227,116.4677961543 39.9486461337,116.4680989087 39.9486998528,116.4682182670 39.9483181633,116.4679312706 39.9482801227))',4490),4517));------与平面坐标的面积相等
大地坐标系距离计算

SELECT st_distance(st_transform(st_geometryfromtext('POINT(116.4677961543 39.9486461337)',4326),3857),st_transform(st_geometryfromtext('POINT(116.4680989087 39.9486998528)',4326),3857));
28 判断点是在哪个多边形里
select * from osm_buildings  
    where ST_Within(st_geomfromtext('point(103.76902131950 36.07270404286)',4326),geom);
29 获取几何对象的中心点ST_Centroid
select 
    st_astext(
    ST_Centroid(ST_GeomFromText('MULTIPOLYGON(((116.3822484 39.9032743,110.3822732 39.9034939,110.3824074 36.9036869,110.3824074 36.9036869,116.3822484 39.9032743)))',4326))
     )
30.提取面的边界st_exteriorRing(geom)
Create table boundaries as 
Select st_union(st_exteriorRing(geom)) as geom from circles;
 -- st_exteriorRing提取面的边界
31.st_polygonize根据线坐标自动构造面,首尾不闭合的线无法构面会舍弃
create table polys as select nextval('polyseq') as id, 
(st_dump(st_polygonize(geom))).geom as geom from boundaries;
 -- st_polygonize根据线坐标自动构造面,首尾不闭合的线无法构面会舍弃
32. 判断几何数据的有效性
select ST_IsValidDetail(geom),* from lzgd_dktb where ST_IsValid(geom) =false
33.设置geom字段的srid值
ALTER TABLE lzgd_dktb
ALTER COLUMN geom TYPE geometry(MULTIPOLYGON, 4490)
USING ST_SetSRID(geom,4490);
34.判断几何是否空:st_isempty(geom A)
35.修改几何的srid
select UpdateGeometrySRID('t_gis','geom',4326);
36.计算指定范围的缓冲区
//获取geom 1000米的缓冲范围
select st_buffer ( geom :: geography, 1000 ) from wp_dktb
37.postgis 的polygon转为multiPloygon
SELECT ST_AsText(ST_Multi(ST_GeomFromText('POLYGON((743238 2967416,743238 2967450,743265 2967450,743265.625 2967416,743238 2967416))')));
38.图形自相交检查
select st_isvalidreason(geom),* from fnh.dktb where ST_IsValid(geom) = false limit 100
39.图形简单检查
select * from fnh.dktb where  ST_IsSimple(geom) =false 
40.图形组合检查
select * from fnh.dktb where  ST_IsSimple(geom) =false 

范围查询

两公里内中心坐标附近数据

OSM内面数据坐标系是900913地图已经发布,不大敢更改为4326,所以获取面中心面后采用函数进行转换,在通过函数转换为4326的面;

SELECT t.name,t.amenity,t.area,ST_Distance(ST_GeographyFromText('POINT(113.3948 23.05819)'),ST_GeographyFromText(t.Point)) as Tagistance,st_asgeojson(t.Point),
t.building,t.height,t.tags,t.landuse,t.leisure,t.aeroway,t.Point

from
(
select ST_AsText(ST_Transform(st_geometryfromtext(ST_AsText(st_centroid(m.way)),900913),4326)) as Point,* 

from planet_osm_polygon m where name is NOT null and 
(
(((m.building IS NOT NULL) AND (m.building <> 'no'::text)) OR (m.aeroway = 'terminal'::text))
or
 ((m.leisure = ANY (ARRAY['dog_park'::text, 'golf_course'::text, 'pitch'::text, 'park'::text, 'playground'::text, 'garden'::text, 'common'::text])) OR (m.landuse = ANY (ARRAY['allotments'::text, 'cemetery'::text, 'recreation_ground'::text, 'village_green'::text])))
)
) t
where  ST_DWithin(
  ST_GeomFromText(ST_AsText(ST_Transform(st_centroid(t.way),4326)),4326)::geography,
  ST_GeomFromText('POINT(113.3948 23.05819)',4326)::geography,
  2000 -- DISTANCE IN METERS
);

粘贴图片


select tb3.sheng,tb3.shi,tb3.qu,tb4.name,tb4.way,tb4.tags from 

(select tb1.sheng,tb1.shi,tb2.name as qu,tb2.way from 

(select '广西省' as sheng,name as shi,way from planet_osm_polygon where name='南宁市') as tb1,

(select * from planet_osm_polygon where admin_level='6') as tb2
where ST_Within(tb2.way,tb1.way)) as tb3
,
(select * from planet_osm_polygon_guangxi m where name is NOT null and 

(((m.building IS NOT NULL) AND (m.building <> 'no'::text)) OR (m.aeroway = 'terminal'::text))
or
 ((m.leisure = ANY (ARRAY['dog_park'::text, 'golf_course'::text, 'pitch'::text, 'park'::text, 'playground'::text, 'garden'::text, 'common'::text])) OR (m.landuse = ANY (ARRAY['allotments'::text, 'cemetery'::text, 'recreation_ground'::text, 'village_green'::text])))
) as tb4
where ST_Within(tb4.way,tb3.way) and name is not NULL
union all 
select tb3.sheng,tb3.shi,tb3.qu,tb4.name,tb4.way,tb4.tags from 

(select tb1.sheng,tb1.shi,tb2.name as qu,tb2.way from 

(select '广西省' as sheng,name as shi,way from planet_osm_polygon where name='南宁市') as tb1,

(select * from planet_osm_polygon_guangxi where admin_level='6') as tb2
where ST_Within(tb2.way,tb1.way)) as tb3
,
(select * from planet_osm_point_guangxi m where name is NOT null and 

(((m.building IS NOT NULL) AND (m.building <> 'no'::text)) OR (m.aeroway = 'terminal'::text))
or
 ((m.leisure = ANY (ARRAY['dog_park'::text, 'golf_course'::text, 'pitch'::text, 'park'::text, 'playground'::text, 'garden'::text, 'common'::text])) OR (m.landuse = ANY (ARRAY['allotments'::text, 'cemetery'::text, 'recreation_ground'::text, 'village_green'::text])))
) as tb4
where ST_Within(tb4.way,tb3.way) and name is not NULL

粘贴图片





 推荐知识

 历史版本

修改日期 修改人 备注
2022-05-20 13:38:36[当前版本] 井沿博 1.0
2022-05-19 16:47:01 井沿博 1.0

 附件

附件类型

PNGPNG

  目录
    戎光软件知识分享平台 - free.V4.3.0-439 - 免费版