QQ - How do I get the name of the nearest city from a table of Geo Data?
  • 1 Minute to read
  • Dark
    Light
  • PDF

QQ - How do I get the name of the nearest city from a table of Geo Data?

  • Dark
    Light
  • PDF

Article summary

Use the following actions:

  1. Load Geo Data (CreateTableFromFile: worldcities)
  2. Calculate the nearest city lookup (Engineering | Functions | Geo | Nearest : nearestcitylookup)
    1. NOTE:  use Limiter #1
  3. Create a self-join key (Engineering | Functions | General | IntegerSequenceKey: KeyField)
  4. Join the table to itself (worldcities.keyfield -> worldcities.nearestcitylookup )
  5. Create the nearest city field (CopyDownDiscrete: worldcities.city)

To calculate the distance in km from the city to the nearest city, do the following:

  1. Use Dist2Nearest to calculate distance in meters (Engineering | Functions | Geo | Dist2Nearest: Distance to Nearest City)
  2. Divide by 1000 to get distance in km: (Engineering | Functions | Dmath | A/B: Distance to City (km)


Image Caption

 


[
  {
    "method": "CreateTableFromFile",
    "action": "LOAD",
    "table": "worldcities",
    "filename": "%DATAPATH%Demo_Modelling/BikeOverview/worldcities.csv",
    "definition": [
      "city|DISCRETE|STRING|BYTE",
      "city_ascii|DISCRETE|STRING|BYTE",
      "lat|DISCRETE|DOUBLE|BYTE",
      "lng|DISCRETE|DOUBLE|BYTE",
      "country|DISCRETE|STRING|BYTE",
      "iso2|DISCRETE|STRING|BYTE",
      "iso3|DISCRETE|STRING|BYTE",
      "admin_name|DISCRETE|STRING|BYTE",
      "capital|DISCRETE|STRING|BYTE",
      "population|DISCRETE|DOUBLE|BYTE",
      "id|DISCRETE|INTEGER|BYTE"
    ],
    "loading": [
      "city",
      "city_ascii",
      "lat",
      "lng",
      "country",
      "iso2",
      "iso3",
      "admin_name",
      "capital",
      "population",
      "id"
    ],
    "preHeaderSkip": 0,
    "dateFormat": "YYYY-MM-DD",
    "fileFormat": "ASCII8",
    "skipFirstLine": true,
    "delimiter": "COMMA",
    "stripCharacter": "",
    "dateTimeFormat": "YYYY-MM-DD HH:MM:SS",
    "sample": false,
    "project": "Matching1"
  },
  {
    "method": "BuildBakedField",
    "targetTable": "worldcities",
    "overwrite": true,
    "name": "NearestCityLookup",
    "function": "geo",
    "p1": "NEAREST(LT,LN,TLT,TLN)",
    "p2": "lat",
    "p3": "lng",
    "p4": "worldcities.lat",
    "p5": "worldcities.lng",
    "limiter": "#1",
    "project": "Matching1"
  },
  {
    "method": "BuildBakedField",
    "targetTable": "worldcities",
    "overwrite": true,
    "name": "KeyField",
    "function": "integersequencekey",
    "project": "Matching1"
  },
  {
    "method": "AddJoin",
    "primaryTable": "worldcities",
    "primaryField": "KeyField",
    "foreignTable": "worldcities",
    "foreignField": "NearestCityLookup",
    "project": "Matching1"
  },
  {
    "method": "BuildBakedField",
    "function": "copydowndiscrete",
    "targetTable": "worldcities",
    "name": "nearest city",
    "overwrite": true,
    "p1": "city",
    "p2": "worldcities",
    "project": "Matching1"
  },
  {
    "method": "BuildBakedField",
    "targetTable": "worldcities",
    "overwrite": true,
    "name": "Distance To Nearest City",
    "function": "geo",
    "p1": "DIST2NEAREST(LT,LN,TLT,TLN)",
    "p2": "lat",
    "p3": "lng",
    "p4": "worldcities.lat",
    "p5": "worldcities.lng",
    "limiter": "#1",
    "project": "Matching1"
  },
  {
    "method": "BuildBakedField",
    "project": "Matching1",
    "targetTable": "worldcities",
    "overwrite": true,
    "name": "Distance To City (km)",
    "function": "dmath",
    "p1": "A/B",
    "p2": "Distance To Nearest City",
    "p3": "#1000"
  }
]

Was this article helpful?