PostGIS關于批量空間數(shù)據(jù)處理的經驗

1、PostGIS關于批量空間數(shù)據(jù)處理的經驗題綱1 矢量數(shù)據(jù)常規(guī)處理方案存在的問題2 PostGIS常用數(shù)據(jù)處理功能簡介3 PostGIS數(shù)據(jù)處理常見問題4 總結矢量數(shù)據(jù)批處理常見流程csvshp輸入數(shù)據(jù)pynodejava邏輯工具shpgeotoolsgdal依賴庫輸出數(shù)據(jù)ArcMapQGIS可視化GIS Serverxlsx常見問題1 依賴環(huán)境多2 開發(fā)任務重3 處理效率低4 成果管理亂 5 shp副作用Shpvspostgis查詢語法對比Sql多簡單，為啥要寫代碼？PostGIS的統(tǒng)計分析語法按名稱分類統(tǒng)計我國各個省（面狀圖形）包含的城市數(shù)量：按名稱分類統(tǒng)計緩沖區(qū)范圍內受影響面狀圖形的面積：

2、Shpvspostgis查詢對比數(shù)據(jù)規(guī)模：191560shp137213121435postgis6825.524.7postgis btree0.7320.4220.466第一次第二次第三次1000500020001500屬性查詢：name=練塘鎮(zhèn)屬性查詢結果返回耗時（單位ms）shppostgispostgis btreeshp150314561474postgis224199197postgis gist10.90.8第一次第二次第三次1000500020001500空間查詢：c:118.6,32.4,r:0.05緩沖空間查詢結果返回耗時（單位ms）shppostgispostgis g

3、istPostGIS常用功能簡介pgsql2shpSHP GUIshp2pgsql圖形構造解析元數(shù)據(jù)讀寫圖形編輯圖形處理空間關系空間索引ImportExportcsvcopyexcelFile_fdw選用PostGIS的理由？Sql語法，入門及其簡單。代碼少則bug少，出成果快。查詢性能優(yōu)異。功能強大可媲美的esri的gp服務。依托PostgreSQL社區(qū)的各種好的產品，適應各種應用場景，如分布式， 時序數(shù)據(jù)庫等大規(guī)模數(shù)據(jù)處理領域。PostGIS數(shù)據(jù)處理中常見問題為什么我的查詢語句慢到崩潰？1 S Q L 不走空間索引2 空間索引效率低圖形處理方案沒有好的思路空間關系簡介ST_Contains

4、 ST_Within 包含/被包含ST_Intersects相交ST_Disjoint相離ST_Covers覆蓋ST_Overlaps壓蓋ST_Crosses穿越ST_Touches相連Rtree索引簡介問題一 SQL不走空間索引問題：如何查詢當前”我的位 置（118.1249,32.0987）”附近一公里內的所有poi？1.1Where后geom字段使用表達式函數(shù)查詢示例語句1：Explain select a.* from poi a where ST_Intersects(ST_Buffer(a.geom,0.01), ST_SetSrid(ST_MakePoint(118.3749,3

5、2.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_MakePoint(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.1Where后g

6、eom字段使用表達式函數(shù)查詢示例語句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 poi a (actual time=307.310.563.545 rows=5 loops=1)-順序掃描 Filter: (st_distance(geom, pt) Parallel Bitmap Heap Scan .-Bitmap Index Scan

7、 on poi_gra_idx (actual time=0.439.0.439 rows=9 loops=1)總結：特殊函數(shù)導致geom類型改變不走索引 ，適時可引入表達式索引。小結1 PostGIS解決同一個問題的寫法有多種，需要仔細審核與對比。2 不在where后對geom字段進行函數(shù)計算或類型轉換操作。3 特殊情況可建立表達式索引。1.3查詢使用ST_Disjoint場景介紹：已知居民點house表數(shù)量25000，交通點station表數(shù)量6000， 統(tǒng)計所有交通點200米以外的居民點數(shù)量。查詢邏輯：1 station先緩沖200米；2 合并所有小緩沖區(qū)為一個面；3 計 算與緩沖面不相

8、交的居民點；查詢示例語句：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_intersects(a.geom,b.geom) );Aggregate (actual time=36163.208.36163.208 rows=1 loops=1) （總耗時36s）-

9、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（查詢點總數(shù)25000，索引掃描到無效數(shù)據(jù)23547）ST_Disjoint優(yōu)化小結1 ST_Disjoint（相離）函數(shù)不符合rtree索引的查詢機制，絕對不會走索 引。2 ST_Disjoint函數(shù)僅適用于where前判定空間關系的結果，不可用于where后根據(jù)空間關系查詢的條件。3 ST

10、_Disjoint優(yōu)化應采用“ST_Intersects（相交）取反”方案。遺留問題：空間索引耗時32s/總耗時36s，索引效果太差。問題二 空間索引效率低案例1.3索引效率低圖示：圖形小，bbox大，會導致索 引掃描范圍放大，效率變差2.1where后或者子查詢中使用ST_Union查詢示例語句：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 fr

11、om 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 station) b where ST_intersects(a.geom,b.geom);-0.73sUnion等圖形處理函數(shù) 應該只在輸出結果用， 不能在查詢條件里用！2.2geom對象本身太復雜Mu

12、ltiPolygon Polygon ST_NumGeometries(geom) ST_GeometryN(geom,N)Polygon 小Polygon集合 ST_SubDivide(geom,n)問題三 復雜的圖形效果沒有思路3.1面壓蓋處理 面拆線C r e a t e t a b l e b o u n d a r i e s a s S e l e c t s t _ u n i o n ( s t _ e x t e r i o r R i n g ( g e o m ) ) a s g e o m f r o m c i r c l e s ; - - s t _ e x t

13、e r i o r R i n g 提取面的邊界面拆線3.1面壓蓋處理 線構面c r e a t e t a b l e p o l y s a s s e l e c t n ex t va l ( p o l y s e q ) a s i d , ( s t _ d u m p ( s t _ p o l y g o n i ze ( g e o m ) ) ) . g e o m a s g e o m f r o m b o u n d a r i e s ;- - s t _ p o l y g o n i ze 根據(jù)線坐標自動構造面， 首尾不閉合的線無法構面會舍棄面拆線3.1面壓

14、蓋處理 更新面的權重a l t e r t a b l e p o l y s a d d c o l u m n c o u n t i n t e g e r d e fa u l t 0 ;u p d a t e p o l y s s e t c o u n t = p . c o u n t f r o m ( s e l e c t c o u n t ( * ) a s c o u n t , p . i d a s i df r o m p o l y s p j o i n c i r c l e s c o n S T _ C o n t a i n s ( c . g e

15、 o m , S T _ Po i n t O n S u r fa c e ( p . g e o m ) ) g r o u p b yp . i d ) A S p w h e r e p . i d = p o l y s . i d ;delete from POLYS where count=0;綁定權重剔除冗余 分級渲染3.2線壓蓋處理 線拆點3.2線壓蓋處理 點位聚合權重更新s e l e c t a r ray _ a g g ( g i d ) g i d s , s u m ( w e i g h t ) w e i g h t i n t o p t _ g r o u

16、 p f r o m p t g r o u p b y g e o m ;c r e a t e i n d ex p t _ g r o u p _ g i d s _ i d xo n p t _ g r o u p u s i n g g i n ( g i d s ) ;u p d a t e p t t 2 s e t w e i g h t = t 1 .w e i g h t f r o m p t _ g r o u p t 1 w h e r e t 2 . g i d = a ny ( t 1 . g i d s ) ;3.2線壓蓋處理 點連線剔除冗余- - 根據(jù)點的線路i d ， 線路中的順序號排序F o r r e c i n s e l e c t * f r o m p t o r d e r b y l i n e _ i d , p t i n d ex l o o p- - 權重連續(xù)的點 構成一個 l i n eE n d l o o p ;D e l e t e f r o m