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

Thank you very much Paul. Your suggestions and input have spared me many
hours of trying to identify applications and functions to perform this
transformation.
I am now installing PostGIS.

Allan.

On Wed, Dec 5, 2018 at 1:25 AM Paul Ramsey <pramsey(at)cleverelephant(dot)ca>
wrote:

>
> On Dec 4, 2018, at 12:36 PM, Allan Kamau <kamauallan(at)gmail(dot)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
>
> 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;
>
>
>
>
>