CODEWITHSUNDEEP
sqlpostgresql
Aman
Aman

Reputation: 353

Postgresql selecting window of rows based on conditions

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

  1. id - varchar(32)
  2. sequence - integer - resets to 1 for a new car trip
  3. timestamp - time at which device recorded gps data. It is shown as date in the below pic but assume it as timestamp
  4. Latitude - numeric
  5. 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:

enter image description here

Output:

enter image description here

Upvotes: 0

Views: 54

Answers (2)

Adam
Adam

Reputation: 5589

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

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35553

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.

Upvotes: 1

Related Questions