PostGIS Tilemill subquery

Question

I need to find a way to merge ways into one long lines with the same attributes. I heard I can use ST_Union but I don't know how. I have a postgresql database connect with tilemill. It works fine but I'm not familiar with postgis so I don't know how to write the subquery. What I want to achieve is to merge segments of ways into one.

Here is my subquery in tilemill:

( SELECT *
    FROM planet_osm_line
    WHERE route IN ('bicycle')

) AS data

And here is my table in postgresql:

These are ways converted from bicycle routes. I want to combine them into one. The reason is that when rendering every single segment gets split and on angles it doesn't look good.

http://pomorskieszlaki.pl/#16/53.7062/16.6908/osm-rowerowe

Answer 1

The PostGIS documentation on ST_Union should help. It has this example:

SELECT stusps,
ST_Multi(ST_Union(f.the_geom)) as singlegeom
FROM sometable As f
GROUP BY stusps

To get one record representing every feature meeting your WHERE criteria:

SELECT ST_Union(pol.geom)
FROM planet_osm_line as pol
WHERE route IN ('bicycle');

This will lose all attribute information (because there's no way to retain it and union all the features.

I think you need to first identify which criteria you want to merge on, and then use a GROUP BY clause:

SELECT foo, bar, ST_Union(pol.geom)
FROM planet_osm_line as pol
WHERE route IN ('bicycle')
GROUP BY foo, bar;

This will return as many records as there are unique combinations of foo and bar in your table, and no more. Each of these records will have one geometry, and the shared foo and bar attribute. Other attributes might be combined as appropriate to your use case. E.g. average path width in a group? Use AVG(width) in the SELECT. This is just a made up variable.