In the world of data visualization, mapping data to specific geographic locations can provide powerful insights. However, creating custom map layers, especially for unique geographic divisions like postal codes, can be challenging. This guide will walk you through the process of creating a custom map layer for Singapore's postal codes, which can be used in Oracle Data Visualization (DV) or similar tools.
The Challenge
The primary goal is to obtain a map tile layer in GeoJSON format, which can be imported when creating a custom map layer in Data Visualization. We'll focus on visualizing business data using postal codes.
Options for Visualization
There are three main approaches to visualizing business data with postal codes:
1.
Add Latitude/Longitude attributes to your data and use these attributes for map visualization.
2.
Obtain a free/open source GeoJSON file that includes Singapore postal codes.
3.
Build a customized GeoJSON file or purchase a map shape file containing Singapore postal codes.
Let's explore each option in detail.
Option 1: Using Latitude/Longitude Attributes
This is the simplest approach if you already have latitude and longitude data for your locations.
1.
Prepare your dataset to include latitude and longitude columns:
Image 1: Sample dataset with Lat/Long attributes
2.
When Lat/Long attributes are included in your business data, DV can automatically visualize this data on a map without additional map layers:
Image 2: Map visualization using Lat/Long attributes
Option 2: Free/Open Source GeoJSON File
Unfortunately, finding a free, ready-to-use GeoJSON file for Singapore postal codes is challenging. As of now, this isn't a viable option unless you discover a volunteer-created resource.
Option 3: Building a Custom GeoJSON File
This option requires more effort but provides the most control and customization. We'll walk through the process of creating a custom map layer for Singapore Petroleum Company (SPC) stations.
Prerequisites
•
Oracle Database Enterprise Edition (for utilizing Spatial features)
•
•
Image 3: Sample of SPC station data
Prepare Postal Code Data
1.
Create a database table (SG_MAP_POSTAL_CODE) to manage master data for all Singapore postal codes:
Image 5: SG_MAP_POSTAL_CODE table structure
Note: For production use, consider purchasing up-to-date data from SingPost or other official sources.
Prepare and Enrich Station Data
1.
Create a table (SG_SPC_STATIONS) to store the SPC station list:
2.
Join SG_SPC_STATIONS and SG_MAP_POSTAL_CODE to add Lat/Long information:
SELECT a.STATION_KEY, a.BRANCH, a.POSTAL_CODE, b.LATITUDE, b.LONGITUDE, b.ADDRESS, b.BLK_NO, b.BUILDING
FROM SG_SPC_STATIONS a, SG_MAP_POSTAL_CODE b
WHERE a.postal_code = b.postal
SQL
복사
3.
Create a new table (SG_SPC_STATIONS_GEOM) with the joined data:
Image 6: SG_SPC_STATIONS_GEOM table structure
4.
Add SDO_GEOMETRY attribute to the table:
ALTER TABLE SG_SPC_STATIONS_GEOM ADD (POSTALGEOM SDO_GEOMETRY);
UPDATE SG_SPC_STATIONS_GEOM SET POSTALGEOM =
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(LONGITUDE, LATITUDE, NULL), NULL, NULL);
INSERT INTO user_sdo_geom_metadata VALUES ('sg_spc_stations_geom', 'POSTALGEOM',
SDO_DIM_ARRAY(
SDO_DIM_ELEMENT('Longitude', -180, 180, 0.5),
SDO_DIM_ELEMENT('Latitude', -90, 90, 0.5)
),
8307
);
CREATE INDEX sg_spc_stations_geom_idx ON sg_spc_stations_geom(POSTALGEOM)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
SQL
복사
5.
How to Create and Export Spatial Themes Using Oracle Map Builder
Oracle Map Builder is a powerful tool for creating and managing spatial themes from your Oracle database. In this tutorial, we'll walk through the process of creating a theme and exporting it to GeoJSON format, which can be used in various web mapping applications.
a. Launch Oracle Map Builder
First, ensure you have the following files in your directory:
•
gdal-2.3.0.jar
•
map_builder-12.2.1.3.0.jar
•
mapbuilder
•
mv_admin_cli-12.2.1.3.0
Image 7: Map Builder interface
To launch Map Builder, oen your command prompt and run:
java -Xmx2048M -cp .\map_builder-12.2.1.3.0.jar;.\gdal-2.3.0.jar oracle.mapviewer.builder.MapBuilder
Java
복사
This command allocates 2GB of memory and specifies the necessary JAR files.
b. Navigate the Map Builder Interface
Once launched, you'll see the main Oracle Map Builder interface. We'll be using the Tools menu for our export process.
c. Define the Spatial Source
Select Export to JSON.
•
Click on Tools in the menu bar.
•
2. Select "Export to JSON" from the dropdown.
Image 8: Map Builder theme creation
•
In the "Define Spatial Source" window:
◦
Choose your Data Source (e.g., OBIEE_NAVTEQ_Sample)
◦
Select the Base Table (e.g., SG_SPC_STATIONS_GEOM)
◦
Choose the Spatial Column (e.g., POSTALGEOM)
•
Click "Next" to proceed.
Image 9: Map Builder connection settings
d. Select Attributes
In the "Select Attributes" window:
•
You'll see two columns: "Available Fields" and "Selected Fields"
•
Choose the fields you want to include in your export from the "Available Fields" list
•
Use the arrow buttons to move them to the "Selected Fields" list
•
In our example, we've selected POSTAL_CODE
•
You can also specify a Key Column if needed
•
Click "Next" when you're done selecting attributes
Image 10: Map Builder theme settings
e. Set Export Parameters
The next window allows you to set various export parameters. For most use cases, the default settings should suffice. Click "Next" to continue.
f. Review and Export
1.
Review your settings in the summary window
2.
Click "Finish" to generate and save your GeoJSON file
3.
Choose a location and name for your file when prompted
g. Examine the Output
Once exported, you can open your GeoJSON file in a text editor. The structure should look similar to this:
{
"type": "FeatureCollection",
"collectionName": "dynamicTheme",
"srs": 8307,
"geodetic": true,
"bbox": [103.71503, 1.2718, 103.96033, 1.44247],
"attr_names": ["POSTAL_CODE", "LATITUDE", "LONGITUDE", "BRANCH"],
"attr_types": ["string", "string", "string", "string"],
"features": [
{
"type": "Feature",
"_id": "auto-key-359505603319078937",
"geometry": {
"type": "Point",
"coordinates": [103.79874, 1.27715]
},
"properties": {
"POSTAL_CODE": "118555",
"LATITUDE": "1.2771459159392",
"LONGITUDE": "103.798736572266"
}
},
// ... more features ...
]
}
JSON
복사
Image 11: GeoJSON file download
This GeoJSON file contains:
•
A FeatureCollection object
•
Coordinate system information (srs)
•
Bounding box of the spatial data (bbox)
•
An array of features, each with geometry and properties
You can now use this GeoJSON file in various web mapping libraries like Leaflet or OpenLayers, or in GIS software for further analysis and visualization.
How to Add and Optimize Custom Map Layers in Oracle Analytics Server (OAS)
Oracle Analytics Server (OAS) offers powerful capabilities for visualizing geographical data. In this blog post, we'll walk through the process of adding custom map layers using GeoJSON files and optimizing them for better performance and user experience.
1.
Accessing the OAS Console
To begin, log in to your OAS instance and navigate to the administrative console:
1.
Sign in to your OAS instance.
2.
On the home page, click the "Console" button to access the admin console.
3.
Opening Map Settings
Once in the console:
1.
Look for the "Maps" option in the left-hand menu and click it.
2.
Select the "Map Layers" tab.
3.
Adding a Custom Map Layer
Now, let's add your custom layer:
1.
Click "Add Custom Map Layer" or the "+" button.
2.
Enter a name for your layer (e.g., "SG_Branch").
3.
Add a description (optional but recommended for clarity).
4.
Click "Choose File" and upload your prepared GeoJSON file.
5.
Configuring Layer Properties
After uploading, set the layer properties:
1.
From the "Key Attribute" dropdown, select the JSON property to use as a unique identifier.
2.
In the "Label Attribute" dropdown, choose the property to display as labels on the map.
3.
Set "Minimum Zoom Level" and "Maximum Zoom Level" to specify when the layer should be visible.
4.
Saving the Layer
Once you've configured the settings:
1.
Review all settings and click "Save".
2.
Your new layer should now appear in the map layers list.
3.
Styling and Optimizing the Map
To enhance the visual appeal and performance of your map:
Layer Styling:
•
Select your newly added layer and click "Edit".
•
Go to the "Style" tab.
•
Adjust fill color, border color, and transparency.
•
Apply conditional formatting to change colors based on data values.
Improving Interactivity:
•
Navigate to the "Popups" tab.
•
Select attributes to display in popups.
•
Use custom HTML to enhance popup designs if needed.
Performance Optimization:
•
Go to the "Advanced" tab.
•
Enable "Use Clustering" for efficient display of numerous point data.
•
Adjust the "Simplification Threshold" to balance detail and performance across zoom levels.
Important Considerations and Tips:
1.
File Size Limit: Ensure your GeoJSON file doesn't exceed OAC's 25MB limit.
2.
Data Simplification: Use tools like Mapshaper to simplify GeoJSON files if necessary.
3.
Performance Monitoring: After adding layers, monitor map loading times and interaction performance.
4.
Leverage Caching: Utilize OAS's caching features to improve performance for repetitive queries.
5.
Regular Updates: Keep your geographical data current by updating layers periodically.
By following these steps, you can effectively add and optimize custom map layers in Oracle Analytics Cloud using GeoJSON files. Once your layer is added, you can select it in the Data Visualization canvas to create insightful geographical visualizations.
Remember, the key to successful map visualizations in OAS is balancing detail with performance. Start with a simplified dataset and gradually increase complexity as needed, always keeping an eye on load times and user experience.
Import and Visualize
1.
Import the GeoJSON file into your custom map tile layer in Data Visualization.
Image 12: Data Visualization map visualization result screen
2.
Create a visualization using your test data set containing postal code and branch information:
Image 13: Final map visualization
Conclusion
Creating a custom map layer for specific geographic divisions like postal codes can greatly enhance your data visualizations. While it requires some effort, the process outlined here provides a robust solution for mapping data to custom geographic boundaries.
Remember to respect data usage rights and consider purchasing official data for production use. With this custom layer, you can now create powerful, location-based visualizations in Oracle Data Visualization or similar tools.
Additional Resources
By following this guide, you should now have a custom map layer for visualizing data by postal codes in Singapore. Happy mapping!