1

When doing the query in postgres to one that has a column whose data type is geography(MultiLineString, 4326) throws me the following result:

select geom from base.osm_roads_free_1 limit 100;
"0105000020E610000001000000010200000002000000ACFE08C380585EC04B598638D6FD44409A93179980585EC0A48D23D6E2FD4440"

I applied the ST_AsText (geom) function and it gives me the following result:

"MULTILINESTRING((-121.382859 41.9831,-121.382849 41.983485))"

My intention is to get into different columns longitude and latitude, example:

LON   LAT
-120.41651 40.192448
-120.416214 40.192471
-120.416055 40.192489
-120.415904 40.192524
-120.415767 40.192549
-120.415651 40.192559
-120.41553  40.192563
-120.415375 40.192546
-120.415246 40.192526
-120.415097 40.192528
-120.414956 40.192539
  • 3
    It helps if you think "longitude, latitude", because you will then be looking at coordinates in X,Y order. – Vince Feb 20 '17 at 3:08
  • 3
    What you have is the lon/lat data in decimal degrees. You are querying a linestring which explains why you have the lon/lat of two different nodes on the line. – the_darkside Feb 20 '17 at 3:12
  • 4
    Welcome to GIS SE! As a new user please take the tour to learn about our focused Q&A format. It appears your result has already given your the coordinates (-121.382859 41.9831, -121.382849 41.983485) - what about this isn't what you were after? – Midavalo Feb 20 '17 at 3:12
  • You might find ST_X and ST_Y useful too, rather than ST_AsText. – John Powell Feb 20 '17 at 12:27
  • 1
    You still have a vertex order issue. There are no values south of 90 degrees. – Vince Feb 20 '17 at 20:30
9

This is what ST_DumpPoints is for:

SELECT ST_X((dp).geom) AS lon, ST_Y((dp).geom) AS lat
FROM (
    SELECT ST_DumpPoints(
        ST_GeogFromText('MULTILINESTRING((-121.382859 41.9831,-121.382849 41.983485))')::geometry
    ) AS dp
) AS foo;

Output:

"lon";"lat"
-121.382859;41.9831
-121.382849;41.983485
  • OP is using geography -- ST_DumpPoints() parameter needs to be casted to geometry – Vince Feb 20 '17 at 21:17
  • True. So that's just an additional ::geometry cast: ST_DumpPoints(my_geography_column::geometry). – Richard Law Feb 20 '17 at 21:19
  • Do you want me to tidy up with a GeogFromText and a cast? – Vince Feb 20 '17 at 21:24
  • Go right ahead. – Richard Law Feb 20 '17 at 21:47
  • 2
    Also try experimenting with ST_AsLatLonText((dp).geom) to show a human readable latitude, longitude. – Mike T Feb 20 '17 at 22:25
1

enter image description here

Listing the coordinates in the "reverse order" (Y, X), helps me:

lat        lon
40.192448 -120.41651
40.192471 -120.416214
40.192489 -120.416055
40.192524 -120.415904
40.192549 -120.415767
40.192559 -120.415651
40.192563 -120.41553
40.192546 -120.415375
40.192526 -120.415246
40.192528 -120.415097
40.192539 -120.414956

ref.: http://maptools.com/tutorials/lat_lon/definitions

lat Latitude

lon Longitude

As mentioned above, you can also use the following:

SELECT (ST_AsLatLonText('POINT (-120.41651 40.192448)'));
    st_aslatlontext
----------------------------
40°11'32.8"N 120°24'559.4"W

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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