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 astext(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

创建

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_transfrom(geom,4326);

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))
     )

2.提高空间数据分析效率的几种方法

1 gist索引创建

create index osm_buildings_idx on osm_buildings using gist(geom);

2 修改列存储不压缩

alter table osm_buildings alter column geom set storage external;

//分析测试
select a.* from dltb a, jbntbhpk b where ST_Intersects(a.geom,b.geom) order by bsm;

select st_area(ag) dltbarea,st_area(ss) cliparea from (
select a.*,a.geom as ag ,ST_Intersection(a.geom,b.geom) as ss from dltb a, jbntbhpk b where ST_Intersects(a.geom,b.geom)
) t

select ST_Intersection(dltb.geom, jbntbhpk.geom)