1

I have a table which contains GPS track points from multiple vehicles.

What I want to do is to produce GeoJSON polygons to overlay each trip on a map over a range of dates.

My GPS table contains:

  1. id - a unique id
  2. device_id - a unique device id,
  3. vehicle_id - a unique vessel id
  4. timecol - the gps timestamp as a unix time stamp
  5. lat - latitude
  6. lon - longitude
  7. location - geom location
  8. distance_from_depot - km from the depot
  9. distance_from_depot_id - a unique depot_id

When the NMEA strings are are imported to my database, I run a trigger which calculates the location, the nearest depot_id, distance from the nearest depot.

To generate the polygons, I currently have a query which I run which delivers the data to my server, which then has to take that data and manipulate it to GeoJSON features with properties and geometry; a job which should really be done by the database layer.

My current query is contained within a function:

FUNCTION public.get_gps_tracks(p_vehicle_id integer, p_start_timestamp timestamp with time zone, p_end_timestamp timestamp with time zone)
RETURNS json
  LANGUAGE sql
AS $function$
    select 'GPS' as display_group, 
        array_agg(distinct entering_depot) as col4, 
        min(timecol) as start_time, 
        max(timecol) as end_time, 
        avg(lat) as data_a, 
        avg(lon) as data_b, 
    from gps_data 
    where 
        vehicle_id=p_vehicle_id 
        and timecol > p_start_timestamp  
        and timecol < p_end_timestamp 
    group by (round(timecol/300) sort by timecol;
$function$

I have had some success using a combination of row_to_json, array_to_json and array_agg, but not enough to get me that last 2% of the the problem.

How could I write this to return valid GeoJSON features, one trip per feature like the following GeoJSON . Valid GeoJSON

Perhaps the answer is to use a window function to detect a change in {col4} and group by that?

1

If I understand correctly, you want the path of the vehicles on geojson format. This query may guide you,

SELECT vehicle_id,
    ST_AsGeoJSON(ST_MakeLine(loc ORDER BY timecol)) As gjson
FROM ( SELECT DISTINCT ON (vehicle_id, round(timecol/300)) * FROM gps ) As foo
GROUP BY vehicle_id;

You can use this query on your function to obtain the geometries, just need to add the filters with the inputs vehicle_id, p_start_time and p_end_time.

References:

  1. PostGIS Doc - ST_MakePoint
  2. PostGIS Doc - ST_MakeLine
  3. PostGIS Doc - ST_AsGeoJSON

Test Data

create table if not exists gps (
    id serial,
    device_id int,
    vehicle_id int,
    timecol int,
    lat float,
    lon float,
    loc geometry('Point', 4326),
    distance_from_depot int,
    distance_from_depot_id int
);
INSERT INTO gps (
    device_id,
    vehicle_id,
    timecol,
    lat,
    lon,
    loc,
    distance_from_depot,
    distance_from_depot_id
) VALUES
    (1, 1, 0, 0, 0, ST_SetSRID(ST_MakePoint(0, 0), 4326), 0, 1),
    (1, 1, 100, 0.1, 0, ST_SetSRID(ST_MakePoint(0.1, 0), 4326), 1, 1),
    (1, 1, 1000, 1, 0, ST_SetSRID(ST_MakePoint(1, 0), 4326), 10, 1),
    (1, 1, 2000, 2, 0, ST_SetSRID(ST_MakePoint(2, 0), 4326), 20, 1),
    (1, 1, 3000, 1, 0, ST_SetSRID(ST_MakePoint(1, 0), 4326), 10, 1),
    (1, 1, 3900, 0.1, 0, ST_SetSRID(ST_MakePoint(0.1, 0), 4326), 0.1, 1),
    (1, 1, 4000, 0, 0, ST_SetSRID(ST_MakePoint(0, 0), 4326), 0, 1),
    (2, 2, 0, 0, 1, ST_SetSRID(ST_MakePoint(0, 0), 4326), 0, 1),
    (2, 2, 1000, 0, 1, ST_SetSRID(ST_MakePoint(0, 1), 4326), 10, 1),
    (2, 2, 2000, 0, 2, ST_SetSRID(ST_MakePoint(0, 2), 4326), 20, 1),
    (2, 2, 2200, 0, 2.2, ST_SetSRID(ST_MakePoint(0, 2.2), 4326), 22, 1),
    (2, 2, 2300, 0, 2.3, ST_SetSRID(ST_MakePoint(0, 2.3), 4326), 23, 1),
    (2, 2, 2400, 0, 2.4, ST_SetSRID(ST_MakePoint(0, 2.4), 4326), 24, 1),
    (2, 2, 3000, 0, 1, ST_SetSRID(ST_MakePoint(0, 1), 4326), 10, 1),
    (2, 2, 4000, 0, 0, ST_SetSRID(ST_MakePoint(0, 0), 4326), 0, 1);

Result

"vehicle_id","gjson"
1,"{""type"":""LineString"",""coordinates"":[[0,0],[1,0],[2,0],[1,0],[0.1,0]]}"
2,"{""type"":""LineString"",""coordinates"":[[0,0],[0,1],[0,2],[0,2.2],[0,2.4],[0,1],[0,0]]}"
3
  • Close but not quite. Each vehicle will have multiple paths. By checking 'col4' I can determine when a vehicle returns to a depot. So each path is defined as the time between depot visits. Also the GPS reports back every second, so the 'round(timecol/300)' allows me to reduce the resolution here to 5 min GPS readings. Some vehicles can be away from the depot for weeks on end...
    – Greg
    Mar 1 '20 at 3:18
  • How about having GROUP BY vehicle_id, col4 in above query?
    – JoeBe
    Mar 2 '20 at 0:32
  • @Greg, I have updated the answer, and added some sample data and result.
    – cabesuon
    Mar 2 '20 at 18:47
1

Thanks for your assistance. I have managed to beat this into submission with:

create or replace function public.map_vehicle_gps(p_vehicle_id integer, p_start_timestamp timestamptz, p_end_timestamp timestamptz)
returns json
language sql
as
$function$
select row_to_json(fc) gps from (select 'FeatureCollection' as type, 
array_to_json(array_agg(f)) as features
  from (select 'Feature' as type,
    json_build_object('start_time', min(coordinates.timecol), 'end_time', max(coordinates.timecol)) as properties,
    st_makeline(location::geometry order by timecol) as geometry
        from (select distinct on (vehicle_id, round(timecol / 300)) location,
          vessel_id, timecol, array_agg(distinct entering_depot) as in_depot
              from gps_data
              where "gps_data"."vessel_id" = p_vehicle_id
                and "gps_data"."timecol" between extract(epoch from p_start_timestamp::timestamp) and extract(epoch from p_end_timestamp::timestamp)
              group by location, vehicle_id, timecol
             ) as coordinates
        group by in_depot, vehicle_id
       ) as f) as fc;
$function$
;

I then call the function like this:

select public.map_vehicle_gps(6,'2019-12-04T00:00:00','2020-02-04T00:00:00');

Now, I just need to sort out the query plan and ensure its well indexed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.