I installed Postgresql, Gis, SELECT postgis_full_version() reports,

 POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel.
 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16"
 LIBXML="2.9.3" LIBJSON="0.11.99"

I was able to import data for a single city, from a Geofabrik file, through osm2pgsql.

Now I'd like to export all pedestrian streets in this database in lat,lon,name format. Same street could show up in multiple rows, a street being composed of multiple line segments - that is fine.

I am able to use this query to get streets, and I believe they are line pieces, but how do I get the latitude / longitude associated with the beginning and end of that line?

SELECT name FROM planet_osm_line WHERE highway in ('pedestrian');

What would be the simplest query to do this?

  • 1
    SELECT concat(ST_X(ST_StartPoint(line)) ,', ', ST_Y(ST_StartPoint(line)) FROM planet_osm_line WHERE highway in ('pedestrian'); and possibly the same for ST_EndPoint? Dec 2 '16 at 11:55
  • 1
    Or simply ST_StartPoint/ST_EndPoint if you want to extract the geometry, rather than a string representation. Dec 2 '16 at 12:06
  • Hmm.. there was no line column, but this seemed to work, st_asText(st_startpoint(st_transform(way, 4326))).
    – BBSysDyn
    Dec 2 '16 at 12:57
  • 1
    OSM is in 4326 by default, so, you shouldn't really need ST_Transform. Dec 2 '16 at 12:59
  • 1
    OK, ignore me then, that looks like spherical mercator. You must have supplied some SRID switch to osm2pgsql. I use osmosis, which loads OSM in a more raw format, and is most definitely 4326. Dec 2 '16 at 13:04

A query to dump all streets with their beginning and end with lat/lon format

psql -d gis -t -A -F";" -c \
     "SELECT  osm_id,name,highway,\
     st_asText(st_startpoint(st_transform(way, 4326))), \
     st_asText(st_endpoint(st_transform(way, 4326))) \
     FROM planet_osm_line " > output.csv

The same street name can show up as multiple rows, with different osm_id's. It seemed that multiple pieces of the same street appear as different rows.

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.