More

Get lat and long in two columns as floats for all points in MULTILINESTRING in PostGIS?

Get lat and long in two columns as floats for all points in MULTILINESTRING in PostGIS?


ST_DumpPoints(geom)gives me something like this:

("{1,1}",0101000020E61000002DEDD45CEEB362C0CE1951DA1BC24E40) ("{1,2}",0101000020E6100000C94AD826F1B362C09D4B7BC72CC24E40)…

Where I want it to look like this

seq lat lon

1, 61.1, -149.1

2, 61.2, -149.2…

I've played around withST_AsTextandST_AsGeoJsonwhich gets me closer but I would like to parse it with SQL instead of the text.


There is a good example of this in the documentation forST_DumpPoints:

SELECT ogc_fid, (dp).path[1] AS seq, ST_AsText((dp).geom) AS wktnode, ST_X((dp).geom), ST_Y((dp).geom) FROM ( SELECT ogc_fid, ST_DumpPoints(the_geom) AS dp FROM my_table ) AS foo;

Returning:

ogc_fid;seq;wktnode;st_x;st_y 84;1;"POINT(1760789.64678326 5433999.35927963)";1760789.64678326;5433999.35927963 84;2;"POINT(1760016.74943794 5433941.5050153)";1760016.74943794;5433941.5050153 1;1;"POINT(1755882.28518908 5434644.98441615)";1755882.28518908;5434644.98441615 1;2;"POINT(1755953.62731641 5434610.42870749)";1755953.62731641;5434610.42870749 1;3;"POINT(1756017.76954237 5434602.51304863)";1756017.76954237;5434602.51304863 2;1;"POINT(1756055.96704621 5434673.63981803)";1756055.96704621;5434673.63981803