1

I have a linestring geometry in my Postgres database. I wanted to extract the latitude-longitude coordinates of each point inside the linestring. Example

 "0102000020E610000002000000CCDA5CEB41D323406E2A9CEE86C54A40CCDA5CEB41D323406E2A9CEE86C54A40"
"0102000020E610000002000000AF5FB01BB6CD22407C7E18213C8E4A40AF5FB01BB6CD22407C7E18213C8E4A40"

Till now, I have executed the query

SELECT st_astext(geometry) from warning_vmz;

result

"LINESTRING(7.17999 52.66463,7.17899 52.66563,7.17857 52.66604)"
"LINESTRING(7.2860666 53.1664885,7.2860666 53.1664885)"

so I have tried the another function 

Select ST_DumpPoints(ST_GeomFromText(st_astext(geometry))) from warning_vmz;

output

"({1},01010000004C89247A19652240E8A4F78DAF8D4A40)"
"({2},010100000012BD8C62B965224076543541D48D4A40)"

If I use ST_DumpPoints(ST_GeomFromText(geometry)) then I get an error

ERROR:  parse error - invalid geometry
HINT:  "0102000020E6100000711300004C" <-- parse error at position 28 within geometry
********** Error **********
ndawson
  • 27,620
  • 3
  • 61
  • 85
Gautam Shahi
  • 169
  • 1
  • 6

1 Answers1

2

This is a syntax error, try this:

SELECT st_astext(((ST_Dumppoints (geometry)).geom)) as geom from warning_vmz;

note that this line is incorrect, "LINESTRING(7.2860666 53.1664885,7.2860666 53.1664885)" it is wrong, to add the new request to check the geometry for errors Why can PostGIS create LINESTRING from the same values of coordinates of the POINT type?

Cyril Mikhalchenko
  • 4,397
  • 7
  • 14
  • 45
  • I'm happy for you, keep trying... – Cyril Mikhalchenko Feb 03 '19 at 17:47
  • Thank you, SELECT st_astext(((ST_Dumppoints (geometry)).geom)) as geom from warning_vmz; gives all coordinates from linestring, how can I filter points for each linestring? – Gautam Shahi Feb 03 '19 at 17:51
  • try group by id, it should work – Cyril Mikhalchenko Feb 03 '19 at 17:53
  • I have accepted the answer, thank you. I wanted to create a grid of geographical coordinates 52.36, 9.8 with a distance of 1 km and trying to represent these point, can you please suggest some lead? I am stuck. – Gautam Shahi Feb 03 '19 at 18:38
  • Look at this answer - https://gis.stackexchange.com/questions/271234/creating-a-grid-on-a-polygon-and-find-number-of-points-in-each-grid/284894#284894 it will help you solve your problem or look for similar answers, there are enough of them... – Cyril Mikhalchenko Feb 03 '19 at 18:42