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:
The CTE gives you the
counties
geometries wherestudy_region_all
geometries are problematic based on your list of ids, and the following query coalesces the fixed geometries with the valid geometries.