Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.
Sign up to join this community
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 with ST_AsText and ST_AsGeoJson which 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 for ST_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
SELECT ST_X((dp.geom), ST_Y((dp).geom) and remove lines 1 and 2. I won't edit it in case the more complete version helps someone else in a similar situation.
Dec 4, 2015 at 22:44
ogc_fid it significantly reduces the usefulness of the information (unless you only have one row). There's no reason not to accept this answer as-is.
ST_AsTextline in theSELECTclause)ST_AsTextwould return the WKB (binary) representation of the point. You just need to doST_X/ST_Yon the geometry to split it out as float values.