For each row, find if any previous rows contains a higher value

Viewed 115

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.

2 Answers
Related