Adding Your own cities to Oracle DV - for the clueless

The aim of this post is to add your own cities to the Oracle geo-Json files in Oracle Data Visualization (DV). There are lots of smart options to create the geo files, that were already covered (extracting from Oracle database map, converting from maps Shapefile, based on image ...) this post describes how clueless person can do it with Excel.

This post is relevant for most types of Oracle Data Visualization (including desktop, OAC - cloud, on-prem). It was "quick and dirty" work and naturally can be greatly improved.

Introduction

There are 3 layer when you work with maps and DV.

1. The background map (such as Google Maps, Oracle Maps...) 
2. Your Data that contains information such as country or city names.
3. A layer that translates your data to geographical info such as specific location (lat&long point) or borders (a set of lat&long points). This layer is created in Oracle DV using geo-Json files.

We can see the existing maps/geo-json files in the Console:

You can see them as physical files as well. The DV desktop files are located in: C:\Users\YOUR_USER_NAME\AppData\Local\Temp\DVDesktop\config\fmwconfig\biconfig\catalogs\basemaps   (replace YOUR_USER_NAME with your windows user name)

In the file world_cities.json, a single city in this file looks like this: {"type":"Feature","_id":"293394","geometry":{"type":"Point","coordinates":[34.76807,32.08194]},"properties":{"City Name":"Tel Aviv-Yafo","ISO":"ISR","Country Name":"Israel","State Name":"Tel Aviv","Scalerank":"2","Country Code_State Province Name_City Name":"ISR_Tel Aviv_Tel Aviv-Yafo","Country Name_State Province Name_City Name":"Israel_Tel Aviv_Tel Aviv-Yafo","Country Name_City Name":"Israel_Tel Aviv-Yafo","Country Code_City Name":"ISR_Tel Aviv-Yafo"}},

In this case we translate the City Name (Tel Aviv-Yafo) to coordinates :[34.76807,32.08194]

The system finds your data and scans existing map files for it, then translates it to geo-location. As a result you can have the same point with multiple name(Tel Aviv-Yafo, Tel Aviv, Tel Aviv Yafo, TA-Israel....) including translation to your local languages...The system would work fine! The one thing that is best not to do is to have 2 rows with the same City Name.

So I wanted to take the existing world_cities.json and add all the locations in Israel, in English, Hebrew and Arabic. The original file has few cities in English names.

How do I find possible location? Google it! There are plenty of people that published free relevant data to your country, or you can right click a map such as google map and copy manually the location coordinates or get it from the URL (for example https://www.google.com/maps/@32.08194,34.76807,17z), just note in google the order is [lat, long] in our geo-json file it's [long,lat].
It's always a good idea to check first at Oracle Analytics store - Maps & Spatial. Maybe someone already did the hard work.

Solution

To make thing extra easy I created an Excel file with a set of calculations. All I care about is filling the first 6 columns: running number, City Name, State Name, Country Code, country name and Long,Lat. the result is column G that is result you want to add to world_cities.json. 

The excel file is located here.
Just delete the values in columns B, C, D, E, F
Copy line 2 (columns A - AG) to the bellow lines
Add your own data as described above:
B - City Name
C - State Name
D - Country Code
E - Country Name
F - Long,Lat


Copy column G result and Paste it as Values.
Add the Values to world_cities.json, before the last line (our data has a comma at the end of the line, it can't be the last data line).

Here is am example file world_cities-HEB.json with 76 major cities in Israel added in English, Hebrew and Arabic.
Here is an example of world_cities_Hebrew_All_Israel.json with over 1000 places in Israel added, only in Hebrew.

Of course if you don't need all the wold cities and need only the local cities for your case you can create your own smaller json file without the world cities, based on world_cities.json file structure (in that case you can greatly simplify the attributes list, if you want).

Last step is to upload the files as custom map layers.

For the DV Desktop or OAC (Oracle Analytic Cloud) we can upload the file using the Console:

Press "Add Custom Layer (click, or drag and drop GeoJSON file)"
Select the json file.
You are required to select Layer Keys. Select the "City Name" at least:

Now you can use this in your maps:

Most likely the layer would be selected automatically. You can manually select your preferred layer option in properties any way.

You can find the same in OAC. Check the Console for Maps or use the URL similar to:
http://SEVER:PORT/dv/ui?consoletab=manageMapDataTab