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