Re: Geographic coordinate values format conversion to DD (Decimal Degrees) format

[Paul Ramsey]

> On Dec 4, 2018, at 12:36 PM, Allan Kamau <kamaual...@gmail.com 
> <mailto:kamaual...@gmail.com>> wrote:
> 
> Does PostgreSQL (more specifically PostGIS) have functions for these types of 
> conversions.
> 
> Below are examples of the geographic coordinates values I have coupled with 
> the resulting decimal degrees values.
> 39.529053 N 107.772406 W=    39.5290530°, -107.7724060°
> 27.485973 S 153.190699 E=    -27.4859730°, 153.1906990°
> 30°32’39” N, 91°07’36” E=    30.5441667°, 091.1266667°
> 27.485973 S 153.190699 E=    -27.4859730°, 153.1906990°
> 1¡20'1N 103¡45'15E=    01.3336111°, 103.7541667°
SELECT st_astext(txt2geometry('S 20 10.8035165 W 176 36.074496'));
> =    -20.1800586°, -176.6012416°
> 
> The "°" sign in the results is optional.


Nope, you’ve got a big ugly pattern matching problem there, unfortunately, and 
probably are going to have to regex your way out of the bag. PostGIS will help 
you output forms like that, but it doesn’t have any general handling of 
arbitrary DMS strings.

http://postgis.net/docs/manual-2.5/ST_AsLatLonText.html 
<http://postgis.net/docs/manual-2.5/ST_AsLatLonText.html>

Here’s a PLPGSQL example that does half of your cases.

CREATE OR REPLACE FUNCTION txt2geometry(textcoord text)
RETURNS geometry AS 
$$
DECLARE 
        textarr text[];
        sep text;
        lon float8;
        lat float8;
BEGIN
        textarr := regexp_matches(textcoord, '(\d+)(\D?)(\d{2})\D?([\d\.]+)\D? 
([NS]),? (\d+)\D?(\d{2})\D?(\d+)\D? ([EW])');
        sep := textarr[2];
        RAISE NOTICE '%', textarr;
        -- DD.DDDDDD
        IF sep = '.' THEN
                lat := int4(textarr[1]) + int4(textarr[3]) / 100.0 + 
float8(textarr[4]) / pow(10, length(textarr[4])) / 100;
                lon := int4(textarr[6]) + int4(textarr[7]) / 100.0 + 
float8(textarr[8]) / pow(10, length(textarr[8])) / 100;
        -- DD.MM'SS"
        ELSE
                lat := int4(textarr[1]) + int4(textarr[3]) / 60.0 + 
float8(textarr[4]) / pow(10, length(textarr[4])) / 36;
                lon := int4(textarr[6]) + int4(textarr[7]) / 60.0 + 
float8(textarr[8]) / pow(10, length(textarr[8])) / 36;
        END IF;
        IF textarr[5] = 'S' THEN
                lat := -1 * lat;
        END IF;
        IF textarr[9] = 'W' THEN
                lon := -1 * lon;
        END IF;
        RETURN ST_SetSRID(ST_MakePoint(lon, lat), 4326);
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE
COST 100;