3

Based on the following tables:

Street

Street

Municipality

Municipality

I'd like to populate Street.NOM_ENT with Municipality.NOM_ENT and Street.NOM_MUN with Municipality.NOMBRE any time Street.geom is within Municipality.geom.

In other words, I have a table with all the streets in a country and I need to add to which state and county that street belongs.

I know how to check if Street.geom is within Municipality.geom, but I'm kind of new working with postgis/postgres and I'm not sure how to use this as a condition for an UPDATE query.

Improve this question
2
  • You could, easily, but it may not be what you want; any road that is not fully contained by (or within) would get no assignment, while, with ST_Intersects you'd get multiple assignments. I would use ST_Intersects(municipality.geom, ST_LineInterpolatePoint(road.geom, 0.5)) to do this (assign the area where the midpoint is found within - a better assumption and a unique assignment); are these roads strictly simple LineStrings (as opposed to MultiLineStrings?
    – geozelot
    Commented May 18, 2021 at 21:34
  • @geozelot I believe they are MultiLineStrings, not just LineStrings. Also, as far as the law around here says, it shouldn't be possible for a street/road to NOT be fully contained within a Municipality, meaning that it should be impossible for a road/street to be within more or less than 1 Municipality/State exactly. Based on this facts, ST_DWithin works just fine to return the data i need, I'm just lost in the UPDATE part based on the ST_DWithin.
    – Kurt Pelzer
    Commented May 18, 2021 at 21:58

1 Answer 1

Reset to default
2

If you are absolutely certain about the necessary restrictions of roads never crossing any areal boundary, use ST_Contains in the UPDATE like so

UPDATE <Street> AS s
  SET  "NOM_ENT" = m."NOM_ENT",
       "NOM_MUN" = m."NOMBRE"
FROM   <Municipalities> AS m
WHERE  ST_Contains(m.geom, s.geom)
;
Improve this answer
3
  • Yes!, this works perfectly, thank you very much.
    – Kurt Pelzer
    Commented May 19, 2021 at 14:32
  • 1
    @KurtPelzer very welcome. Consider upvoting my answer (ht the up arrow next to it) and, if it fully answers your question, to also accept it (hit the little tick next to it).
    – geozelot
    Commented May 19, 2021 at 14:35
  • 1
    I Tried, but since this is my first question ever, I don´t have enough reputation yet. I'll come back to upvote as soon as I can.
    – Kurt Pelzer
    Commented May 19, 2021 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.