CODEWITHSUNDEEP
postgresqlgpsopenstreetmappostgis
altamont
altamont

Reputation: 463

Combine two linestrings in different rows using PostGIS

Let me give an example to explain the problem. I have a OSM file for a portion of a city, say Santa Clara, CA, USA which I loaded into Postgres+PostGIS. I am trying to create a database of all roads in the OSM file along with their GPS coordinates. Let us consider an example road called 'Central Expressway'. My query was 

SELECT NAME, OSM_ID, AS_TEXT(ST_TRANSFORM(WAY, 4326)) AS whole_road FROM planet_osm_roads WHERE NAME = 'CENTRAL EXPRESSWAY'

However this gives me different rows which correspond to different way segments of the same road (Central Expressway) with the GPS coordinates being stored as a linestring for each row. For example if obtain something like

Central Expressway |  id_1 | LINESTRING(x_1 y_1, x_2 y_2, x_3 y_3)


Central Expressway |  id_2 | LINESTRING(x_4 y_4, x_5 y_5)

I understand that is how the data was stored in the OSM file. My question is how do I concatenate the different linestrings into a single one, i.e. something like

Central Expressway |  id_new | LINESTRING(x_1 y_1, x_2 y_2, x_3 y_3, x_4 y_4, x_5 y_5)

Thanks in advance

Upvotes: 5

Views: 3760

Answers (1)

Patrick
Patrick

Reputation: 32254

In OSM, as in virtually all GIS systems, long roads are stored as multiple linestrings for topological reasons. If you want to get all coordinates of a single road, then you should first concatenate the geometries and only then convert to a WKT format:

SELECT name, As_Text(
               ST_LineMerge(
                 ST_Collect(
                   ST_Transform(way, 4326)
                 )
               )
             ) AS whole_road
FROM planet_osm_roads
WHERE name = 'CENTRAL EXPRESSWAY'
GROUP BY name;

ST_Collect() will group all linestrings that compose Central Expressway into a single multilinestring and ST_LineMerge() will then convert that to a single linestring in a topologically sensible way (i.e. ordered from one end point to the next starting point).

Upvotes: 6

Related Questions