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?
-
Maybe there is a contrary of this function: postgis.net/docs/ST_AsLatLonText.html– StefanCommented 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 FCommented 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 FCommented 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 FriendCommented 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 FCommented Jan 30, 2014 at 18:30
Add a comment
|
2 Answers
You may try this:
EDIT
Presuming this is your PostGIS table,
running the DMS2DD function gives this:
EDIT 2
Because you have only Degrees and Minutes,
in the DMS2DD function you need to comment one single line to achieve your results:
answered Jan 29, 2014 at 10:04
-
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)... Commented Jan 29, 2014 at 11:54
-
1I've updated my response. You don't need to split the strig fields to be able to calculate only from
Degrees and Minutes. Commented Jan 29, 2014 at 12:11
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.
