SpatialDB Advisor

Home

PostGIS Blog Articles

Building polygons from overlapping linestrings requiring intersection

(read more..)

Converting Oracle Optimized Rectangles to PostGIS

(read more..)

DMS2DD for PostGIS

(read more..)

Filtering Rings in Polygon (PostGIS)

(read more..)

FOSSLIC - PostGIS presentations

(read more..)

How to apply spatial constraints to PostGIS tables

(read more..)

Implementing Oracle's GetVertices function in PostGIS - ST_DumpPoints

(read more..)

Loading and Processing GPX 1.1 files using PostgreSQL XML

(read more..)

Loading Point Data from a CSV File in PostGIS

(read more..)

Vectorization: Exploding a linestring or polygon into individual vectors in PostGIS

(read more..)

RSS / Atom

Email me

· Feb 19, 09:27 am by Simon Greener

Yesterday, I posted a short article on Converting Google Earth Formatted Longitude/Latitude points to decimal degrees. The Google Earth longitude/latitude values are expressed as a string containing degree, minute and seconds values with text separators as follows:

The function described was written in PL/SQL for Oracle.

Regina Obe emailed me today as she had read the article and had converted it to PostGIS (supplying me a copy).

You will notice, in the code that follows, that PL/SQL and PL/PgSQL are very similar, making conversion much easier than you might expect.

CREATE OR REPLACE FUNCTION DMS2DD(strDegMinSec varchar)
    RETURNS numeric
    AS
    $$
    DECLARE
       i               numeric;
       intDmsLen       numeric;          -- Length of original string
       strCompassPoint Char(1);
       strNorm         varchar(16) = ''; -- Will contain normalized string
       strDegMinSecB   varchar(100);
       blnGotSeparator integer;          -- Keeps track of separator sequences
       arrDegMinSec    varchar[];        -- TYPE stringarray is table of varchar(2048) ;
       dDeg            numeric := 0;
       dMin            numeric := 0;
       dSec            numeric := 0;
       strChr          Char(1);
    BEGIN
       -- Remove leading and trailing spaces
       strDegMinSecB := REPLACE(strDegMinSec,' ','');
       -- assume no leading and trailing spaces?
       intDmsLen := Length(strDegMinSecB);

       blnGotSeparator := 0; -- Not in separator sequence right now

       -- Loop over string, replacing anything that is not a digit or a
       -- decimal separator with
       -- a single blank
       FOR i in 1..intDmsLen LOOP
          -- Get current character
          strChr := SubStr(strDegMinSecB, i, 1);
          -- either add character to normalized string or replace
          -- separator sequence with single blank         
          If strpos('0123456789,.', strChr) > 0 Then
             -- add character but replace comma with point
             If (strChr <> ',') Then
                strNorm := strNorm || strChr;
             Else
                strNorm := strNorm || '.';
             End If;
             blnGotSeparator := 0;
          ElsIf strpos('neswNESW',strChr) > 0 Then -- Extract Compass Point if present
            strCompassPoint := strChr;
          Else
             -- ensure only one separator is replaced with a blank -
             -- suppress the rest
             If blnGotSeparator = 0 Then
                strNorm := strNorm || ' ';
                blnGotSeparator := 0;
             End If;
          End If;
       End Loop;

       -- Split normalized string into array of max 3 components
       arrDegMinSec := string_to_array(strNorm, ' ');

       --convert specified components to double
       i := array_upper(arrDegMinSec,1);
       If i >= 1 Then
          dDeg := CAST(arrDegMinSec[1] AS numeric);
       End If;
       If i >= 2 Then
          dMin := CAST(arrDegMinSec[2] AS numeric);
       End If;
       If i >= 3 Then
          dSec := CAST(arrDegMinSec[3] AS numeric);
       End If;

       -- convert components to value
       return (CASE WHEN UPPER(strCompassPoint) IN ('S','W') 
                    THEN -1 
                    ELSE 1 
                END 
               *
               (dDeg + dMin / 60 + dSec / 3600));
    End 
$$
    LANGUAGE 'plpgsql' IMMUTABLE;

SELECT round(dms2dd('43° 0''50.60"S'),9) as latitude,
       round(dms2dd('147°12''18.20"E'),9) as longitude;

Thanks Regina.

<<Implementing Oracle's GetVertices function in PostGIS - ST_DumpPoints >>Loading Point Data from a CSV File in PostGIS