Converting coordinates into Longitude/Latatitude form that CartoDB understands?

Question

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?

Answer 1

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.