4

I have a PostgreSQL/PostGIS database with a two string fields containing Lat and Long coordinates in degrees and decimal minutes, like this 45° 24.5' N 3° 43.01' E.

How can I convert them into decimal degrees, for use in ST_GeographyFromText?

5
  • Maybe there is a contrary of this function: postgis.net/docs/ST_AsLatLonText.html
    – Stefan
    Jan 29, 2014 at 9:40
  • I'm wondering if the [gps] tag is worth adding here, since GPS is one of the few modern use cases for DDMM.M?
    – Martin F
    Jan 30, 2014 at 0:02
  • And since there's no common tags linking a related Q, here it is: gis.stackexchange.com/questions/29525/…
    – Martin F
    Jan 30, 2014 at 0:03
  • @martinf I know that carrying the gps tag is not very nice but it's from the original data and I have to keep it in the database
    – GIS Friend
    Jan 30, 2014 at 9:30
  • I'm not sure what you mean. I didn't know "carrying the gps tag is not very nice". I'm not commenting on your DB -- just thought it might be a useful/relevant tag to add to this topic.
    – Martin F
    Jan 30, 2014 at 18:30

2 Answers 2

7

You may try this:

DMS2DD for PostGIS


EDIT

Presuming this is your PostGIS table,

enter image description here

running the DMS2DD function gives this:

enter image description here


EDIT 2

Because you have only Degrees and Minutes,

enter image description here

in the DMS2DD function you need to comment one single line to achieve your results:

enter image description here

2
  • Thanks for the info but I've already seen this function. The problem is that I've got Degrees Minutes (DM) and not Degrees Minutes Seconds (DMS) and therefore it does not work. The formula for calculation is easy: 45° 24.5' = 45° 24.5/60 = 45.4083 but I don't know how to split my string field to be able to calculate Decimal Degrees (DD)...
    – GIS Friend
    Jan 29, 2014 at 11:54
  • 1
    I've updated my response. You don't need to split the strig fields to be able to calculate only from Degrees and Minutes. Jan 29, 2014 at 12:11
1

In my opinion there exists no function that is the inverse of ST_AsLatLongText, but it is well discussed. It would be a very useful function.

For your business:

When you are familiar with R than you can use this function, which converts your coordinates from degrees to decimals.

After that you update your table with the new coordinates (best way would be a postgresql/postgis connection with R, see RPostgreSQL and DBI). And then you can use the ST_GeographyFromText function.

Your Answer

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

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