Extract individual lines from a gps track that runs in a loop

curiousCat Source

I have a gps track that I imported into postgis. The track ran in a loop for around 8 hours and so the resulting table contains only one record though it shows several lines in QGIS. I would like to cut the track into several records, one record per loop. So far I have employed the st_intersection & st_intersects using a polygon that looks like a donut (with a gap) cutting the lines at its ends.

Below is the script (from here) I used to get the tracklines within the boundary polygon:

AS (SELECT st_intersection(brte.st_union, trk.geom_32651)
    FROM buffer35 AS brte,
        tracks_utmz51n AS trk
    WHERE st_intersects(brte.st_union, trk.geom_32651)); 


Still the table shows only one record even though the lines already start and end same as the donut. Is there an explode function (like in autocad) in postgis? The solution suggested here explodes it into point-to-point segments.

I'd like to try the code below (from Lhttp://amercader.net/blog/2011/04/exploding-multigeometries-with-postgis ~returns a set of the individual geometries that make up a multi one). If mpoly is for polygons, what is it for lines so I can substitute it?:

CREATE TABLE world_worldborders_3 AS
SELECT id,name,
FROM world_worldborders;


  1. I have a GPX track from a vehicle that ran 8 hours around a predefined route. This being a loop, the vehicle passes the same areas at different times. This was intended because I am trying to study travel time differences per hour.

  2. Using OGR2OGR I import the track(as is/default settings) unto a postgis database. The result is 6 tables, of which I only keep the tracklines and the trackpoints then discard the rest. I convert this to EPSG32651 from EPSG4326

  3. I initially intended to just work with the trackpoints and discard the tracklines as well, but the dilemma was, since it was in a loop, I couldn't just pick-out the points at the start and end of a road segment because there are other pairs on the same segment but at a different time interval. So maybe after 30-40 minutes, the vehicle passes then I have another start and end time at that segment.

  4. I need to be able to differentiate the pairs. The trackline per loop, I thought would help since that would serve as an ID for the pairs. ex. This pair from Loop#1 and so on.

  5. That's why I removed a small portion out of the route line, buffered the remaining in order to catch the tracklines then tried running the st_intersects & st_intersection thinking that it would cut the trackline into the loops I needed. Unfortunately, the output was a multiline. If I click on one loop, the rest are also selected.

  6. The solutions I've found so far used a pl/pgsql script. I cannot do that (I think) because this is a python class project. I'm basically running the postgis script through psycopg2 so I'm looking for a straightforward script.

  7. If there's really no way to try and break-up the trackline into several tracklines (1 per loop) with postgis, I guess I'll have to use ST_MAKELINE after I have made a loop identifier(to group by) with just python like discussed in Lhttp://www.bostongis.com/postgis_makeline.snippet ..something..something..or just do everthing with just python and a list of dictionaries containing the track points information.

Any ideas? Is there any other way? Thanks in advance!



answered 4 years ago Tommaso Di Bucchianico #1

The query to explode multilinestrings in individual linestrings is the same as for multipolygons. In your case would be:

SELECT id,(ST_Dump(the_geom)).geom FROM test;

Note that, in this query, "the_geom" is the name of the geometry column of the table "test" and must be change if you named the geometry column different. "geom" is the name of the geometry field of the dump object and must not be changed.

PS: actually the geometry column of your test table is named "st_intersections". It would be better to explicitly give a name to this column using a alias. Just change your select statement so:

AS (SELECT st_intersection(brte.st_union, trk.geom_32651) AS the_geom
    FROM buffer35 AS brte, .......

comments powered by Disqus