3

I have a table with approximately 500,000 records that I am trying to intersect with a table that has about 30,000 records. It was running for 11 hours until I finally stopped it.

Why would this take so long?

I have spatial indexes on both tables and they are in UTM. I have noticed in the past when I have a very large amount of vertices things can run very slowly and so on occasion I will simplify. In this case the 30K record table has about 3.5 million points, whereas the 500K table has about 7 million. I can't simplify the 500K records table without losing the level of accuracy I need and I'm not sure simplifying the the 30K table will help. Below is the query I am running.

Is there something I am missing in terms of it taking so long to run?

I am running postgresql v9.5 with postgis v2.2.

SELECT p.gid, w.wetland_ty, 
CASE WHEN ST_CoveredBy(p.geom, w.geom) 
   THEN p.geom 
   ELSE 
    ST_Intersection(p.geom, w.geom) END as geom
into suffolk_co_ny_prcl_data_wetlands_intsct
FROM suffolk_co_ny_prcl_data AS p, suffolk_co_ny_wetlands AS w
where ST_Intersects(p.geom, w.geom);

Here are the results of EXPLAIN:

"Nested Loop  (cost=0.28..7568923.81 rows=11749404 width=415)"
"  ->  Seq Scan on suffolk_co_ny_prcl_data p  (cost=0.00..304746.03 rows=1229103 width=265)"
"  ->  Index Scan using suffolk_co_ny_wetlands_geom_idx on suffolk_co_ny_wetlands w  (cost=0.28..1.10 rows=1 width=150)"
"        Index Cond: (p.geom && geom)"
"        Filter: _st_intersects(p.geom, geom)"
  • 1
    Even if they were all rectangles, that's still 15E9 checks, then 1-2 more for those that match. The ST_CoveredBy call is potentially an antioptimisation. – lynxlynxlynx Sep 17 '17 at 19:50
  • I'd have added this as a comment since I can't directly answer what is wrong, but I don't have enough reputation. }:-/ Like lynxlynxlynx I also don't think the ST_CoveredBy is helping anything. You end up making way more geometry comparisons than if you only did ST_Intersection, unless it's the case that most wetlands are completely covered by one and only one parcel. Another thing to consider is whether your spatial indexes are optimal. Did you just create them with default values? The place to go now is to have a look at the query analyzer and - for one thing - make sure that the query i – kthy Sep 17 '17 at 20:48
  • @kflaw are the input datasets available somewhere online as shapefiles / geojson so I could benchmark the join on my system? – bcollins Sep 17 '17 at 23:41
  • @bcollins the wetlands can be download from here: 128.104.224.198/State-Downloads/NY_shapefile_wetlands.zip and the parcels i pulled down from this web service: gis.co.suffolk.ny.us/ArcGIS/rest/services/BaseMap83/MapServer/1 – kflaw Sep 18 '17 at 0:36
  • 3
    Update your question by posting the output of your query with EXPLAIN at the beginning. – Mike T Sep 18 '17 at 7:33
5

You know I don't see anything wrong with your query. You clearly cribbed your SQL from http://postgis.net/2014/03/14/tip_intersection_faster/ which still seems like good advice to me. You want to avoid running everything through st_intersection so thus the st_coveredby is the way to go.

lynxlynxlynx is the best advice I see here. kthy should explain just what is this "tuning" they do for a gist index. I'd ask how much RAM you are running on your server? Is the data on an SSD? Have you tuned postgresql to make the most of your system? Letting large spatial analysis queries run overnight or for a day or two is not a strange idea to me. Sometimes you got a lot of data and not a lot of hardware.


Edit One

John's critique is valid. I should not beat about the bush.

My Answer: This SQL query is just fine as it is. I use the same syntax myself for intersecting layers via PostGIS. Seek performance improvements elsewhere in your system.


Edit Two

I don't have enough reputation to comment in the main thread.

I am asserting that the SQL query and the overall PostGIS workflow is just fine.

So I downloaded the wetlands shapefile and then scraped the 50,000 parcels off the Suffox county AGS server. I then popped them into PostGIS, transformed the wetlands from 5070 to 2263 and ran the query.

It took seven minutes on my machine resulting in 3,331 parcel/wetland intersections.

enter image description here


Cheers,

Paul

  • This strikes me as more of a series of questions (albeit reasonable ones) than an answer. – John Powell Sep 19 '17 at 14:20
  • Thanks for testing that. I have run it on a smaller subset myself but it definitely took longer than 7 minutes for me - I queried out all parcels 5 acres and larger and ran the analysis on those. The total number in the original layer is 500K. I know there are some very large wetland polygons that intersect some of the large parcels I think it may have something to do with those. – kflaw Sep 20 '17 at 20:55
  • There are 889,274 wetland polygons and 50,000 parcel polygons. In terms of the heavy lifting for me this is all processor bound for those seven minutes. One core is just pinned for that time presumably carefully evaluating the complex spatial relationships and then cutting the intersections. So faster processor would mean quicker results. You could also break the work up like John suggested by first doing the bounding box intersection step and then doing the intersections separately. But I think that will just show you that all the time is spent in the latter step. – pauldzy Sep 20 '17 at 21:09
  • No arguing with that. Good edits – John Powell Sep 21 '17 at 19:58
0

Three optimizations come to my mind:

  • As already mentioned, get rid of `ST_CoveredBy as it's probably hurting rather than helping
  • Make sure there are no indices on suffolk_co_ny_prcl_data_wetlands_intsct table, build them after inserting all the data
  • If you don't mind losing your data on database crash set the suffolk_co_ny_prcl_data_wetlands_intsct table to UNLOGGED, that's gonna speed the inserts up a bit as well
  • Thanks for your reply. st_covered by may not be helping but I've tried running it without now with no improvement. The intsct table gets created with this statement so there shouldn't be any indices. I will have to look at the UNLOGGED option. – kflaw Sep 18 '17 at 15:44

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.