Postgresql – Extract data along a path (lat, long, altitude and time)

postgispostgresql

I will be deploying PostGIS in the next few day. I've never done this before, nor have I ever worked with and geo-spacial data in the past.

I will have two datasets: weather data (A) and coordinate data (B).

  • (A) will have records containing a lat, long, altitude, time and a measurement value.

  • (B) will have records containing a lat, long, altitude and time.

I would like to pull out a bunch of records from (B), which together would represent a trajectory through space and time.

I would then like to, for each record extracted from (B), query (A) for the measurement value which corresponds to the lat, long, altitude and time from (B)… give or take some distance (eg, within 10 seconds or within 1km radius from the record from (B)).

An example would be I have a GPS device taped around my head and I am flying through the clouds. As I fly, I record my lat, long, altitude and time of measurement. When I get home, I want to look at the weather data that I have available and show what the temperature was at lat_x, long_x, altitude_x and time_x. I want to do this for each measurement that I made while flying so that I can work out what temperatures I experienced during my flight. However, since I know my measurements may not be 100% accurate, I'd like to give a bit of tolerance to the time of my measurement, as well as to the location (imagine I have a donut with radius r around my center).

How can I achieve this? What functions should I look to use?

Happy to clarify further if needed.

UPDATE

As requested, some example data & expected output.

  • altitude is in feet.
  • temp in deg c

Weather data (A):

CREATE TEMP TABLE weather AS
SELECT lat, lon, alt, time::timestamp, temp
FROM ( VALUES
  ( 23, 7  , 30000, '2017-01-01 00:01:00', -4  ),
  ( 35, 13 , 33000, '2017-01-01 00:01:30', -9  ),
  ( 45, 19 , 36000, '2017-01-01 00:02:00', -11 ),
  ( 53, 30 , 40000, '2017-01-01 00:02:30', -15 )
) AS t(lat, lon, alt, time, temp);

Flight path (B):

CREATE TEMP TABLE flightpath AS
SELECT id, lat, lon, alt, time::timestamp
FROM ( VALUES
  ( 1, 7 , 9 , 29000, '2017-01-01 00:01:05' ),
  ( 1, 35, 14, 34000, '2017-01-01 00:01:31' ),
  ( 1, 44, 19, 37000, '2017-01-01 00:01:59' ),
  ( 1, 55, 31, 40100, '2017-01-01 00:02:28' ),
  ( 2, 20, 9 , 60000, '2017-01-01 00:14:05' )
) AS t(id, lat, lon, alt, time);

In the above example, I would expect to extract all records from (B) where id = 1. I'd then want to go over each record in (A) and extract records within some range according to lat, lon, altitude & time. So, for example, I would like a tolerance of +-3 for lat, lon, altitude and time… I would then expect to see a new table:

id | lat | lon | altitude (feet) | time                | temperature (c)
------------------------------------------------------------------------
1  | 7   | 9   | 29000           | 2017-01-01 00:01:05 | no measurement 
1  | 35  | 14  | 34000           | 2017-01-01 00:01:31 | -9
1  | 44  | 19  | 37000           | 2017-01-01 00:01:59 | -11
1  | 55  | 31  | 40100           | 2017-01-01 00:02:28 | -15

Best Answer

First, to make sense of this we convert to use geography points, rather than lat-long. I assume this is in WGS84. We do this because measuring a difference in lat/long doesn't make any sense. As you approach the poles the differences between 3 degrees of lat/long will be nothing compared to the equator.

BEGIN;
  ALTER TABLE flightpath ADD COLUMN geog geography;
  UPDATE flightpath SET geog =
    ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography;
  ALTER TABLE flightpath DROP COLUMN lon, DROP COLUMN lat;
COMMIT;

BEGIN;
  ALTER TABLE weather ADD COLUMN geog geography;
  UPDATE weather SET geog =
    ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography;
  ALTER TABLE weather DROP COLUMN lon, DROP COLUMN lat;
COMMIT;

Now you can use ST_DWithin and datetime math.

SELECT
  id,
  f.alt,
  f.time,
  coalesce(w.temp::text, 'no temperature') AS temp
FROM flightpath AS f
LEFT OUTER JOIN weather AS w
  ON (
    (
      w.time - f.time < '3 minutes'::interval      -- diff in time
      AND  f.time - w.time < '3 minutes'::interval
    )
    AND abs(w.alt - f.alt) <= 1000                 -- diff in alt
    AND ST_DWithin(w.geog, f.geog, 1000000)        -- diff in distance (meters)
  )
WHERE id = 1
ORDER BY f.alt;

That outputs something like this..

 id |  alt  |        time         |      temp      
----+-------+---------------------+----------------
  1 | 29000 | 2017-01-01 00:01:05 | no temperature
  1 | 34000 | 2017-01-01 00:01:31 | -9
  1 | 37000 | 2017-01-01 00:01:59 | -11
  1 | 40100 | 2017-01-01 00:02:28 | -15
Related Question