4

I am running an area intersection query on two large multipolygon tables (a few million rows).

SELECT r.geom
FROM public.my_table r
WHERE EXISTS (
    SELECT gid
    FROM public.my_table_intersection
    WHERE
        r.class = '2' AND
        st_intersects(r.geom, geom) AND
        ST_IsValid(r.geom) AND
        ST_IsValid(geom) AND
        (st_area(st_intersection(r.geom, geom))/st_area(r.geom)) > 0.1
);

The code appears to work with small test files but I have left it for ages with my main files with no result yet. I know that such processing on very large datasets can be very slow, is this sort of operation within PostGis's capabilities or am I pushing it too far? Is there perhaps an alternative way of doing such processing on very large data-sets? would ArcGIS be quicker?

EDIT: The table row size is 3.7 million for my_table and then 2.1 million for my_table_intersection with indexes on geom for both.

The query plan is:

"Nested Loop Semi Join  (cost=0.41..50013102.40 rows=33 width=275)"

"  ->  Seq Scan on my_table r  (cost=0.00..1153671.74 rows=295608 width=275)"

"        Filter: ((modal_clas = 3::numeric) AND st_isvalid(geom))"

"  ->  Index Scan using my_table_intersection_geom_gist on my_table_intersection  (cost=0.41..165.22 rows=8 width=277)"

"        Index Cond: (r.geom && geom)"

"        Filter: (st_isvalid(geom) AND _st_intersects(r.geom, geom) AND ((st_area(st_intersection(r.geom, geom)) / st_area(r.geom)) > 0.1::double precision))"
  • Please edit the question to specify the number of rows in each of the tables and a complete listing of the indexes created on both tables. The query plan from the optimizer would also be useful. – Vince Aug 5 '15 at 11:28
  • Why are you including ST_IsValid in the query -- Shouldn't that be a maintenance step? It doesn't appear that the query plan applies to the provided query. Do you have an attribute index built on "class/modal_clas"? What percentage of the table has value "3/2"? – Vince Aug 5 '15 at 12:04
  • l would avoid the calls to ST_IsValid which is going to be adding extra overhead. Start by removing or cleaning (ST_MakeValid) your invalid geometries. Beyond that I would imagine a lot will depend on how specifc class = 2 and ST_Intersects are, as to which the query optimizer will chose first. I assume you have indexes on both? – John Powell Aug 5 '15 at 12:04
  • 1
    @Vince, great minds think alike, fools seldom differ :-) – John Powell Aug 5 '15 at 12:05
  • Ok, I will try and concentrate on fixing the geometries, indeed the ST_IsValid is needed currently to filter these out as fixing the geometries is causing problems too. I didn't have an index on the class, but I will add one now, adding that check had actually increased the speed (I assume this is because the whole dataset was no longer needing to be checked). – Mart Aug 5 '15 at 12:12
2

It depends a lot on your input geometries. You're running what looks like a conflation query here, and hopefully that means that your geometries in both sets are of fairly similar sizes and not overly complex. That means that any given geometry on one side will be tested against a fairly limited number of geometries on the other (in a parcel-against-parcel example, say, each parcel might get tested against 4-5 other candidates; in a housing footprint case, each print might get testing against 1-3 other candidates).

The opposite case, where one side has really large polygons where each polygon gathers in lots of candidates for testing, could take quite a long time to run.

1

It also really depends how complex your geometries are. If you have large geometries with a lot of points, it would take much longer to process than smaller ones. Do a MAX(ST_NPoints(geom)) on your tables to see how bad they are. In that case you could gain some by adding an ST_Simplyfy step

SELECT r.geom
 FROM public.my_table r
WHERE EXISTS (
SELECT gid
FROM public.my_table_intersection
WHERE
    r.class = '2' AND
    st_intersects(r.geom, geom) AND
    ST_IsValid(r.geom) AND
    ST_IsValid(geom) AND
    (st_area(st_intersection(r.geom,    ST_SimplifyPreserveTopologY(geom,1)))/st_area(r.geom)) > 0.1
   );

The unit 1 is a function of the units of your geometries

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.