| | 63 | * '''Convert degree minutes seconds to long lat decimals ''' |
| | 64 | This is from Simon Greener's article [http://www.spatialdbadvisor.com/postgis_tips_tricks/115/dms2dd-for-postgis/ DMS2DD for PostGIS] |
| | 65 | and was an adaptation of his Oracle function described in [http://www.spatialdbadvisor.com/oracle_spatial_tips_tricks/114/converting-google-earth-formatted-longitude-latitude-points-to-decimal-degrees Converting Google Earth Formatted Longitude/Latitude points to decimal degrees] |
| | 66 | {{{ |
| | 67 | CREATE OR REPLACE FUNCTION DMS2DD(strDegMinSec varchar) |
| | 68 | RETURNS numeric |
| | 69 | AS |
| | 70 | $$ |
| | 71 | DECLARE |
| | 72 | i numeric; |
| | 73 | intDmsLen numeric; -- Length of original string |
| | 74 | strCompassPoint Char(1); |
| | 75 | strNorm varchar(16) = ''; -- Will contain normalized string |
| | 76 | strDegMinSecB varchar(100); |
| | 77 | blnGotSeparator integer; -- Keeps track of separator sequences |
| | 78 | arrDegMinSec varchar[]; -- TYPE stringarray is table of varchar(2048) ; |
| | 79 | dDeg numeric := 0; |
| | 80 | dMin numeric := 0; |
| | 81 | dSec numeric := 0; |
| | 82 | strChr Char(1); |
| | 83 | BEGIN |
| | 84 | -- Remove leading and trailing spaces |
| | 85 | strDegMinSecB := REPLACE(strDegMinSec,' ',''); |
| | 86 | -- assume no leading and trailing spaces? |
| | 87 | intDmsLen := Length(strDegMinSecB); |
| | 88 | |
| | 89 | blnGotSeparator := 0; -- Not in separator sequence right now |
| | 90 | |
| | 91 | -- Loop over string, replacing anything that is not a digit or a |
| | 92 | -- decimal separator with |
| | 93 | -- a single blank |
| | 94 | FOR i in 1..intDmsLen LOOP |
| | 95 | -- Get current character |
| | 96 | strChr := SubStr(strDegMinSecB, i, 1); |
| | 97 | -- either add character to normalized string or replace |
| | 98 | -- separator sequence with single blank |
| | 99 | If strpos('0123456789,.', strChr) > 0 Then |
| | 100 | -- add character but replace comma with point |
| | 101 | If (strChr <> ',') Then |
| | 102 | strNorm := strNorm || strChr; |
| | 103 | Else |
| | 104 | strNorm := strNorm || '.'; |
| | 105 | End If; |
| | 106 | blnGotSeparator := 0; |
| | 107 | ElsIf strpos('neswNESW',strChr) > 0 Then -- Extract Compass Point if present |
| | 108 | strCompassPoint := strChr; |
| | 109 | Else |
| | 110 | -- ensure only one separator is replaced with a blank - |
| | 111 | -- suppress the rest |
| | 112 | If blnGotSeparator = 0 Then |
| | 113 | strNorm := strNorm || ' '; |
| | 114 | blnGotSeparator := 0; |
| | 115 | End If; |
| | 116 | End If; |
| | 117 | End Loop; |
| | 118 | |
| | 119 | -- Split normalized string into array of max 3 components |
| | 120 | arrDegMinSec := string_to_array(strNorm, ' '); |
| | 121 | |
| | 122 | --convert specified components to double |
| | 123 | i := array_upper(arrDegMinSec,1); |
| | 124 | If i >= 1 Then |
| | 125 | dDeg := CAST(arrDegMinSec[1] AS numeric); |
| | 126 | End If; |
| | 127 | If i >= 2 Then |
| | 128 | dMin := CAST(arrDegMinSec[2] AS numeric); |
| | 129 | End If; |
| | 130 | If i >= 3 Then |
| | 131 | dSec := CAST(arrDegMinSec[3] AS numeric); |
| | 132 | End If; |
| | 133 | |
| | 134 | -- convert components to value |
| | 135 | return (CASE WHEN UPPER(strCompassPoint) IN ('S','W') |
| | 136 | THEN -1 |
| | 137 | ELSE 1 |
| | 138 | END |
| | 139 | * |
| | 140 | (dDeg + dMin / 60 + dSec / 3600)); |
| | 141 | End |
| | 142 | $$ |
| | 143 | LANGUAGE 'plpgsql' IMMUTABLE; |
| | 144 | |
| | 145 | }}} |
| | 146 | |
| | 147 | |
