# Converting coordinates into Longitude/Latatitude form that CartoDB understands?

Does anyone know how to convert this infomation into longitude/latitude coordinates for georeferencing on cartodb?

``````UTC: Posn: 03:00N – 105:10E
LT: Posn: 01:28.0S – 116:48.0E
LT: Posn: 08:44.7S - 013:17.9E
``````

These data represent locations in the middle of oceans.

Is there a simple rule/website for conversion?

• I expect these are degrees:minutes so the first coordinate pair is latitude:3.0, longitude:105.16667, then -1.4667 116.8 and so on. Commented Jul 3, 2016 at 4:59
• @mkennedy can you show me how you do the conversion? Commented Jul 3, 2016 at 10:06
• Divide the second number by 60. A W or S means a negative number. Commented Jul 3, 2016 at 14:01
• So LT: Posn: 08:44.7S - 013:17.9E would be 8.745 13.29833333? Commented Jul 4, 2016 at 5:53

I'd try to use a desktop software like a spreadsheet LibreOffice Calc or Microsoft Excel to process your data but if you really want to go the SQL way, this query parses a table with a field like yours and returns back a geometry. You will need a bit more of tuning for your use case but generally speaking this should work:

``````  WITH
data as (SELECT input_field FROM mytable),
splitted as (
SELECT
regexp_matches(
data.input,
'.*Posn:(.*):(.*)([S|N]).* - (.*):(.*)([E|W]).*'
) as data from data),
coordinates as (
SELECT
CASE WHEN data[3] ilike 'S' THEN
(data[1]::numeric + data[2]::numeric/60.0) * -1
ELSE data[1]::numeric + data[2]::numeric/60.0
END as LAT,
CASE WHEN data[6] ilike 'W' THEN
(data[4]::numeric + data[5]::numeric/60.0) * -1
ELSE data[4]::numeric + data[5]::numeric/60.0
END as LON
FROM splitted
)
SELECT
ST_AsText(
CDB_LatLng(lat,lon)
)
FROM coordinates
``````

First WITH clause is to get the data, second to split the input text into an array with the interesting components, third to convert them into lat/lon coordinates and the final one to generate the geometry.

• Can you show me how you use Microsoft Excel to convert the data? Commented Jul 5, 2016 at 6:57
• Sorry I haven't used Excel in ages, but the workflow should be similar to the one I posted: split the information into the pieces that give you the lat/lon and then perform the same calculation: degees + minutes/60. Commented Jul 5, 2016 at 11:14
• So to get lat/lon, I need to first get the sum of degress and minutes and then divide the sum by 60? Commented Jul 6, 2016 at 9:20
• Do I need to include a negative '-' sign when it is A or W? Commented Jul 6, 2016 at 9:20
• Yep, the result for the SQL is `POINT(13.2983333333333 -8.745)` being the first the longitude and the second the latitude Commented Jul 7, 2016 at 13:49