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

Karin Patenge
6 min readOct 15, 2024
USGS earthquake locations as vector tiles displayed on a base map
Single-page web app displaying vector tiles

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.

Database Action > Load data
Load data into an ADB using Database Actions

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 RESTmodule. 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.

QGIS: Create Vector Tiles Connection

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.

QGIS visualization of vector tiles
QGIS: Map with OpenStreetMap and USGS earthquakes vector tiles

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).

--

--

Karin Patenge
Karin Patenge

Written by Karin Patenge

I am a Product Manager with the Oracle Database team covering Spatial and Graph technologies. The views expressed here are my own.

No responses yet