generating a linestring from a set of points

[Brent Wood]

Hi,

I have ingested a log of GPS NMEA messages & generated a timestamped point for each RMC message.

I have some events with start & finish times, which are within the time range of the GPS messages.

I'm trying to devise an SQL which will build a linestring from the points for each station where the point's timestamp lies between the event start & finish times, with the points ordered by timestamp.

I have done this in Postgis, but need to do the same thing in Spatialite, so far with no success.

Does anyone have any suggestions?


Thanks,

   Brent Wood

[a.fu...@lqt.it]

Hi Brent,

have you tried using MakeLine() in its version acting as an
aggregate function?

this SQL spatial function is documented here:
https://www.gaia-gis.it/gaia-sins/spatialite-sql-5.1.0.html#p0

---------------
MakeLine( geom PointGeometry ) : LinestringGeometry

"a Linestring Geometry will be returned connecting all the input
Points (accordingly to input sequence)
aggregate function
---------------

basically something like this:

SELECT MakeLine(pt_geom)
FROM gps_points
WHERE <timestamp> BETWEEN <start> AND <finish>
ORDER BY <timestamp>;

best regards,
Sandro