Technical Article

Converting To/From User Friendly Trigesimal Geocodes

,

The Problem

As a data professional, I have often wished that there was a short, simple, and universal code to enable finding a place on earth. Addresses are nice but require a government authority to build a road, name it, and number its locations, and standardize its entry / use, and then they are far from short, or even universal. And while latitude/longitude coordinates are more universal and can be more exacting, they are unwieldly to use and complex.

This isn’t so much a challenge for information systems and the DBMSs underpinning them, but it is a huge problem for people.  After all, who wants to use decimal degrees (DD) for latitude (LAT) and longitude (LON) pairs to six digits behind the decimal in interpersonal communication? Especially, for instance, consider their use in emergency services with lives on the line where seconds count, mixing up say the fifth and sixth digits of a LAT/LON coordinate pair searching for an accident victim in a large wilderness area. Yes, there are ways around this, but not a single, universal method in use by everyone.

Consider instead of a decimal based system for LAT/LON, the benefit of using a trigesimal, or base 30, system. Why trigesimal?

  • It is much more compact than a decimal system, allowing people to say in 8 or 10 bytes what a DD system would require a pair of numbers in decimal (9,6) to express.
  • The numbers are evenly divisible into two, three, five, and six parts.
  • A practical limit on the quantity of human usable alphanumeric glyphs, with readability balancing easily confused character glyphs like “0”/“O”, “1”/“I”, “2”/“Z”, “3”/“E”, or “4”/“A”.

Trigesimal Geocodes seems ideal!

A Solution with an "Inventive Step"?

Unfortunately, someone already patented a trigesimal system, so that a version of the idea is now heavily IP encumbered. It is called the Natural Area Code (NAC). In its article on “Patents”, Wikipedia states that “Under the World Trade Organization's (WTO) TRIPS Agreement, patents should be available in WTO member states for any invention, in all fields of technology, provided they are new, involve an inventive step, and are capable of industrial application”. What “inventive step” does the patented trigesimal require? Any DD coordinate to trigesimal coordinate translation must go through relatively common mathematical steps:

  1. Origin Translation – moving the origin of the Cartesian coordinate system:

    FROM the intersection of the prime meridian and the equator

    TO the south pole where the anti-meridian starts

    SO that all DD numbers are positive.

  2. Pair Swapping – changing the order of LAT (Y) and LON (X), so the order is more conventional for a Cartesian system.
  3. Glyph Conversion – instead using each numeral {0, 1, 2 … 9} in combination, use those digits plus some 20 alphabetic characters.
  4. Recursively apply for X and Y:
    1. Basis Calculation – for each position / place, calculate a percentage value between 0 and 1 and multiply by 30 subtracting any remainder not yet accounted for in the base.
    2. Remainder Addition - for each position / place, add back that remainder for the next position / place.

Step 4 is routine for converting to other bases from base 10 (decimal); for example, hexadecimal, which commonly uses {A, B, C, D, E, F} for {10, 11, 12, 13, 14, 15} for its six additional glyphs in Step 3.

A Generalized Solution, Not NAC

Rather than argue whether Step 3 using “always consonants” for the other 20 characters is “inventive” or not (as the steps 1, 2, and 4 clearly are neither new nor inventive), we can avoid the issue by using a different character set:

'0123456789BCDFGHJKLMNPQRSTVWXY' -- not the same set as "Natural Area Code"

'0123456789BCDFGHJKLMNPQRSTVWXZ' --  "Natural Area Code"

Trigesimal System Features

Any trigesimal system will have the important features that the NAC claims:

“The efficiency of Natural Area Coding System is so significant that it can save 50% of memory for geodetic points, 75% for line sections, 87% for NAC areas and 94% for NAC regions.”

“…an eight-character NAC is an ideal universal address for postal services, delivery services, emergency services and taxi services because it can specify an area less than 25 by 50 meters anywhere in the world.

“…a ten-character NAC is a perfect property identity code for each property in the world, which specify a reference area less than 0.8 by 1.6 meters on a property anywhere in the world.”

WITH
coordinate_pair (id, lat_deg, lon_deg) AS ( -- Replace with your source table
/**/SELECT 109876, 32.747338, -97.083928 UNION ALL  -- Globe Life Arlington. TX
    SELECT 115432, 30.000000, -90.000000            -- Point in New Orleans, LA  
),
trigesimal_glyph_set (txt) AS ( -- Any trigesimal set of 30 characters
/**/SELECT '0123456789BCDFGHJKLMNPQRSTVWXY' -- not the same set as "Natural Area Code" 
),
trigesimal_pair_place /*RECURSIVE*/ (id, lon_pct, lon_glyph
                                       , lat_pct, lat_glyph, row_nbr ) AS (
/**/SELECT id
         , CAST( ( lon_deg + 180) / 360 AS decimal(9,9))
         , CAST( NULL                   AS char(1))
         , CAST( ( lat_deg + 090) / 180 AS decimal(9,9))
         , CAST( NULL                   AS char(1))
         , 0
      FROM coordinate_pair
UNION ALL
/**/SELECT id
         , CAST(  lon_pct * 30 - FLOOR( lon_pct * 30)         AS decimal(9,9))
         , CAST( SUBSTRING( txt, FLOOR( lon_pct * 30) + 1, 1) AS char(1))
         , CAST(  lat_pct * 30 - FLOOR( lat_pct * 30)         AS decimal(9,9))
         , CAST( SUBSTRING( txt, FLOOR( lat_pct * 30) + 1, 1) AS char(1))
         , row_nbr + 1
      FROM trigesimal_pair_place
CROSS JOIN trigesimal_glyph_set
     WHERE row_nbr &lt 5 
),
trigesimal_pair AS (
/**/SELECT id
         , STRING_AGG ( lon_glyph,'')
           WITHIN GROUP (ORDER BY row_nbr ) AS x_cd
         , STRING_AGG ( lat_glyph,'')
           WITHIN GROUP (ORDER BY row_nbr ) AS y_cd
   -- STRING_AGG is T-SQL similar to 
   -- LISTAGG in ISO/IEC 9075
      FROM trigesimal_pair_place
  GROUP BY id)
/**/SELECT id
         , x_cd
         , y_cd
      FROM trigesimal_pair
; -- Decimal Degrees to Trigesimal Geocode done
WITH -- Trigesimal Geocode to Decimal Degrees begin
trigesimal_pair ( id, x_cd, y_cd) AS ( -- shows
/**/SELECT 109875, '6W8P3', 'NFQ2Y' UNION ALL -- next sector SW of target
    SELECT 109876, '6W8P4', 'NFQ30' UNION ALL -- target sector in Arlington, TX
    SELECT 109877, '6W8P5', 'NFQ31' UNION ALL -- next sector NE of target
    SELECT 115432, '7H000','N0000'           -- sector in New Orleans, LA
),
trigesimal_glyph_set (txt) AS ( -- Any trigesimal set of 30 characters
/**/SELECT '0123456789BCDFGHJKLMNPQRSTVWXY' -- not the same set as "Natural Area Code" 
),
delimited_trigesimal_pair AS ( -- Begin Reversal back to Decimal Degrees
/**/SELECT id                  -- Insert delimiters (',')
         , STRING_AGG( SUBSTRING( x_cd, nbr + 1, 1), ',') AS delimited_x_cd
         , STRING_AGG( SUBSTRING( y_cd, nbr + 1, 1), ',') AS delimited_y_cd
      FROM (VALUES (0),(1),(2),(3),(4)) AS geocode_place_minus_1(nbr) 
CROSS JOIN trigesimal_pair 
  GROUP BY id),
trigesimal_pair_slot ( id, lon_pct, lat_pct) AS (
/**/SELECT id
 , SUM (( CHARINDEX( x.value, txt) - 1) / POWER( 30.0, nbr))
 , SUM (( CHARINDEX( y.value, txt) - 1) / POWER( 30.0, nbr)) 
   -- CHARINDEX(substring,string) is T-SQL for
   -- POSITION(substring IN string) in ISO/IEC 9075 SQL
      FROM delimited_trigesimal_pair
CROSS JOIN (VALUES (1),(2),(3),(4),(5)) AS geocode_place(nbr)
CROSS JOIN trigesimal_glyph_set
     CROSS -- CROSS APPLY is T-SQL for CROSS JOIN LATERAL in ISO/IEC 9075 SQL
 APPLY STRING_SPLIT( delimited_x_cd, ',', 1) x
 CROSS
 APPLY STRING_SPLIT( delimited_y_cd, ',', 1) y
 WHERE geocode_place.nbr = x.ordinal -- ordinal requires MSSS 2022
   AND geocode_place.nbr = y.ordinal
  GROUP BY id)
/**/SELECT id
         , CAST( lat_pct * 180 -  90 AS decimal(9,6)) AS lat_deg
         , CAST( lon_pct * 360 - 180 AS decimal(9,6)) AS lon_deg
      FROM trigesimal_pair_slot
;

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

SQLServerCentral Article

The Basics of Cryptography Part 2

Encryption in SQL Server is difficult to implement and manage, but it is being required more and more often. However understanding what encryption means is as important as being able to manage and implement it. Michael Coles brings us part 2 in his series on explaining the mysteries behind cryptography.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2005-08-17

11,003 reads

SQLServerCentral Article

An Interview with Idera CEO, Rick Pleczko

I was talking with the people over at Idera a few weeks ago and they mentioned that their CEO would be happy to give us a few minutes of his time. So I took them up on the offer and built a list of questions about Idera and SQL Server 2005. Take a moment to learn about the vendor view of the world and the impending release of SQL Server 2005.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2005-03-15

6,097 reads

SQLServerCentral Article

An Efficient Set-based Solution for Islands and Gaps

After reading a recent article on identifying islands and gaps in sequential numbers, one of our readers was inspired to develop a more efficient solution for fragmented data. New Author Goce Smilevski brings us his solution and supporting data to show how this can be done better.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

2.33 (3)

You rated this post out of 5. Change rating

2021-04-16 (first published: )

11,170 reads