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
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,542评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,596评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 158,021评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,682评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,792评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,985评论 1赞 291
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,107评论 3赞 410
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,845评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,299评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,612评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,747评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,441评论 4赞 333
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,072评论 3赞 317
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,828评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,069评论 1赞 267
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,545评论 2赞 362
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,658评论 2赞 350

推荐阅读更多精彩内容

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