Timeline for Optimizing an intersection between a single massive multipolygon (WKT) and many features from PostGIS
Current License: CC BY-SA 4.0
34 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Oct 2, 2021 at 21:00 | history | tweeted | twitter.com/StackGIS/status/1444406864674889729 | ||
| Sep 29, 2021 at 20:01 | answer | added | swiss_knight | timeline score: 1 | |
| Sep 29, 2021 at 15:08 | history | edited | swiss_knight | CC BY-SA 4.0 |
added 432 characters in body
|
| Sep 29, 2021 at 14:22 | history | edited | swiss_knight | CC BY-SA 4.0 |
deleted 436 characters in body
|
| Sep 27, 2021 at 10:34 | answer | added | geozelot | timeline score: 4 | |
| Sep 26, 2021 at 9:55 | history | edited | swiss_knight | CC BY-SA 4.0 |
added 865 characters in body
|
| Sep 26, 2021 at 8:24 | history | edited | swiss_knight |
edited tags
|
|
| Sep 26, 2021 at 7:44 | history | edited | swiss_knight | CC BY-SA 4.0 |
added 227 characters in body
|
| Sep 26, 2021 at 7:33 | history | edited | swiss_knight | CC BY-SA 4.0 |
added 255 characters in body
|
| Sep 25, 2021 at 21:35 | history | edited | swiss_knight | CC BY-SA 4.0 |
added 71 characters in body
|
| Sep 25, 2021 at 12:26 | answer | added | ziggy | timeline score: 4 | |
| Sep 25, 2021 at 9:30 | comment | added | geozelot | Please bear in mind that throwing indexes at everything is not helpful. In theory, an index on the main decision column on the larger of two related tables is the most performant combination - while it would also work the other way around. In every case, indexes are considered for only one of two relations, i.e. one per joined relation. If your base table has an index that can be used, and is the running table, no need for another index elsewhere. | |
| Sep 25, 2021 at 9:04 | history | edited | swiss_knight | CC BY-SA 4.0 |
added 49 characters in body
|
| Sep 25, 2021 at 8:52 | history | edited | swiss_knight | CC BY-SA 4.0 |
deleted 31 characters in body
|
| Sep 25, 2021 at 8:21 | history | edited | swiss_knight | CC BY-SA 4.0 |
added 48 characters in body
|
| Sep 25, 2021 at 2:51 | comment | added | ziggy | i also suggest using a case statement and st_within if any of the polygons fall within the foo data | |
| Sep 25, 2021 at 2:48 | comment | added | ziggy | i suggest forcing a bounding box intersect before any of the hard geom spatial functions | |
| Sep 24, 2021 at 23:10 | review | Close votes | |||
| Sep 26, 2021 at 10:52 | |||||
| Sep 24, 2021 at 22:01 | history | edited | swiss_knight | CC BY-SA 4.0 |
added 366 characters in body
|
| Sep 24, 2021 at 21:23 | history | edited | swiss_knight | CC BY-SA 4.0 |
added 615 characters in body
|
| Sep 24, 2021 at 19:18 | comment | added | jbalk | I suggested a temporary table because you can add a spatial index to it, which you cannot do with a cte (WITH) | |
| Sep 24, 2021 at 19:14 | comment | added | swiss_knight |
Ah okay, so then this idea + ST_SubDivide() should be optimal. I guess. I'll try that... And to upgrade my main post to include relevant part instead of the '...' without bringing too much noise. Does it also make sense to add a spatial index on a temp table holding a single feature?
|
|
| Sep 24, 2021 at 19:12 | comment | added | jbalk | That query only runs the intersection if the polygon is not entirely covered by the big polygon. That's what st_coveredby is doing here. It still finds the intersection if the polygon is only partially covered by the big polygon. If the small polygon is covered completely by the big polygon, the entire small polygon is returned rather than running the intersection and getting the same result. | |
| Sep 24, 2021 at 19:11 | comment | added | geozelot |
ST_SubDivide your MultiPolygon (e.g. in a CTE)
|
|
| Sep 24, 2021 at 19:10 | comment | added | swiss_knight |
Well, actually ST_CoveredBy will not take into account features that are partially inside the MultiPolygon: postgis.net/docs/ST_CoveredBy.html. But I actually do need them. I need to clip them and to extract their centroid, regardless if they are fully covered by the MultiPolygon or only partially. Also; isn't a temporary table the same as using a CTE? I've read WITH is maybe better for such case: dba.stackexchange.com/a/78256/196371
|
|
| Sep 24, 2021 at 19:10 | comment | added | jbalk | You might want to consider creating a temporary table for the huge polygon. Then you can create a spatial index for the huge polygon as well | |
| Sep 24, 2021 at 19:06 | history | edited | swiss_knight | CC BY-SA 4.0 |
Add indexes
|
| Sep 24, 2021 at 19:03 | comment | added | jbalk |
SELECT p.parcel_id, n.nei_name, CASE WHEN ST_CoveredBy(p.geom, n.geom) THEN p.geom ELSE ST_Multi(ST_Intersection(p.geom,n.geom)) END AS geom FROM parcels AS p INNER JOIN neighborhoods AS n ON (ST_Intersects(p.geom, n.geom) AND NOT ST_Touches(p.geom, n.geom) ); This is the most efficient way to do an intersection of polygons. You should be able to adapt it to your case.
|
|
| Sep 24, 2021 at 18:55 | comment | added | swiss_knight |
Yes, I do have a spatial index both on the geom and its transformed version. Thanks for pointing that out. I unfortunately also need the centroids of the PostGIS features after they are clipped by the massive MultiPolygon. So I need the intersection.
|
|
| Sep 24, 2021 at 18:28 | comment | added | jbalk | See the second example here for how to do a transformation index postgis.net/docs/ST_Transform.html | |
| Sep 24, 2021 at 18:27 | comment | added | jbalk | Hey this is a good question(s), but it's 3 questions in one. Can you focus on one issue at a time (maybe ask multiple questions)? Also, can you share the rest of your code (the ... parts)? You say 'I need to get the smaller polygons that intersect the large polygon'. You don't need st_intersection for that. Do you also need to get the actual intersection between the polygons? One very obvious problem I see is that you're transforming all of your geometries. Unless you have a transformation index on both tables, the spatial index won't be used on either table. | |
| Sep 24, 2021 at 18:06 | history | edited | swiss_knight | CC BY-SA 4.0 |
added 6 characters in body
|
| Sep 24, 2021 at 17:54 | history | edited | swiss_knight | CC BY-SA 4.0 |
edited title
|
| Sep 24, 2021 at 17:43 | history | asked | swiss_knight | CC BY-SA 4.0 |