1

I have a very extensive GPS track from which I am trying to reduce the number of plotted points. I though that I would be able to do so by grouping by a round of the timestamp /900 ( every 15 mins ), but thats not working.

Whats the correct way to reduce the resolution of the points returned.

SELECT row_to_json(featurecollection) AS route
        FROM (
               SELECT
                 'FeatureCollection'               AS type,
                 array_to_json(array_agg(feature)) AS features
               FROM (
                      SELECT
                        'Feature'                                                             AS type,
                        lg.gid                                                                AS id,
                        ST_AsGeoJSON(ST_MakeLine(lg.start_itudes3d, lg.end_itudes3d)) :: JSON AS geometry,
                        row_to_json((SELECT property
                                     FROM (SELECT
                                       round(start_time/3600),                                               
                                             lg.start_time,
                                             lg.end_time                                                 
                                             ) AS property))                      AS properties
                      FROM ( SELECT                 
                               itudes_3d::GEOMETRY                                    AS start_itudes3d,
                               MIN(timecol)                              AS start_time,
                               LEAD(itudes_3d::GEOMETRY) OVER (ORDER BY timecol ASC)  AS end_itudes3d,
                               LEAD(timecol) OVER (ORDER BY timecol ASC)                AS end_time,
                               MIN(id)                                                  AS gid
                             FROM gps_data
                             WHERE device_id = :vessel_id AND
                                   timecol >= 1499643073 AND
                                   timecol <= 1502321473 AND 
                                   active = TRUE AND
                                   deleted_at IS NULL
                             GROUP BY round(timecol/900), itudes_3d, timecol
                           ) AS lg
                    ) AS feature
             ) AS featurecollection
4
  • 1
    What didn't work? And in what way do you want to reduce points? Based on being close in distance or close in time ?
    – tilt
    Commented Aug 11, 2017 at 8:14
  • Our GPS units provide a very fine resolution. However in my front end I need to show the route they have taken overlaid in LeafletJS, which I am doing through GeoJSON. Furthermore I need the ability to click on the route and show the lat/lng and timestamp at that point which means I have to make up the route with lots of GeoJSON features (the ST_Makelines), each with their own popup details. Because of the high resolution and the fact that a track may occur over several weeks or months, I need to reduce the number of features to allow the UI to be responsive and not chew up all the memory.
    – Greg
    Commented Aug 11, 2017 at 22:40
  • So basically I am trying to reduce the number of points by time, although I could also have done it by distance...
    – Greg
    Commented Aug 11, 2017 at 22:42
  • In terms of what didnt work, the Group by round(timecol/900). I think it was because I also had to group by the itudes_3d and time col columns. I still ended up with over 46000 features for a days worth of GPS points regardless of whether I had the round in there or not.
    – Greg
    Commented Aug 11, 2017 at 22:47

1 Answer 1

1

You may want to use the ST_Simplify function:

— Returns a "simplified" version of the given geometry using the Douglas-Peucker algorithm.

This approach will simplify by distance instead of time like your approach.

Something like that should work:

select ST_Simplify(ST_MakeLine(itudes_3d))
from gps_data
where device_id = :vessel_id
and active = TRUE
and deleted_at IS NULL;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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