I have a point table (addresses) and and line table (coastline) in PostGIS. I would like to build a spatial view selecting the points that's has a minimum distance of 600 meters from the line and a maximum distance of 700 meters from the line. I have tried this:
CREATE OR REPLACE VIEW vw_pointsinterval_600_700z WITH (security_barrier=true) AS SELECT DISTINCT a.gid, a.geom FROM coastline c LEFT JOIN address a ON st_dwithin(a.geom, n.geom, 700::double precision) AND not st_dwithin(a.geom, n.geom, 600::double precision);
The spatial view is visualized in QGIS added two test buffers. One of 600 and one 700 meters from the coastline. This does not show the correct result:
The coastline is the red line, the purple color is a test buffer of 600 meters from the coastline. The light green is the 700 meter buffer and the green dots are addresses. Some random places the view returns addresses that's closer than 600 meters to the coastline? I would expect all the green points to be on the green polygon test buffer.