In the query below I am exporting only the endpoint of linestrings in my table routes to a json file.

My objective, however, is to export to a csv, with four separate columns - starttime, endtime, lat and lon

ogr2ogr -f GeoJSON endpoints.json  "PG:host=localhost dbname=dbname user=user password=password"  -sql "SELECT ST_AsText(ST_EndPoint(path::geometry)) from routes"

How could I change the query in psql below to include latitude and longitude of the endpoints as separate columns, using ST_AsText(ST_EndPoint(path::geometry)) ?

   \COPY (
        SELECT starttime, endtime, lat, lon from routes
    ) TO 'endpoints.csv' WITH CSV HEADER DELIMITER ',';
  • What's the structure of your routes table? – Micha Mar 12 '17 at 6:50

Use the ST_X and ST_Y Geometry Accessors to get the point coordinate values:

SELECT starttime, endtime, ST_Y(ST_EndPoint(geom)) AS lat, ST_X(ST_EndPoint(geom)) AS lon
FROM routes;

There is also ST_AsLatLonText for a human-readable format.

| improve this answer | |

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.