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?

  • 2
    You can't UPDATE a materialized view, except from its sources.
    – Vince
    Sep 10 at 16:03

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 (
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.

  • OK, this was the correct solution. I had to make a few tweaks but COALESCE and LEFT JOIN worked!
    – gwydion93
    Sep 10 at 18:46
  • 1
    Great to hear @gwydion93. I just typed this out without running and testing it, so I'm sure there's a syntax error somewhere. Feel free to update the answer.
    – Encomium
    Sep 10 at 19:03

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.