Postgresql selecting window of rows based on conditions

Question

I have a car trip data with different car trips. The relevant columns are

id - varchar(32)
sequence - integer - resets to 1 for a new car trip
timestamp - time at which device recorded gps data. It is shown as date in the below pic but assume it as timestamp
Latitude - numeric
Longitude

I am trying to find out car trips that are between a particular origin and destination point. If I enter origin as 40.34, 23.5 and destination as 40.75, 23.9 then the output would be as shown in second picture.

The first picture contains 2 car trips namely abc & def. 'abc' took place on December 18th while 'def' took on December 15th so def appears first in the output. The output table is ordered by timestamp column and sequence column and grouped by id. The output should also contain intermediate points between origin and destination.

I am unable to figure out how to find first trips that pass through particular points.

Input:

Output:

Adam · Accepted Answer · 2017-12-15 00:40:26Z

Assuming your car trip data table is named trips:

WITH starts_and_ends AS (
    SELECT
        starts.id,
        starts.sequence AS start_sequence,
        ends.sequence AS end_sequence
    FROM
        trips AS starts
        JOIN trips AS ends
        ON (starts.id = ends.id AND starts.sequence < ends.sequence)
    WHERE
        starts.latitude = 40.34 AND
        starts.longitude = 23.50 AND
        ends.latitude = 40.75 AND
        ends.longitude = 23.90
)
SELECT
    trips.*
FROM
    starts_and_ends,
    trips
WHERE
    trips.id = starts_and_ends.id AND
    sequence BETWEEN starts_and_ends.start_sequence AND starts_and_ends.end_sequence
ORDER BY
    trips.id,
    trips.sequence,
    trips.timestamp;

In WITH query I select start and end point ids and sequence number. Then join it with original table to show the trip.

Output:

abc 2   2017-12-18 40.34    23.50
abc 3   2017-12-18 40.56    23.80
abc 4   2017-12-18 40.75    23.90
def 2   2017-12-15 40.34    23.50
def 3   2017-12-15 40.55    23.59
def 4   2017-12-15 40.80    23.99
def 5   2017-12-15 40.75    23.90

Paul Maxwell · Accepted Answer · 2017-12-15 00:35:43Z

1

Try row_number() over()

SELECT
      *
FROM (
      SELECT
            t.*
          , ROW_NUMBER() OVER (PARTITION BY id ORDER BY sequence, timestamp) AS rn
      FROM yourtable t
      WHERE Latitude = 40.34
      AND Longitude = 23.5
      ) d
WHERE rn = 1

nb: not sure is timestamp is needed in the ordering, but could be used as tie-beaker perhaps.

answered Dec 15, 2017 at 0:35

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

Collectives™ on Stack Overflow

Postgresql selecting window of rows based on conditions

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related