Making lines from nodes using OpenStreetMap and PostGIS (+ TileMill)

Question

using openstreetmap data (in a postgis database), I would like to highlight all roads with a certain surface tag in tilemill.

I can query for the tag, doing

SELECT * FROM planet_osm_ways WHERE 'cobblestone'=any(tags)

which returns id, nodes, tags:

24204652    {262087470,262087665,262087666} {highway,residential,maxspeed,30,name,Landréstraße,postal_code,12621,surface,cobblestone}
...

I'm not sure how to proceed though, as tilemill expects geometry. I suppose, I somehow have to look up each id in nodes and combine everything to lines(?).

---

update:

I think I managed to make linestrings from node coordinates, doing

SELECT way_id, st_makeline(coord) AS way 
    INTO __test
    FROM (SELECT b.id AS way_id, ST_SetSRID(st_makepoint(lon, lat), 3857) AS coord
        FROM (SELECT id, unnest(nodes) AS node_id FROM planet_osm_ways WHERE 'cobblestone'=any(tags)) a 
        INNER JOIN planet_osm_nodes b 
            ON a.node_id=b.id
        ORDER BY way_id
    ) c
    GROUP BY way_id

but in tilemill nothing shows up.

---

update 2:

after adjusting the layer settings in tilemill, I'm getting an error now:

Detected out of bounds geographic extent (180,85.0511287798066,180,85.051) for layer 'cobblestone'. Please ensure that the SRS for this layer is correct. Its native extent is '686159783,145786972,692158671,153144983'

Answer 1

Now that you have a geom, you should be able to add the layer on Tilemill. Enter your request, surrounded by ( ...you request... ) as my_table (the name is not important, but you need one).

Be sure, when you add your postgis layer, to enter way_id as your unique id, way as your geometry, and you select the good SRS (by default it should be 900913, which is the old id for 3857 so it should be ok). Then press save & style and you should be able to see it.

If you don't, even if you don't have any error, and even after waiting some time, maybe try with only a few way_id to be sure that it's not a performance issu.

Update 2: if you look and the data (the table), you indeed have values over the limit. The raison is most probably because there is a problem with your SRID: using ST_SetSRID does NOT project the geom, it just set the SRID field inside the geom. When you use ST_SetSRID(st_makepoint(lon, lat), 3857) you have a point in 4326, and you say it's in 3857. Either use ST_Transform instead of ST_SetSRID, or simply set it instead to 4326 and set the SRS in Tilemill on WSG84 (which is the srid 4326).