CODEWITHSUNDEEP
sqlpostgresqlpostgis
JonasPedersen
JonasPedersen

Reputation: 133

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

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.

Upvotes: 3

Views: 122

Answers (2)

Alex
Alex

Reputation: 1673

This sounds like it could be solved with a window function.

This will give you a true/false flag for records where a previous degrees field (ordered by id) is greater than the degrees for the current row:

    case  when degrees < max( degrees ) over( 
            order by id 
            rows between unbounded preceding and 1 preceding 
          ) 
          then true 
          else false 
    end as higher_value_present

See SQLfiddle here: http://sqlfiddle.com/#!15/23196/1

From your question, I wasn't clear on whether there was a set of ids for each geom point that you want to apply this logic to? If this is the case, you can partition the window function by geom:

    case  when degrees < max( degrees ) over( 
            partition by geom
            order by id 
            rows between unbounded preceding and 1 preceding 
          ) 
          then true 
          else false 
    end as higher_value_present

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

I may be wrong, but isn't this simply:

select
  id, geom, degrees
from pitch_at_point
where exists
(
  select *
  from pitch_at_point before
  where before.id < pitch_at_point.id
  and before.degree > pitch_at_point.degree
);

This gets all points where a record with a lower ID has a higher pitch.

The same thing may be faster with the windowing version of MAX:

select id, geom, degrees
from
(
  select
    id, geom, degrees,
    max(degreees) over (order by id rows between unbounded preceding and 1 preceding) 
      as max_degrees_before
  from pitch_at_point
) data
where degrees < max_degrees_before;

Upvotes: 2

Related Questions