Geo Lookup without IP

Follow

Ashley Beverlin

• November 07, 2018 16:48

I am curious since we do not gather customer IPs, but we do gather the local from which they select. I am looking for a way to possibly re-code the geo lookup to go off country code. Thoughts so far define country code US = IP 111.111.1.1 and then match case local with country code and trick it to think the IP is 111.111.1.1. I dont need specific lat and long but rather just broad country of origin think it will work?

0

• 

  Cory Singleton

  • November 14, 2018 20:13

  Hi Ashley, yes, this is possible. Alternatively, instead of faking an IP, you could also simply return a latitude and longitude that corresponds to somewhere in each country code for example:

  | if(country_code_in_log="US","39.7392") as latitude

  | if(country_code_in_log="US","-104.9903") as longitude

  | count by latitude,longitude

  As long as you have two columns named Latitude and Longitude, the map visualization will display it. 

  Hope this helps. If for some reason I have misunderstood, let me know and we can chat further. 

  0

  Comment actions Permalink
• 

  Ian Ashman

  • May 13, 2019 06:16

  Rather than IP Addresses, consider using a lookup table with the country code in the log.

  Find a source csv file of latitude/logitude for each country code and use this file in a search lookup.

  For example,

  The source file https://raw.githubusercontent.com/google/dspl/master/datasets/google/canonical/countries.csv

  country,latitude,longitude,name
  AD,42.546245,1.601554,Andorra
  AE,23.424076,53.847818,"United Arab Emirates"
  AF,33.93911,67.709953,Afghanistan
  AG,17.060816,-61.796428,"Antigua and Barbuda"
  AI,18.220554,-63.068615,Anguilla
  AL,41.153332,20.168331,Albania
  .... etc

  This lookup file can be integrated as follows:

  | lookup latitude,longitude from https://raw.githubusercontent.com/google/dspl/master/datasets/google/canonical/countries.csv on country=country_code_in_log
  | count by latitude,longitude

   

  0

  Comment actions Permalink
• 

  Stephen Fensom

  • January 29, 2020 20:55

  The source file listed above no longer works - it gives a 404.

  Do you know of another URL for the lat/lon for countries?

  0

  Comment actions Permalink
• 

  Jellou Mae Infante

  • March 26, 2021 07:54

  Hi Stephen Fensom,

  https://developers.google.com/public-data/docs/canonical/countries_csv

   

  0

  Comment actions Permalink

Please sign in to leave a comment.