PostGIS 通过SQL语句实现空间分析【入门级】

时间：2022-06-01 17:54:48

　　PostGIS是对象关系型数据库系统PostgreSQL的一个扩展，PostGIS提供如下空间信息服务功能:空间对象、空间索引、空间操作函数和空间操作符。同时，PostGIS遵循OpenGIS的规范。

 

刚开始学习了一些SQL语句，尝试实现要素图层的空间分析：

--读取要素的几何信息（按照WKT描述）
select ST_AsText(geom) from dzzg where bsm=2715;
select ST_AsText(geom) from ckyd where bsm=420;
--求出dzzg.bsm为2715的要素和cykd.bsm为420的要素的相交部分
select ST_Intersection(ST_GeomFromText(ST_astext(a.geom)),ST_GeomFromText(ST_astext(b.geom)))
FROM dzzg a INNER JOIN ckyd b on ST_Intersects(ST_GeomFromText(ST_astext(a.geom)),ST_GeomFromText(ST_astext(b.geom)))
where a.bsm=2715 and b.bsm=420

//ST_Intersection表示求交

//红色部分表示判断相交 on ST_Intersects

--success --选出dzzg和ckyd两个图层的相交部分 --只选择前5个 不然需要很多时间
SELECT b.bsm As bbsm, p.bsm As pbsm, ST_Intersection(ST_GeomFromText(ST_AsText(b.geom)), ST_GeomFromText(ST_AsText(p.geom))) As intersect_bp FROM dzzg b INNER JOIN ckyd p ON ST_Intersects(ST_GeomFromText(ST_AsText(b.geom)),ST_GeomFromText(ST_AsText(p.geom))) --WHERE ST_Overlaps(ST_GeomFromText(ST_AsText(b.geom)),ST_GeomFromText(ST_AsText(p.geom))) 
    LIMIT 5

--从dzzg层中选出满足以下条件的要素，并显示相交部分 --1、标识码大于2000 [感觉有部分的geom值有问题，所以限定一下搜索范围] --2、与ckyd中标识码为420的面相交
select a.bsm, st_astext(st_intersection(st_geomfromtext(st_astext(a.geom)),st_geomfromtext(st_astext(b.geom)))) as intersection from dzzg a inner join ckyd b ON ST_Intersects(ST_GeomFromText(ST_AsText(a.geom)),ST_GeomFromText(ST_AsText(b.geom))) where b.bsm=420 and a.bsm>2000


--查询与已知面相交的图形
SELECT bsm FROM dzzg where ST_Intersects( ST_GeomFromText('POLYGON((102.463 24.873,102.465 24.872,102.463 24.872,102.463 24.873))'), ST_GeomFromText(ST_AsText(geom))) and bsm>2700


--创建表
create table test(id1 int4,id2 int4); select addgeometrycolumn('public','test','shape',4610,'POLYGON',2); //添加几何字段，4610表示坐标系，2表示二维要素 SELECT b.bsm As bbsm, p.bsm As pbsm, ST_AsText(ST_Intersection(ST_GeomFromText(ST_AsText(b.geom)), ST_GeomFromText(ST_AsText(p.geom)))) As intersect_bp FROM dzzg b INNER JOIN ckyd p ON ST_Intersects(ST_GeomFromText(ST_AsText(b.geom)),ST_GeomFromText(ST_AsText(p.geom))) LIMIT 3

--插入记录
--函数st_geomfromtext 表示从“WKT描述”构建几何图形

insert into test(id1,id2,shape)
values( 21,699,st_geomfromtext('POLYGON((102.481862440151 24.9381407958162, 102.480064171822 24.9380431117968,102.480009386288 24.9381143096393, 102.481862440151 24.9381407958162))',4610) );--删除已知表 drop table ckyd

 初步学习，持续更新......

参考：http://blog.sina.com.cn/s/blog_722b6a020102v5m9.html

 

标签：

相关文章