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
Improve this question
9
  • 3
    It helps if you think "longitude, latitude", because you will then be looking at coordinates in X,Y order. Commented Feb 20, 2017 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. Commented Feb 20, 2017 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? Commented Feb 20, 2017 at 3:12
  • You might find ST_X and ST_Y useful too, rather than ST_AsText. Commented Feb 20, 2017 at 12:27
  • 1
    You still have a vertex order issue. There are no values south of 90 degrees. Commented Feb 20, 2017 at 20:30

2 Answers 2

Reset to default
11

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
Improve this answer
5
  • 1
    OP is using geography -- ST_DumpPoints() parameter needs to be casted to geometry Commented Feb 20, 2017 at 21:17
  • True. So that's just an additional ::geometry cast: ST_DumpPoints(my_geography_column::geometry). Commented Feb 20, 2017 at 21:19
  • Do you want me to tidy up with a GeogFromText and a cast? Commented Feb 20, 2017 at 21:24
  • Go right ahead. Commented Feb 20, 2017 at 21:47
  • 2
    Also try experimenting with ST_AsLatLonText((dp).geom) to show a human readable latitude, longitude. Commented Feb 20, 2017 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
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.