Technical Article

Convert Lat/Lon Decimal Degrees to Trigesimal Codes

,

Latitudes and longitudes, whether in Decimal Degrees (DD) or Degrees-Minutes-Seconds (DMS), are difficult to handle with enough meaningful precision.  This script will convert DD into trigesimal form, simplifying rooftop location, with a single ten-position code. Typically DD are stored in two columns as decimal(9,6) or larger.

WITH
coordinate_pair (id, lat_deg, lon_deg) AS ( -- Replace with your source table
/**/SELECT 109876, 32.747338, -97.083928 UNION ALL
    SELECT 115432, 30, -90 
),
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 < 5 
)
/**/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
      FROM trigesimal_pair_place
  GROUP BY id

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating