QQ - How do I get the name of the nearest city from a table of Geo Data?
- 1 Minute to read
- Print
- DarkLight
- PDF
QQ - How do I get the name of the nearest city from a table of Geo Data?
- 1 Minute to read
- Print
- DarkLight
- PDF
Article summary
Did you find this summary helpful?
Thank you for your feedback
Use the following actions:
- Load Geo Data (CreateTableFromFile: worldcities)
- Calculate the nearest city lookup (Engineering | Functions | Geo | Nearest : nearestcitylookup)
- NOTE: use Limiter #1
- Create a self-join key (Engineering | Functions | General | IntegerSequenceKey: KeyField)
- Join the table to itself (worldcities.keyfield -> worldcities.nearestcitylookup )
- Create the nearest city field (CopyDownDiscrete: worldcities.city)
To calculate the distance in km from the city to the nearest city, do the following:
- Use Dist2Nearest to calculate distance in meters (Engineering | Functions | Geo | Dist2Nearest: Distance to Nearest City)
- Divide by 1000 to get distance in km: (Engineering | Functions | Dmath | A/B: Distance to City (km)
[
{
"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?