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?

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
GIS Friend
  • 435
  • 4
  • 10
  • Maybe there is a contrary of this function: http://postgis.net/docs/ST_AsLatLonText.html – Stefan Jan 29 '14 at 09: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 '14 at 00:02
  • And since there's no common tags linking a related Q, here it is: http://gis.stackexchange.com/questions/29525/what-is-the-origin-of-the-degrees-decimal-minutes-format – Martin F Jan 30 '14 at 00: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 '14 at 09: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 '14 at 18:30

2 Answers2

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

Sorin Călinică
  • 5,118
  • 1
  • 17
  • 23
  • 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 '14 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. – Sorin Călinică Jan 29 '14 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.

Community
  • 1
Stefan
  • 4,414
  • 1
  • 33
  • 66