[postgis-users] optimizing intersection of two large feature classes
andreas_bfw
Andreas.Schild at bfw.gv.at
Mon Sep 1 01:52:47 PDT 2008
Dear Paul,
Thanks a lot for Your suggestion.
However, I have allready done the intersection and it worked fine at the end
(about 10 hours computing time, Number of Intersections ~ 40Mio) .
There have been some obstacles (maybe degenerations) in the output dataset.
I will give a summary after I have completely controlled and cleaned the
output dataset.
Thanks again!
Andi
Paul Ramsey-3 wrote:
>
> 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
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
--
View this message in context: http://www.nabble.com/optimizing-intersection-of-two-large-feature-classes-tp19107439p19250304.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
More information about the postgis-users
mailing list