parselatitude, parselongitude



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:

  • nnn

    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.

  • U A unit (case-insensitive):

    • d

    • deg

    • deg.

    • degrees

    • ' (single quote) for minutes

    • m

    • min

    • min.

    • minutes

    • " (double quote) for seconds

    • s (iff d/m also used for degrees/minutes)

    • sec

    • sec.

    • seconds

    • Unicode degree-sign (U+00B0), in ISO-8559-1 or UTF-8
    If no unit is given, the first number is assumed to be degrees, the second minutes, the third seconds. Note that "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".

  • H A hemisphere (case-insensitive):

    • N

    • north

    • S

    • south

    • E

    • east

    • W

    • west
    A longitude hemisphere may not be given for a latitude, and vice-versa.

  • DD

    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.

  • MM

    A two-digit minutes value, with leading zero if needed to make two digits.

  • .MMM...

    A zero or more digit fractional minute value.

  • SS

    A two-digit seconds value, with leading zero if needed to make two digits.

  • .SSS...

    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.


insert into geotest(lat, lon)
  values(parselatitude('54d 40m 10"'),
         parselongitude('W90 10.2'));


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.

Copyright © Thunderstone Software     Last updated: Tue Feb 21 13:46:30 EST 2017
Copyright © 2017 Thunderstone Software LLC. All rights reserved.