2

I have a PostGIS table containing linestrings. Some of these are parts of larger logical constructs (tracks, roads or boundaries). I have been going though the data using snapping tools to make sure that that coordinates match up properly.

I now want to change my "tracks" layer to have polyline features, one for each logical track so I can label them on the maps and export them to GPS as a single named entity, or convert them to a route.

I am pretty sure that the answer will involve ST_MakeLine but I cannot see how construct the query?

The line table has an attribute 'part_of' which has the name of the logical entity and a sequence attribute too.

Pointers to a good basic tutorial on working with various geometric constructs would be useful.

2

ST_UNION is what you are looking for: https://postgis.net/docs/ST_Union.html

However, if you've already cleaned up your data, it may be faster to use ST_COLLECT: https://postgis.net/docs/ST_Collect.html

Both will achieve what you are trying to do - create multilinestrings from linestrings

ST_UNION will produce a cleaner/more OGC compliant dataset as it will dissolve boundaries and re-order geometries to ensure there are no self-intersections.

Here is the documentation for ST_MULTI, which will return the geometry as a multigeometry: https://postgis.net/docs/ST_Multi.html

  • Thanks, these certainly look like they will do the job but what I can't figure out is how to actually use them in practice. The examples in the manuals don't make any sense to me. What I need is an example bit of SQL that builds polyline objects from a table of simple lines which have an attribute of belongs_to which defines aggregation. Should I ask another question with a lot more details? – Russell Fulton Jul 1 '17 at 2:04
  • I think I am there. This layer definition seems to do what I need: dbname='tiri_work' host=localhost port=5432 sslmode=disable key='part_of' table="(SELECT part_of, ST_Multi(ST_Collect(f.the_geom)) as multigeom FROM (SELECT part_of, (ST_Dump(the_geom)).geom As the_geom FROM base.line_master ) As f where part_of != '' GROUP BY part_of )" (multigeom) sql= I need to do more testing and data cleanup put it appears to be doing the 'right thing' – Russell Fulton Jul 1 '17 at 4:17

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.