PostGIS spatial view getting points within distance interval from line

Question

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.

Answer 1

I'm making the assumption that you have multiple records in your coastline table. That could cause these "false positives" if there is piece of coastline further than the nearest bit that still meets your join criteria.

The following query merges the coastline into one record, then joins on that. The INNER JOIN will work, you won't get duplicates (because there's only one coastline record) and the BETWEEN syntax is clearer to me...

CREATE OR REPLACE VIEW vw_pointsinterval_600_700z WITH (security_barrier=true) AS 
 SELECT  a.gid,
    a.geom
   FROM (SELECT st_collect(geom) as coast_geom from coastline) c
     INNER JOIN address a ON st_distance(a.geom,coast_geom) BETWEEN 600 AND 700

Answer 2

One approach would be to use a CTE and use ST_DWithin, which will efficiently use a spatial index to get all addresses that are within 700 meters of the coastlines and then a second query based on those results to only choose those that have a minimum distance of at least 600 meters. You can't use min(ST_Distance....) in a WHERE clause, so this was the most efficient approach I could come up.

CREATE OR REPLACE VIEW vw_pointsinterval_600_700z AS
WITH 
 candidates (gid, ageom, cgeom) AS 
  (SELECT a.gid, a.geom, c.geom   
   FROM   addresses a, coast  c 
   WHERE ST_DWithin(a.geom, c.geom, 700)),
mins(id, ageom, mindist) AS 
  (SELECT id, ageom, min(ST_Distance(ageom, cgeom)) AS mindist 
   FROM candidates 
   GROUP BY id, ageom) 
SELECT id, ageom 
FROM mins 
WHERE mindist > 600 GROUP BY id, ageom;