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

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Allan Kamau-3
I have data that contains geographic coordinate values in various geographic coordinate system formats such as "degrees minutes seconds" and "degrees decimal minutes".
I would like to convert these geographic coordinate values into decimal degrees format.
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°
S 20 10.8035165 W 176 36.074496=    -20.1800586°, -176.6012416°

The "°" sign in the results is optional.

Allan.

Reply | Threaded
Open this post in threaded view
|

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

Paul Ramsey

On Dec 4, 2018, at 12:36 PM, Allan Kamau <[hidden email]> 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.


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;




Reply | Threaded
Open this post in threaded view
|

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

Allan Kamau-3
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 <[hidden email]> wrote:

On Dec 4, 2018, at 12:36 PM, Allan Kamau <[hidden email]> 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.


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;