PostGIS check if multiline string is included in polygon

Question

I want to return all records that the multiline-string is included in the polygon. In the photo below, it should return two records.
Green line (streets) is contained inside two neighbourhoods (white line)
I use ST_Contains(a."NeighbourhoodWatchGeometry", b.geom); in the WHERE clause. It doesn't return any record for that street.
FROM "NeighbourhoodWatch" a,
    gis_streets b
  WHERE 
st_contains(a."NeighbourhoodWatchGeometry", b.geom);

geozelot · Accepted Answer

It seems "included" here means intersects, rather than true containment; you want to run
SELECT nw.*
FROM   "NeighbourhoodWatch" AS nw
JOIN   gis_streets AS gs
  ON   ST_Intersects(nw."NeighbourhoodWatchGeometry", gs.geom)
;

Choose the actual SELECT list as needed.
Note that you'd do yourself a favor by avoiding anything but lowercase names for objects and relations. It's also an anti-pattern to add the relation name to its columns, like "NeighbourhoodWatch"."NeighbourhoodWatchGeometry".

Apart from the PostGIS docs, the Wikipedia article about the underlying DE-9IM spatial predicate definition has some general info about spatial relation functions, and this article by @dr_jts discusses the details of containment.

PostGIS check if multiline string is included in polygon

One Answer

Add your own answers!

Ask a Question