linestring output format by lat lon

Question

Is there a way to return linestrings from my postgis database in this type of format?

Line: 1, "Road" 35.62200200, -88.98259200 35.62203500, -88.98240800 35.62202700, -88.98231000 35.62180000, -88.98163400 35.62175700, -88.98149000 35.62172500, -88.97881200 35.62172000, -88.97798500 35.62169800, -88.97752400 35.62170300, -88.97736200 35.62173900, -88.97723200 35.62180600, -88.97715200 35.62292600, -88.97608000 35.62467700, -88.97441600 35.62482100, -88.97429800 End:

score 4 · Answer 1 · answered Jul 02 '12 at 21:39

4

you should concat the xy's

LINESTRING('|| s."X" ||' '||s."Y" ||','|| s."X" || ' '||s."Y" || ')'',2309)

hope too be usefull for you :)

answered Jul 02 '12 at 21:39

Santiago Elvira Ramirez

1,191
8
28

-91.427209995107873,35.698482084970728 -91.269197123447611,35.819554655906138 is what the result is so I can concat that? I'm lost. I just need lat and long returned. – Chris Jul 03 '12 at 01:19

Mike T · Answer 2 · 2012-07-04T19:52:57.610

0

Do you have a custom format, or is it known? There are several geometry output functions, and you can always parse their output into something new, for example:

SELECT 'Line: ' || id || ', ' ||
  trim(translate(ST_AsText(ST_FlipCoordinates(ST_Transform(the_geom, 4326))),
                 'MULTILINESTRINGZM()', '')) || ' End:' AS output
FROM nqatrkl;

Or you can dump the points and tinker around with them some other way:

SELECT id, time, (d).path, (d).path[1] AS part,
       ST_Y((d).geom) AS lat, ST_X((d).geom) AS lon
FROM (SELECT id, time, ST_DumpPoints(ST_Transform(the_geom, 4326)) AS d
      FROM nqatrkl) AS foo
ORDER BY (d).path;

edited Jul 04 '12 at 19:52

answered Jul 04 '12 at 04:18

Mike T

41,085
18
152
203

Dumping points would be great, I cant seem to make it work. my table is nqatrkl and I have a few fields like the_geom, id, time. Can you help me format the query correctly? I can deal with them as rows. – Chris Jul 04 '12 at 05:47
Its a custom format, the software takes a linestring 1.lat,lon 2.lat,lon – Chris Jul 04 '12 at 06:11

score 0 · Answer 3 · answered Jul 13 '12 at 05:54

This worked for me its a custom format, or is it known? There are several geometry output functions, and you can always parse their output into something new, for example:

SELECT 'Line: ' || id || ', ' ||
  trim(translate(ST_AsText(ST_FlipCoordinates(ST_Transform(the_geom, 4326))),
                 'MULTILINESTRINGZM()', '')) || ' End:' AS output
FROM nqatrkl;

Or you can dump the points and tinker around with them some other way:

SELECT id, time, (d).path, (d).path[1] AS part,
       ST_Y((d).geom) AS lat, ST_X((d).geom) AS lon
FROM (SELECT id, time, ST_DumpPoints(ST_Transform(the_geom, 4326)) AS d
      FROM nqatrkl) AS foo
ORDER BY (d).path;

linestring output format by lat lon

3 Answers3