Ideas

Treasure Data's primary idea portal. 

Submit your ideas & feature requests directly to our product requirements team! We look forward to hearing from you.

have the same output for TD_LAT_LONG_TO_COUNTRY and TD_IP_TO_COUNTRY_NAME

Right now, the two location UDFs report slightly different names for some countries. Ones we've found are:

'United States of America' vs 'United States'
'South Korea' vs 'Republic of Korea'
'Hong Kong S.A.R.' vs 'Hong Kong'

Ideally, there should be a single standard at least within TD for full county names.

  • Kevin Hill
  • Jun 10 2016
  • Likely to Implement
  • Kevin Hill commented
    June 10, 2016 23:34

    Here's my current best workaround, ip_address seems to have more standard names, so we will have to do 2x the computation if we want to map results from TD_LAT_LONG_TO_COUNTRY:

    CASE
    WHEN (location_latitude IS NOT NULL AND location_longitude IS NOT NULL) THEN
    CASE
    WHEN TD_LAT_LONG_TO_COUNTRY('FULL_NAME', location_latitude, location_longitude) IN ('United States of America', 'South Korea', 'Hong Kong S.A.R')
    THEN MAP( ARRAY['United States of America', 'South Korea', 'Hong Kong S.A.R'], ARRAY['United States', 'Republic of Korea', 'Hong Kong'])[TD_LAT_LONG_TO_COUNTRY('FULL_NAME', location_latitude, location_longitude)]
    ELSE TD_LAT_LONG_TO_COUNTRY('FULL_NAME', location_latitude, location_longitude)
    END
    WHEN ip_address IS NOT NULL THEN TD_IP_TO_COUNTRY_NAME(ip_address)
    END as country_name