postgis常用操作

postgis

标签（空格分隔）： 数据库

[toc]

1. 空间数据库建表

1. 首先建立一个常规的表格存储有关城市（cities）的信息。这个表格有两栏，一个是 ID 编号，一个是城市名：

CREATE TABLE cities ( id int4, name varchar(50) )

2.现在添加一个空间栏用于存储城市的位置。

习惯上这个栏目叫做 the_geom 。它记录了数据为什么类型（点、线、面）、有几维（这里是二维）以及空间坐标系统。此处使用 EPSG:4326 坐标系统。

SELECT AddGeometryColumn ('cities', 'the_geom', 4326, 'POINT', 2)

3.使用 PostGIS 的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. 求多边形层中每个多边形的中心

SELECT st_astext(ST_Centroid(the_geom)) FROM country;

5. 基本的查询语句

SELECT * FROM china LIMIT 5;
--SELECT geom FROM china WHERE china."name"='东城区';
--SELECT geom FROM china WHERE china."name"='西城区';
--SELECT * FROM china WHERE china."name"='朝阳区';
--SELECT geom FROM china WHERE china."name"='石景山区';
--SELECT * FROM china WHERE china."name"='通州区';
--SELECT geom FROM china WHERE china."name"='海淀区';
--SELECT geom FROM china WHERE china."name"='丰台区';
--SELECT geom FROM china WHERE china."name"='大兴区';
--SELECT geom FROM china WHERE china."name"='房山区';
--SELECT geom FROM china WHERE china."name"='门头沟区';

6. 空间关系查询

1. 是否相连

--SELECT st_disjoint(a.geom, b.geom) from china a, china b where a.name='石景山区'  and b."name"='丰台区';

2. 是否相交

--SELECT st_intersects(a.geom, b.geom) FROM china a,china 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"='朝阳区';

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

创建 注意：如果创建提示relation 不存在可能没有创建postgis扩展，在public模式下执行第23步

shp2pgsql -s 4547 -c -W "UTF-8" D:/资料/freedoTech/2023/商丘/商丘建筑单体底面矢量/商丘全.shp cim.building_model1 | psql -h 192.168.20.236 -d ancient-city -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

41.压占分析查询

WITH g AS ( SELECT st_geomfromtext ( '{the_geom}', 4490 ) AS geom,st_area(st_geomfromtext('{the_geom}',4490),true) as ysarea ) 
SELECT  s.*,(s.area/s.ysarea) as yzbl FROM( SELECT st_area ( ST_Intersection ( t.geom, g.geom ), TRUE ) AS area,    t.glid, t.jzlb,g.ysarea  FROM   public.tb t,    g WHERE  ST_Intersects ( t.geom, g.geom )   ) s

42.将自相交图形合法化(ST_MakeValid)

select ST_MakeValid(geom) from tb where is_valid(geom) = false

43.通过st_asgeojson获取坐标信息

通过st_asgeojson 获取图斑的几何信息时，默认小数位数只保留9位，会损失精度，所以一般设置保留14位小数位数

select st_asgeojson(geom,14) from public.tb

44.关于srid

若没有设置srid，则会默认srid为0。 srid为0时，PostGIS无法获知你数据的坐标系，也就无法使用st_transform()函数进行转换。首先需要设置自己数据的srid

45.按距离排序

CREATE TABLE mylocation ( 
  id SERIAL PRIMARY KEY,
  geom GEOMETRY(Point, 4326),
  name VARCHAR(128),
  x double precision,
  y double precision
); 
 
INSERT INTO mylocation (geom,name,x,y) VALUES (
  ST_GeomFromText('POINT(0.0001 0)', 4326),'zhangsan',0.0001,0
);
INSERT INTO mylocation (geom,name,x,y) VALUES (
  ST_GeomFromText('POINT(0.001 0)', 4326),'zhangsan',0.001,0
);
INSERT INTO mylocation (geom,name,x,y) VALUES (
  ST_GeomFromText('POINT(0.001 0)', 4326),'zhangsan',0.001,0
);
INSERT INTO mylocation (geom,name,x,y) VALUES (
  ST_GeomFromText('POINT(0.1 0)', 4326),'zhangsan',0.1,0
);
 
--- 平面坐标查询
 
SELECT id, name,geom,x,y,   ST_DistanceSphere(
                      geom,
                      ST_GeometryFromText('POINT(0 0)')) distance
FROM mylocation
WHERE ST_DWithin(
  geom, 
  ST_GeomFromText('POINT(0 0)', 4326),
  0.001

经纬度坐标查询


SELECT id, name,geom,x,y,   ST_DistanceSphere(
                      geom,
                      ST_GeometryFromText('POINT(0 0)')) distance
FROM mylocation
WHERE ST_DWithin(
  geom::geography, 
  ST_GeomFromText('POINT(0 0)', 4326)::geography,
  1000
) ORDER BY distance asc
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容

  • PostGIS 常用函数
    本文转载自PostGIS中的常用函数并稍加修改 说明：这里许多函数是以ST_[X]yyy形式命名的，事实上很多函数...
    AllanHao阅读 7,087评论 0赞 4
  • PostgreSQL+PostGIS 的使用
    from PostgreSQL+PostGIS 的使用 一、PostGIS中的几何类型 PostGIS支持所有O...
    山华水清阅读 7,013评论 0赞 5
  • 深情
    清明节了 三天假期 近来有好好生活 挺好的 深情演绎着 人生的每个角色 怕太入戏 也怕情到深处 难自禁 动心忍性 ...
    三趾树懒记阅读 132评论 0赞 1
  • 2017-05-07
    东汉末年，群雄逐鹿之时，战火纷纷。 他与她相遇之时，正是他大破皖城之际，雄姿勃发。 公子已过弱冠之年，意气风发，...
    楚云端阅读 207评论 0赞 1
  • 挥汗如雨，为了更好的自己！
    春夏秋来，是怎么跟它建立密不可分的关系的，我原以为如此牢固的感情，是源于身体哪里出了问题…… 没有一面直视你全身的...
    天香百合666阅读 225评论 0赞 1