1、PostGISPostGIS关于批量空间数据关于批量空间数据处理的经验处理的经验梅白帆free_江苏电力信息 GIS专家题纲题纲 1 矢量数据常规处理方案存在的问题 2 PostGIS常用数据处理功能简介 3 PostGIS数据处理常见问题 4 总结矢量数据批处理常见流程矢量数据批处理常见流程csvshp输入数据pynodejava逻辑工具shpgeotoolsgdal依赖库输出数据ArcMapQGIS可视化GIS Server1 依赖环境多2 开发任务重3 处理效率低4 成果管理乱5 shp副作用xlsx常见问题Shp vs postgis 查询语法对比查询语法对比Sql多简单，为啥要写代码

2、？PostGIS的统计分析语法的统计分析语法 按名称分类统计我国各个省（面状图形）包含的城市数量：按名称分类统计缓冲区范围内受影响面状图形的面积：Shp vs postgis 查询对比查询对比数据规模：191560 属性查询：name=练塘镇 空间查询：c:118.6,32.4,r:0.05缓冲shp5postgis6825.524.7postgis btree0.7320.4220.466第一次第二次第三次05000属性查询结果返回耗时（单位ms）shppostgispostgis btreeshp4postgis224199

3、197postgis gist10.90.8第一次第二次第三次05000空间查询结果返回耗时（单位ms）shppostgispostgis gistPostGIS常用功能简介常用功能简介shp2pgsqlpgsql2shpSHP GUI图形构造解析元数据读写图形编辑图形处理空间关系空间索引ImportExportcsvcopyexcelFile_fdw选用选用PostGIS的理由？的理由？Sql语法，入门及其简单。代码少则bug少，出成果快。查询性能优异。功能强大可媲美的esri的gp服务。依托PostgreSQL社区的各种好的产品，适应各种应用场景，如分布式，时序数据库

4、等大规模数据处理领域。PostGIS数据处理中常见问题数据处理中常见问题 为什么我的查询语句慢到崩溃？1 SQL不走空间索引 2 空间索引效率低 图形处理方案没有好的思路空间关系简介空间关系简介ST_ContainsST_Within包含/被包含ST_Intersects相交ST_Disjoint相离ST_Covers覆盖ST_Overlaps压盖ST_Crosses穿越ST_Touches相连Rtree索引简介索引简介问题一问题一 SQL不走空间索引不走空间索引问题：如何查询当前”我的位置（118.1249,32.0987）”附近一公里内的所有poi？1.1 Where后后geom字段使用表

5、达式函数字段使用表达式函数 查询示例语句1：Explain select a.*from poi a where ST_Intersects(ST_Buffer(a.geom,0.01),ST_SetSrid(ST_MakePoint(118.3749,32.0987),4326);Parallel Seq Scan on poi a Filter:st_intersects(st_buffer(geom,0.01),pt)Explain select a.*from poi a where ST_Intersects(a.geom,st_buffer(ST_SetSrid(ST_MakePo

6、int(118.3749,32.0987),4326),0.01);Index Scan using poi_geom_idx on poi a(actual time=0.065.0.104 rows=5 loops=1)1.1 Where后后geom字段使用表达式函数字段使用表达式函数 查询示例语句2：总结：where后对geom使用表达式会导致索引失效。select a.*from poi a where ST_Distance(a.geom,ST_SetSrid(ST_MakePoint(118.3749,32.0987),4326)0.01;Parallel Seq Scan on

7、poi a(actual time=307.310.563.545 rows=5 loops=1)-顺序扫描 Filter:(st_distance(geom,pt)Parallel Bitmap Heap Scan.-Bitmap Index Scan on poi_gra_idx(actual time=0.439.0.439 rows=9 loops=1)小结小结 1 PostGIS解决同一个问题的写法有多种，需要仔细审核与对比。2 不在where后对geom字段进行函数计算或类型转换操作。3 特殊情况可建立表达式索引。1.3 查询使用查询使用ST_Disjoint 场景介绍：已知居民点

8、house表数量25000，交通点station表数量6000，统计所有交通点200米以外的居民点数量。查询逻辑：1 station先缓冲200米；2 合并所有小缓冲区为一个面；3 计算与缓冲面不相交的居民点；查询示例语句：explain(analyse)select count(a.*)from house a where gid not in(select distinct(a.gid)from house a,(select st_union(st_buffer(geom:geography,200):geometry)geom from station)b where ST_inter

9、sects(a.geom,b.geom);Aggregate(actual time=36163.208.36163.208 rows=1 loops=1)（总耗时36s）-Seq Scan on house -Index Scan using house_geom_idx on house a_1(actual time=38.881.31971.891 rows=1453 loops=1)（空间索引耗时32s）Rows Removed by Filter:23547 （查询点总数25000，索引扫描到无效数据23547）ST_Disjoint优化小结优化小结 1 ST_Disjoint（相

10、离）函数不符合rtree索引的查询机制，绝对不会走索引。2 ST_Disjoint函数仅适用于where前判定空间关系的结果，不可用于where后根据空间关系查询的条件。3 ST_Disjoint优化应采用“ST_Intersects（相交）取反”方案。遗留问题：空间索引耗时32s/总耗时36s，索引效果太差。问题二问题二 空间索引效率低空间索引效率低 案例1.3索引效率低图示：图形小，bbox大，会导致索引扫描范围放大，效率变差2.1 where后或者子查询中使用后或者子查询中使用ST_Union 查询示例语句：select count(a.*)from house a where gid

11、not in (select distinct(a.gid)from house a,(select ST_Union(st_buffer(geom:geography,200):geometry)geom from station)b where ST_intersects(a.geom,b.geom);-36sselect count(a.*)from house a where gid not in(select distinct(a.gid)from house a,(select st_buffer(geom:geography,200):geometry geom from sta

12、tion)b where ST_intersects(a.geom,b.geom);-0.73sUnion等图形处理函数应该只在输出结果用，不能在查询条件里用！2.2 geom对象本身太复杂对象本身太复杂MultiPolygon PolygonST_NumGeometries(geom)ST_GeometryN(geom,N)Polygon 小Polygon集合ST_SubDivide(geom,n)问题三问题三 复杂的图形效果没有思路复杂的图形效果没有思路3.1 面压盖处理面压盖处理 面拆线面拆线Create table boundaries as Select st_union(st_ex

13、teriorRing(geom)as geom from circles;-st_exteriorRing提取面的边界面拆线3.1 面压盖处理面压盖处理 线构面线构面create table polys as select nextval(polyseq)as id,(st_dump(st_polygonize(geom).geom as geom from boundaries;-st_polygonize根据线坐标自动构造面，首尾不闭合的线无法构面会舍弃面拆线3.1 面压盖处理面压盖处理 更新面的权重更新面的权重alter table polys add column count inte

14、ger default 0;update polys set count=p.count from(select count(*)as count,p.id as id from polys p join circles c on ST_Contains(c.geom,ST_PointOnSurface(p.geom)group by p.id)AS p where p.id=polys.id;delete from POLYS where count=0;绑定权重剔除冗余分级渲染3.2 线压盖处理线压盖处理 线拆点线拆点3.2 线压盖处理线压盖处理 点位聚合权重更新点位聚合权重更新selec

15、t array_agg(gid)gids,sum(weight)weight into pt_group from pt group by geom;create index pt_group_gids_idx on pt_group using gin(gids);update pt t2 set weight=t1.weight from pt_group t1 where t2.gid=any(t1.gids);3.2 线压盖处理线压盖处理 点连线剔除冗余点连线剔除冗余-根据点的线路id，线路中的顺序号排序For rec in select*from pt order by line_id,ptindex loop-权重连续的点 构成一个 lineEnd loop;Delete from line_result where gid not in(select min(ctid)from line_result group by geom)去重分级渲染总结总结 1 PostGIS入门简单，功能全面。2 开发敏捷，试错与调优效率高。3 熟练掌握PostGIS的函数是解决复杂综合问题的前提。4 深入理解PG的索引机制及其对应场景是调优的关键。5 非常适合科研机构做GIS数据处理平台，服务发布数据源。