Postgis Spatial Query

user_13989531 0
Viewed 59

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');
postgis postgresql sql
2 Answers

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.

user_22753880

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.

user_139895310
Related