parselatitude(latitudeText) parselongitude(longitudeText)
The parselatitude
and parselongitude
functions parse a
text (varchar
) latitude or longitude coordinate, respectively,
and return its value in decimal degrees as a double
. The
coordinate should be in one of the following forms (optional parts in
square brackets):
[H] nnn [U] [:
] [H] [nnn [U] [:
] [nnn [U]]] [H]
DDMM[.MMM...]
DDMMSS[.SSS...]
where the terms are:
A number (integer or decimal) with optional plus/minus sign. Only the first number may be negative, in which case it is a south latitude or west longitude. Note that this is true even for DDDMMSS (DMS) longitudes - i.e. the ISO 6709 east-positive standard is followed, not the deprecated Texis/Vortex west-positive standard.
d
deg
deg.
degrees
'
(single quote) for minutesm
min
min.
minutes
"
(double quote) for secondss
(iff d
/m
also used for degrees/minutes)sec
sec.
seconds
s
" may
only be used for seconds if "d
" and/or "m
" was
also used for an earlier degrees/minutes value; this is to help
disambiguate "seconds" vs. "southern hemisphere".N
north
S
south
E
east
W
west
A two- or three-digit degree value, with optional sign. Note that longitudes are east-positive ala ISO 6709, not west-positive like the deprecated Texis standard.
A two-digit minutes value, with leading zero if needed to make two digits.
A zero or more digit fractional minute value.
A two-digit seconds value, with leading zero if needed to make two digits.
A zero or more digit fractional seconds value.
Whitespace is generally not required between terms in the first format. A hemisphere token may only occur once. Degrees/minutes/seconds numbers need not be in that order, if units are given after each number. If a 5-integer-digit DDDMM[.MMM...] format is given and the degree value is out of range (e.g. more than 90 degrees latitude), it is interpreted as a DMMSS[.SSS...] value instead. To force DDDMMSS[.SSS...] for small numbers, pad with leading zeros to 6 or 7 digits.
EXAMPLE
insert into geotest(lat, lon) values(parselatitude('54d 40m 10"'), parselongitude('W90 10.2'));
CAVEATS
An invalid or unparseable latitude or longitude value will return
NaN
(Not a Number). Extra unparsed/unparsable text may be
allowed (and ignored) after the coordinate in most instances.
Out-of-range values (e.g. latitudes greater than 90 degrees) are
accepted; it is up to the caller to bounds-check the result. The
parselatitude
and parselongitude
SQL functions were
added in version 6.00.1300132000 20110314.