I am trying to create a query that will return a list of points that do not intersect with a line. For the most part it works great and very fast. However I would like to know how far away the nearest line is.
I have tried to use the st_distance function but it is returning null. Does anybody have any tips or guidance to help me solve this?
This is my SQL statement so far:
SELECT p.objectid, p.voidentifier, p.featuretypecd, p.heightagl, st_distance(p.geom::geography, l.geom::geography), p.geom
FROM vo_point p
LEFT JOIN vo_line l ON ST_Intersects(p.geom, l.geom)
WHERE l.featureid IS NULL
and p.featuretypecd in ('537','538','540','541','542','543','544');
I believe a subquery is what you're looking for:
SELECT p.objectid, p.voidentifier, p.featuretypecd, p.heightagl, p.geom,
(SELECT min(ST_Distance(p.geom::geography, l2.geom::geography)) FROM vo_line l2)
FROM vo_point p
LEFT JOIN vo_line l ON ST_Intersects(p.geom, l.geom)
WHERE l.featureid IS NULL
AND p.featuretypecd IN ('537','538','540','541','542','543','544');
This query should return all records where points and lines intersect, and it will also return the distance to the nearest line.
So after more than an hour I decided to abort and try a different approach. Instead I created two views:
create view pylon_temp as SELECT p.objectid, p.voidentifier, p.featuretypecd, p.heightagl, p.geom
FROM vo_point p LEFT JOIN vo_line l ON ST_Intersects(p.geom, l.geom)
WHERE l.featureid IS NULL and p.featuretypecd in ('537','538','540','541','542','543','544');
create view pylon_errors as
select p.objectid, p.voidentifier, p.featuretypecd, p.heightagl, min(st_distance(p.geom::geography, l.geom::geography))as distance, p.geom
from pylon_temp p, vo_line l
where st_dwithin(p.geom,l.geom,0.001)
group by p.objectid, p.voidentifier, p.featuretypecd, p.heightagl, p.geom
order by distance desc;
It may not be the most elegant way of going about this, but it is very quick and I can use the view in my web app / GIS applications.