I'm trying to solve a Line of Sight (LoS) problem using SQL in PostgreSQL and PostGIS. To do this I have a table pitch_at_point that contains an id, a point geometry and a pitch.
pitch_at_point(id integer,geom geometry,degrees float)
The id column's lowest value is the starting point for the LoS and the highest is furthest away. For each of these points I want to determine if there are any points with a lower id, which also has a higher pitch (degrees). If this is the case, then the point can't be seen.
I'm stuck trying to find a solution. I have tried using the recursive query as in the SQL below:
WITH RECURSIVE
walk_points AS
(
SELECT ARRAY[id] AS idlist,geom,degrees,id
FROM pitch_at_point
WHERE degrees = (SELECT max(degrees) FROM pitch_at_point)
UNION ALL
SELECT array_append(w.idlist, n.id) AS idlist,n.geom,n.degrees,n.id
FROM pitch_at_point n, walk_points w
WHERE n.degrees < any(SELECT n.degrees FROM pitch_at_point WHERE NOT
w.idlist @> ARRAY[n.id])
)
SELECT * FROM walk_points
I expected to get a return of all points that is preceded by points that have a higher pitch, but I only get a result of a single point, and always the same point even if I use WHERE n.degrees > any(.
I have a hard time figuring out the PostgreSQL recursive CTE so if anybody can help me on my way I would appreciate it.