Custom Map Layers in Oracle Analytics: Latitude/Longitude Columns and GeoJSON

In 2020 I worked on putting Singapore customer data on a map by postal code and weighed three ways to do it: use latitude/longitude columns, find a public GeoJSON file, or build a GeoJSON file yourself. At the time I built the GeoJSON with Oracle Database Spatial and Map Builder, but that tool chain is no longer the recommended path. This post restates that experience against the current Oracle Analytics (OAC and OAS) documentation.

First decide: points or boundaries?

What to show	Examples	How
Points (locations)	Stores, gas stations, branches	Add latitude and longitude columns and set them in Location Details. No layer needed
Areas findable by name	Countries, major cities	For place-name columns, Oracle Analytics applies a system map layer automatically
Boundaries not in the system layers	Postal districts, sales territories, internal zones	Prepare a GeoJSON file and upload it as a custom map layer

The example back then, a list of gas stations, was point data. In hindsight, adding latitude and longitude from the postal code was enough; there was no need to build a separate GeoJSON layer for station locations. A custom layer is really needed when you want to shade boundaries (polygons) such as postal districts.

1. Show points with latitude and longitude columns

1. Add latitude and longitude columns to the dataset. If you only have postal codes, join a postal-code-to-coordinates table.
2. Numeric columns load as measures, so change “Treat as” for the latitude and longitude columns to attribute.
3. From the latitude column’s Options menu, open Location Details and set Location Type to Latitude; do the same with Longitude for the longitude column. When the column icon changes to a location icon, it is linked to the map.

My original note said latitude/longitude columns are mapped automatically, but the current Oracle tutorial describes setting the columns explicitly as above.

2. Upload boundaries as a GeoJSON layer

Prepare the GeoJSON

The file needs a .json extension and must conform to GeoJSON. Each feature’s properties needs a key whose values match a column in your data; that key becomes the “layer key” that links the data to the layer. Coordinates follow the GeoJSON standard: WGS84, longitude first.

{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "properties": { "district_code": "D01", "district_name": "Raffles Place" },
      "geometry": {
        "type": "Polygon",
        "coordinates": [[[103.8500, 1.2800], [103.8550, 1.2800], [103.8550, 1.2850],
                         [103.8500, 1.2850], [103.8500, 1.2800]]]
      }
    }
  ]
}

The coordinates above are an arbitrary rectangle that shows the structure, not a real postal district boundary. If your boundaries are in a Shapefile, GDAL’s ogr2ogr converts them to WGS84 GeoJSON.

ogr2ogr -f GeoJSON -t_srs EPSG:4326 districts.json districts.shp

• Size: Per the documentation, a single file can be up to 100 MB compressed, and total GeoJSON storage defaults to 200 MB compressed. Uploads automatically strip whitespace and round coordinates to six decimal places, and Oracle’s documentation also suggests shrinking files beforehand with tools such as mapshaper.org.
• Data source and license: Get boundary data from an official body or a source with clear terms of use. Some free public datasets allow only non-commercial use. Back then, too, I used a sample of postal-code coordinates from a public repository for testing and considered buying official data for production.

Upload and link

1. Go to Navigator → Console → Maps.
2. Under Add Custom Layer, choose GeoJSON and upload the file.
3. In the Map Layer dialog, under Layer Keys, select the key that matches your data column (for example, district_code) and save.
4. In the dataset, open the matching column’s Options menu → Location Details and select the new layer as its Map Layer.

Uploaded layers can be managed under Console → Maps → Map Layers → Custom Map Layers: change the name or layer keys, reload a new file, download, or delete.

If you want to build the GeoJSON in the database

If your boundaries or locations are already stored as SDO_GEOMETRY in Oracle Database, consider generating GeoJSON from the database instead of using Map Builder. My original note said Spatial required Enterprise Edition, but Oracle announced that Spatial features can be used without an extra license from December 2019. Confirm the exact terms for your edition in the Oracle Database Licensing Information guide.

Summary

For points, set latitude and longitude columns in Location Details; upload a GeoJSON custom layer only when you need boundaries that the system layers don’t have. The key is matching the layer key to a data column with the same values. The old MapViewer and Map Builder procedure is no longer the recommended path.

For cleaning up a date column with mixed formats, see Normalizing Mixed Date Formats with Regular Expressions in Oracle Analytics.