PostGIS – Applying ST_Intersects in PostgreSQL Query

postgispostgresqlst-intersects

I have a PostgreSQL/PostGIS materialized view (study_region_all) with stream lines and a table of county boundary polygons (counties). They both have a column called co_fips but for a subset of items in the study_regions_all MV, the co_fips values are wrong and need to be replaced with the co_fips value from the counties that those stream lines are in. Previously, I had done this in Python using arcpy.Intersect_Analysis(). I know I can use ST_Intersects from PostGIS in a similar manner but I am not quite sure how to set the query up correctly. This is the query to get my first subset (before intersecting stream lines and county boundaries):

SELECT co_fips 
FROM data.study_regions_all
WHERE SUBSTRING(reach_id ,1,2) = ANY(ARRAY['60', '66', '72' ,'78']);

How would I apply ST_Intersects (or another function) to this query to get the county co_fips to replace the co_fips for the corresponding stream lines that fall within the county boundaries?

Best Answer

Ok. The generalized goal is to coalesce geometries A and B based on a condition.

I'll assume you know the IDs of the features you need to have fixed and that all rows in study_region_all have values in the geom field.

In that case, you can do:

WITH needsfixing as (
SELECT 
a.id,
b.geom
FROM
study_regions_all a
INNER JOIN counties b on ST_INTERSECTS(a.geom,b.geom) is True
--(alternate syntax: from a,b where ST_INTERSECTS(a.geom,b.geom))
WHERE a.id in (###list the ids for bad features###)
)

SELECT c.id,coalesce(d.geom,c.geom) as geom
FROM study_regions_all c
LEFT JOIN needsfixing d ON c.id = d.id

The CTE gives you the counties geometries where study_region_all geometries are problematic based on your list of ids, and the following query coalesces the fixed geometries with the valid geometries.

Related Question