Intersection operation in PostGIS, best practices?

[Carbguy]

I've been working with PostGIS more lately and wanted to get a better handle on doing intersection operations specifically, I'm trying to intersect a set of polygons like zoning areas with another layer say, parcel boundaries to get a breakdown of how each parcel overlaps with the zones. I'm using ST_Intersection, but I've noticed performance can tank when the datasets are large, are there any tricks or best practices for speeding this up like using ST_Intersects first to filter, or prepping geometries somehow?
How do you usually handle cases where the result of the intersection needs to retain attributes from both original layers, do you do it all in one query, or break it up?

 

[CROSS]

Hmm, big datasets can slow things down with ST_Intersection, so its smart to use ST_Intersects first to filter what actually overlaps. You can also try ST_SnapToGrid or prepping geometrie stuff to help speed things up. If you need to keep info from both layers, you can do it all in one query with a join, but sometimes splitting it into steps makes it easier to manage. Good luck,

 

[Meowzilla]

I've been working with PostGIS more lately and wanted to get a better handle on doing intersection operations specifically, I'm trying to intersect a set of polygons like zoning areas with another layer say, parcel boundaries to get a breakdown of how each parcel overlaps with the zones. I'm using ST_Intersection, but I've noticed performance can tank when the datasets are large, are there any tricks or best practices for speeding this up like using ST_Intersects first to filter, or prepping geometries somehow?
How do you usually handle cases where the result of the intersection needs to retain attributes from both original layers, do you do it all in one query, or break it up?

When you're dealing with big intersection ops, beyond the indexing and ST_Intersects pre-filter (which everyone else mentioned and are crucial!), sometimes it's also about your transaction management and hardware.

If you're doing this in a BEGIN; ... COMMIT; block, and it's a huge operation, ensure your work_mem setting in PostgreSQL is adequate.
Also, these operations are very CPU-intensive, so if your PostGIS server is strapped for cores or memory, that'll be your bottleneck.

For attributes, always try to keep them in one query. Select the intersected geometry, and then explicitly list the attributes you want from both tables. It's the most efficient way to ensure data integrity and avoid extra joins later.

 

[bananpud7]

I've been working with PostGIS more lately and wanted to get a better handle on doing intersection operations specifically, I'm trying to intersect a set of polygons like zoning areas with another layer say, parcel boundaries to get a breakdown of how each parcel overlaps with the zones. I'm using ST_Intersection, but I've noticed performance can tank when the datasets are large, are there any tricks or best practices for speeding this up like using ST_Intersects first to filter, or prepping geometries somehow?
How do you usually handle cases where the result of the intersection needs to retain attributes from both original layers, do you do it all in one query, or break it up?

for me, always use EXPLAIN ANALYZE before running complex queries on large datasets. This will show you the query plan and highlight where bottlenecks are occurring (like full table scans, poor index usage)