Create vector tiles with a simple SQL call
How to create vector tiles from spatial data managed in the Oracle Database?
Explore new spatial features available in 23ai
Vector tiles are the preferred method for modern map data delivery with complete flexibility in design.
As of Oracle Database version 23ai, developers can easily create vector tiles directly from spatial data managed in the Oracle Database. A simple SQL call efficiently streams large amounts of spatial data to web or GIS (Geographic Information System) clients.
In summary, vector tiles enable
- dynamic styling,
- fast performance,
- smooth map interactions,
- and dynamic map queries.
The following chapters describe in detail, how you create and visualize vector tiles via a REST
endpoint. For completeness, I start with necessary information about
- the environment,
- the data set, and
- how to load and prepare the data set.
About the environment
I used an Autonomous Database 23ai provisioned in the Oracle Cloud for this post. Suppose you don´t have an Oracle Cloud account and an Autonomous Database (ADB) instance ready to use. In that case, you can follow this Oracle LiveLabs workshop to set up the environment via the LiveLabs Sandbox (“Green button”) option.
For simplicity, I also use the default ADB user ADMIN
.
Note: Generally, I recommend creating a new user that owns the spatial data. If you choose to create a new one, make sure to REST-enable the database schema.
About the data
If you don´t have a sample spatial data set available, you can download this USGS Earthquakes data set (earthquakes.csv
), which includes longitude and latitude values representing a geographic location.
Load the data set into your Autonomous Database instance
Step 1: Load the data using Database Actions
> Load Data
into a new table. Name the table USGS_EARTHQUAKES
.
Step 2: Verify the uploaded data. Open a SQL Worksheet
in Database Actions
and run the following SQL statement:
SELECT * FROM usgs_earthquakes FETCH FIRST 10 ROWS ONLY;
Convert longitude and latitude values into point geometries
Next, perform the following simple steps within the already opened SQL Worksheet
to prepare the data.
Step 3: Add a new column of type SDO_GEOMETRY
to your table.
ALTER TABLE usgs_earthquakes ADD (geometry SDO_GEOMETRY);
Step 4: Convert longitude/latitude into point geometries.
UPDATE usgs_earthquakes
SET geometry = SDO_GEOMETRY(location_longitude, location_latitude);
COMMIT;
Step 5: Verify the updated data.
SELECT
g.location_longitude,
g.location_latitude,
g.geometry.sdo_point.x AS lon,
g.geometry.sdo_point.y AS lat,
g.geometry.sdo_srid AS srid
FROM usgs_earthquakes g;
Step 6: Create a spatial index.
CREATE INDEX usgs_earthquakes_sidx
ON usgs_earthquakes (geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2 PARAMETERS ('LAYER_GTYPE=POINT');
Create vector tiles using SQL
The PL/SQL Package SDO_UTIL
contains a SQL
function called GET_VECTORTILE.
We use it to generate vector tiles from our point data set.
Step 7: Here is how to do it:
SELECT SDO_UTIL.GET_VECTORTILE(
TABLE_NAME => 'USGS_EARTHQUAKES',
GEOM_COL_NAME => 'GEOMETRY',
ATT_COL_NAMES => SDO_STRING_ARRAY('LOCATION_NAME','IMPACT_MAGNITUDE','LOCATION_LATITUDE','LOCATION_LONGITUDE'),
TILE_X => :x,
TILE_Y_PBF => :y,
TILE_ZOOM => :z) AS vtile
FROM DUAL;
Replace the binding variables with the following values while executing the function:
x = 2
y = 2.pbf
z = 2
The result is a BLOB
returned in column VTILE
.
Note: If you want more details about the function, here is the link to the documentation.
Well done! You could stop here. But a BLOB
containing the vector tiles is very likely different from what you seek. As for myself, I want an interactive map that displays the vector tiles as a map layer. Two visualization options are QGIS or a single-page web application using the MapLibre GL JS.
Let´s see for both options how to consume the vector tiles?
Set up a REST endpoint for the vector tiles
Vector tiles can be consumed sending a GET
request to a REST
endpoint, so we need to set up one first. The following three SQL
statements create the REST
interface with aGET
handler. You can execute them in your SQL Worksheet
.
Step 8: Create a REST
module. Name it EARTHQUAKES
.
BEGIN
ORDS.DEFINE_MODULE(
P_MODULE_NAME => 'earthquakes',
P_BASE_PATH => '/usgs/',
P_ITEMS_PER_PAGE => 25,
P_STATUS => 'PUBLISHED',
P_COMMENTS => ''
);
COMMIT;
END;
/
Step 9: Create a module template.
BEGIN
ORDS.DEFINE_TEMPLATE(
P_MODULE_NAME => 'earthquakes',
P_PATTERN => 'vt/:z/:x/:y',
P_PRIORITY => 0,
P_ETAG_TYPE => 'HASH',
P_COMMENTS => ''
);
COMMIT;
END;
/
Step 10: Create the GET
handler. It includes our SELECT
call using the SDO_UTIL.GET_VECTORTILE
function as the source. Add a column named MEDIATYPE
to the SQL
statement to specify how the application should interprete the resulting BLOB
.
BEGIN
ORDS.DEFINE_HANDLER(
P_MODULE_NAME => 'earthquakes',
P_PATTERN => 'vt/:z/:x/:y',
P_METHOD => 'GET',
P_SOURCE_TYPE => ords.source_type_media,
P_SOURCE => 'SELECT
''application/vnd.mapbox-vector-tile'' as mediatype,
SDO_UTIL.GET_VECTORTILE(
TABLE_NAME => ''USGS_EARTHQUAKES'',
GEOM_COL_NAME => ''GEOMETRY'',
ATT_COL_NAMES => sdo_string_array(''LOCATION_NAME'',''LOCATION_FULL'',''IMPACT_MAGNITUDE'',''LOCATION_LATITUDE'',''LOCATION_LONGITUDE''),
TILE_X => :x,
TILE_Y_PBF => :y,
TILE_ZOOM => :z) AS vtile
FROM dual',
P_ITEMS_PER_PAGE => 25,
P_COMMENTS => ''
);
COMMIT;
END;
/
Congratulations. You have successfully set up a new REST
endpoint.
Step 11: Fetch the URL
of the REST
endpoint. Go to Database Actions
> Developer
> REST
> Modules
. Click on module earthquakes
. Then click on template vt/:z/:x/:x
. And finally, click on the GET
handler.
You can now copy the URL
for the REST
endpoint. It has the following pattern:
https://<adb_ocid>-<adb_name>.adb.<region>.oraclecloudapps.com/ords/<username>/usgs/vt/:z/:x/:y
Step 12: Replace the string /:z/:x/:y
with /{z}/{x}/{y}.pbf
since the application will replace them with proper values on its own. Save the URL
.
Visualize the vector tiles in QGIS using the REST endpoint
Step 13: Create a new connection for vector tiles in QGIS using the URL
.
Step 14: Create a map. Drag and drop the new connection onto a base map of your choice. I used OpenStreetMap vector tiles as the base map.
Visualize the vector tiles with a single-page web app using the REST endpoint
For this part I use Visual Studio Code and the Live Server extension to edit and run a web app. Further down you find the HTML code of that web app which uses the MapLibre GL JS library.
Step 15: Search for the text <<Insert your REST endpoint URL ending with /{z}/{x}/{y}.pbf here>>
and replace it with the REST
endpoint URL
. Then start Live Server and run the app.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>USGS Earthquakes Vector Tiles 23ai Demo</title>
<meta name="viewport" content="initial-scale=1,maximum-scale=1,user-scalable=no" />
<script src="https://unpkg.com/maplibre-gl@2.4.0/dist/maplibre-gl.js"></script>
<link href="https://unpkg.com/maplibre-gl@2.4.0/dist/maplibre-gl.css" rel="stylesheet" />
<style>
body { margin: 0; padding: 0; }
#map { position: absolute; top: 0; bottom: 0; width: 100%; }
</style>
</head>
<body>
<div id="map"></div>
<script>
const map = new maplibregl.Map({
container: 'map',
style: 'https://maps.oracle.com/mapviewer/pvt/res/style/osm-positron/style.json',
center: [0.0, 0.0],
zoom: 1,
transformRequest: (url, resourceType) => {
if (resourceType === 'Tile' && (
url.startsWith('https://maps.oracle.com/mapviewer/pvt') ||
url.startsWith('https://elocation.oracle.com/mapviewer/pvt'))
){
return {
url: url,
headers: {'x-oracle-pvtile': 'OracleSpatial'},
credentials: 'include'
};
}
}
});
map.on('load', function () {
// USGS Earthquakes
map.addSource('earthquakes', {
type: 'vector',
tiles: [
"<<Insert your REST endpoint URL ending with /{z}/{x}/{y}.pbf here>>"
]
});
map.addLayer({
'id': 'earthquakes-layer',
'type': 'circle',
'source': 'earthquakes',
'source-layer': 'LAYER',
'layout': {},
'paint': {
'circle-radius': 3,
'circle-color': '#8B3626',
'circle-stroke-color': 'white',
'circle-stroke-width': 1,
'circle-opacity': 0.5}
});
});
map.on('click', 'earthquakes-layer', function(event) {
console.log(event.features);
const popup = new maplibregl.Popup();
const longitude = event.lngLat.lng;
const latitude = event.lngLat.lat;
popup.setLngLat({lng: (longitude), lat: latitude}).setHTML('<span>Magnitude: ' + event.features[0].properties.IMPACT_MAGNITUDE + '<br\>Location: ' + event.features[1].properties.LOCATION_NAME + '</span>').addTo(map);
});
// Add zoom and rotation controls to the map
map.addControl(new maplibregl.NavigationControl());
// Add full screen map
map.addControl(new maplibregl.FullscreenControl());
</script>
</body>
</html>
The result is what you see in the image at the top of this post.
Note: In the code I use a reference to the style.json
of the OSM POSITRON (Vector Tiles) available here. In the described environment you can use all styles and maps listed there for free.
Well done! You have seen and hopefully tested everything I wanted to show in this post. It´s time now for a break. Enjoy a good coffee or tea. If you feel in the mood, let me know if the content is useful for you (or not).
Do you want to learn more?
Let me add a few more links that reference technologies I mentioned or used.