[postgis-users] optimizing intersection of two large feature classes
Paul Ramsey
pramsey at cleverelephant.ca
Sun Aug 31 22:38:57 PDT 2008
In general, overlays are going to be slow in PostGIS, until we have
prepared geometries for intersection as well as predicates.
However, since one side of your overlay is a grid, perhaps you can use
the grid property to cut your problem down in size. Try testing the
containment of the envelope of your complex polygons by the grid
squares, and anything which passes that test, add to your resultant
table without change. I think there is even an index operator for
it...
"A @ B
The "@" operator returns true if A's bounding box is completely
contained by B's"
So you could use that index op to remove all the complete containment
cases from your workload, leaving only the cases that actually cross
lines to be computed.
Paul
On Mon, Aug 25, 2008 at 12:43 AM, andreas_bfw <Andreas.Schild at bfw.gv.at> wrote:
>
> Dear List,
>
> I'm a newby to Postgres/Postgis.
> I have 2 feature classes, #)soilunits of austria 500000+ polygons which I
> reduced to about 13000 multipolygons,
> #)statistic units of austria 1300000 polygons (regular grid )
> I have to do an intersection on those feature classes.
>
> Therefor I did some tuning: Postgres Parameters such as shared_buffers, also
> increased shmmax of the linux kernel
> since EXPLAIN ANALYZE on a subset of the feature classes I used ST_ADDBBox
> on both feature classes.
>
> However, since the query is running now for 2 days, i am not sure if I have
> done the query well.
> Below is my query and the explain verbose output
>
> I would be very happy about any help or tip
>
> Thanks a lot in advance,
>
> Andi
>
>
> ***************
> CREATE TABLE lwbk.bodenform_r250_poly2 AS SELECT
> ST_Intersection(r.wkb_geometry, b.wkb_geometry) AS wbk_geometry,
> r.r_250m, b.bofo
> FROM
> r250_bbox_test AS r, lwbk.bofo_u_b_bbox_test AS b
> WHERE ST_Intersects(r.wkb_geometry, b.wkb_geometry) AND b.bofo NOT IN
> (73900, 145900);
> ***************
> " {NESTLOOP "
> " :startup_cost 0.00 "
> " :total_cost 600611.05 "
> " :plan_rows 1847088 "
> " :plan_width 26874 "
> " :targetlist ("
> " {TARGETENTRY "
> " :expr "
> " {FUNCEXPR "
> " :funcid 17620 "
> " :funcresulttype 17160 "
> " :funcretset false "
> " :funcformat 0 "
> " :args ("
> " {VAR "
> " :varno 65000 "
> " :varattno 2 "
> " :vartype 17160 "
> " :vartypmod -1 "
> " :varlevelsup 0 "
> " :varnoold 1 "
> " :varoattno 2"
> " }"
> " {VAR "
> " :varno 65001 "
> " :varattno 2 "
> " :vartype 17160 "
> " :vartypmod -1 "
> " :varlevelsup 0 "
> " :varnoold 2 "
> " :varoattno 2"
> " }"
> " )"
> " }"
> " :resno 1 "
> " :resname wbk_geometry "
> " :ressortgroupref 0 "
> " :resorigtbl 0 "
> " :resorigcol 0 "
> " :resjunk false"
> " }"
> " {TARGETENTRY "
> " :expr "
> " {VAR "
> " :varno 65000 "
> " :varattno 3 "
> " :vartype 1042 "
> " :vartypmod 14 "
> " :varlevelsup 0 "
> " :varnoold 1 "
> " :varoattno 3"
> " }"
> " :resno 2 "
> " :resname r_250m "
> " :ressortgroupref 0 "
> " :resorigtbl 275458 "
> " :resorigcol 3 "
> " :resjunk false"
> " }"
> " {TARGETENTRY "
> " :expr "
> " {VAR "
> " :varno 65001 "
> " :varattno 1 "
> " :vartype 23 "
> " :vartypmod -1 "
> " :varlevelsup 0 "
> " :varnoold 2 "
> " :varoattno 1"
> " }"
> " :resno 3 "
> " :resname bofo "
> " :ressortgroupref 0 "
> " :resorigtbl 253892 "
> " :resorigcol 1 "
> " :resjunk false"
> " }"
> " )"
> " :qual <> "
> " :lefttree "
> " {SEQSCAN "
> " :startup_cost 0.00 "
> " :total_cost 933.83 "
> " :plan_rows 24544 "
> " :plan_width 26747 "
> " :targetlist ("
> " {TARGETENTRY "
> " :expr "
> " {VAR "
> " :varno 2 "
> " :varattno 1 "
> " :vartype 23 "
> " :vartypmod -1 "
> " :varlevelsup 0 "
> " :varnoold 2 "
> " :varoattno 1"
> " }"
> " :resno 1 "
> " :resname <> "
> " :ressortgroupref 0 "
> " :resorigtbl 0 "
> " :resorigcol 0 "
> " :resjunk false"
> " }"
> " {TARGETENTRY "
> " :expr "
> " {VAR "
> " :varno 2 "
> " :varattno 2 "
> " :vartype 17160 "
> " :vartypmod -1 "
> " :varlevelsup 0 "
> " :varnoold 2 "
> " :varoattno 2"
> " }"
> " :resno 2 "
> " :resname <> "
> " :ressortgroupref 0 "
> " :resorigtbl 0 "
> " :resorigcol 0 "
> " :resjunk false"
> " }"
> " )"
> " :qual ("
> " {SCALARARRAYOPEXPR "
> " :opno 518 "
> " :opfuncid 144 "
> " :useOr false "
> " :args ("
> " {VAR "
> " :varno 2 "
> " :varattno 1 "
> " :vartype 23 "
> " :vartypmod -1 "
> " :varlevelsup 0 "
> " :varnoold 2 "
> " :varoattno 1"
> " }"
> " {CONST "
> " :consttype 1007 "
> " :constlen -1 "
> " :constbyval false "
> " :constisnull false "
> " :constvalue 32 [ 32 0 0 0 1 0 0 0 0 0 0 0 23 0 0 0 2 0 0 0 1 0
> 0 0"
> " -84 32 1 0 -20 57 2 0 ]"
> " }"
> " )"
> " }"
> " )"
> " :lefttree <> "
> " :righttree <> "
> " :initPlan <> "
> " :extParam (b)"
> " :allParam (b)"
> " :nParamExec 0 "
> " :scanrelid 2"
> " }"
> " :righttree "
> " {INDEXSCAN "
> " :startup_cost 0.00 "
> " :total_cost 24.03 "
> " :plan_rows 14 "
> " :plan_width 127 "
> " :targetlist ("
> " {TARGETENTRY "
> " :expr "
> " {VAR "
> " :varno 1 "
> " :varattno 1 "
> " :vartype 23 "
> " :vartypmod -1 "
> " :varlevelsup 0 "
> " :varnoold 1 "
> " :varoattno 1"
> " }"
> " :resno 1 "
> " :resname <> "
> " :ressortgroupref 0 "
> " :resorigtbl 0 "
> " :resorigcol 0 "
> " :resjunk false"
> " }"
> " {TARGETENTRY "
> " :expr "
> " {VAR "
> " :varno 1 "
> " :varattno 2 "
> " :vartype 17160 "
> " :vartypmod -1 "
> " :varlevelsup 0 "
> " :varnoold 1 "
> " :varoattno 2"
> " }"
> " :resno 2 "
> " :resname <> "
> " :ressortgroupref 0 "
> " :resorigtbl 0 "
> " :resorigcol 0 "
> " :resjunk false"
> " }"
> " {TARGETENTRY "
> " :expr "
> " {VAR "
> " :varno 1 "
> " :varattno 3 "
> " :vartype 1042 "
> " :vartypmod 14 "
> " :varlevelsup 0 "
> " :varnoold 1 "
> " :varoattno 3"
> " }"
> " :resno 3 "
> " :resname <> "
> " :ressortgroupref 0 "
> " :resorigtbl 0 "
> " :resorigcol 0 "
> " :resjunk false"
> " }"
> " )"
> " :qual ("
> " {OPEXPR "
> " :opno 17298 "
> " :opfuncid 17287 "
> " :opresulttype 16 "
> " :opretset false "
> " :args ("
> " {VAR "
> " :varno 1 "
> " :varattno 2 "
> " :vartype 17160 "
> " :vartypmod -1 "
> " :varlevelsup 0 "
> " :varnoold 1 "
> " :varoattno 2"
> " }"
> " {VAR "
> " :varno 65001 "
> " :varattno 2 "
> " :vartype 17160 "
> " :vartypmod -1 "
> " :varlevelsup 0 "
> " :varnoold 2 "
> " :varoattno 2"
> " }"
> " )"
> " }"
> " )"
> " :lefttree <> "
> " :righttree <> "
> " :initPlan <> "
> " :extParam (b)"
> " :allParam (b)"
> " :nParamExec 0 "
> " :scanrelid 1 "
> " :indexid 275465 "
> " :indexqual ("
> " {OPEXPR "
> " :opno 17298 "
> " :opfuncid 17287 "
> " :opresulttype 16 "
> " :opretset false "
> " :args ("
> " {VAR "
> " :varno 1 "
> " :varattno 1 "
> " :vartype 17160 "
> " :vartypmod -1 "
> " :varlevelsup 0 "
> " :varnoold 1 "
> " :varoattno 2"
> " }"
> " {VAR "
> " :varno 65001 "
> " :varattno 2 "
> " :vartype 17160 "
> " :vartypmod -1 "
> " :varlevelsup 0 "
> " :varnoold 2 "
> " :varoattno 2"
> " }"
> " )"
> " }"
> " )"
> " :indexqualorig ("
> " {OPEXPR "
> " :opno 17298 "
> " :opfuncid 17287 "
> " :opresulttype 16 "
> " :opretset false "
> " :args ("
> " {VAR "
> " :varno 1 "
> " :varattno 2 "
> " :vartype 17160 "
> " :vartypmod -1 "
> " :varlevelsup 0 "
> " :varnoold 1 "
> " :varoattno 2"
> " }"
> " {VAR "
> " :varno 65001 "
> " :varattno 2 "
> " :vartype 17160 "
> " :vartypmod -1 "
> " :varlevelsup 0 "
> " :varnoold 2 "
> " :varoattno 2"
> " }"
> " )"
> " }"
> " )"
> " :indexstrategy (i 3)"
> " :indexsubtype (o 0)"
> " :indexorderdir 0"
> " }"
> " :initPlan <> "
> " :extParam (b)"
> " :allParam (b)"
> " :nParamExec 0 "
> " :jointype 0 "
> " :joinqual ("
> " {FUNCEXPR "
> " :funcid 17654 "
> " :funcresulttype 16 "
> " :funcretset false "
> " :funcformat 0 "
> " :args ("
> " {VAR "
> " :varno 65000 "
> " :varattno 2 "
> " :vartype 17160 "
> " :vartypmod -1 "
> " :varlevelsup 0 "
> " :varnoold 1 "
> " :varoattno 2"
> " }"
> " {VAR "
> " :varno 65001 "
> " :varattno 2 "
> " :vartype 17160 "
> " :vartypmod -1 "
> " :varlevelsup 0 "
> " :varnoold 2 "
> " :varoattno 2"
> " }"
> " )"
> " }"
> " )"
> " }"
> ""
> "Nested Loop (cost=0.00..600611.05 rows=1847088 width=26874)"
> " Join Filter: _st_intersects(r.wkb_geometry, b.wkb_geometry)"
> " -> Seq Scan on bofo_u_b_bbox_test b (cost=0.00..933.83 rows=24544
> width=26747)"
> " Filter: (bofo <> ALL ('{73900,145900}'::integer[]))"
> " -> Index Scan using r250_bbox_test_gist1 on r250_bbox_test r
> (cost=0.00..24.03 rows=14 width=127)"
> " Index Cond: (r.wkb_geometry && b.wkb_geometry)"
> " Filter: (r.wkb_geometry && b.wkb_geometry)"
>
> --
> View this message in context: http://www.nabble.com/optimizing-intersection-of-two-large-feature-classes-tp19107439p19107439.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
More information about the postgis-users
mailing list