1

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.

5
  • I strongly recommend "lon,lat" order to avoid confusion. The ST_X() and ST_Y() functions can be used for this purpose with point data.
    – Vince
    Dec 4, 2015 at 21:53
  • That did it for me. Would you like to write the answer or should I?
    – cylondude
    Dec 4, 2015 at 22:34
  • @alpha-beta-soup already has it covered (just take out the ST_AsText line in the SELECT clause)
    – Vince
    Dec 4, 2015 at 22:36
  • Taking out ST_AsText would return the WKB (binary) representation of the point. You just need to do ST_X/ST_Y on the geometry to split it out as float values. Dec 4, 2015 at 22:48
  • 1
    The whole line from the answer, through "wktnode," would result in the minimum necessary -- fid,seq,x,y
    – Vince
    Dec 4, 2015 at 23:18

1 Answer 1

4

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
3
  • This is close to what I am looking for but I really just want a column with float values for lat and long seperately. Could you edit your query to reflect this so I can approve it?
    – cylondude
    Dec 4, 2015 at 22:38
  • ...sure, but it's just removing some of what is being included in the selection: just do 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
  • 1
    If you eliminate the 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.
    – Vince
    Dec 4, 2015 at 23:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.