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 
