On the Fourth Day of 23ai, Viscosity Gave To Me…

Thwart Toil Through Tiles: Leveraging Oracle 23ai’s Latest Geospatial Features

Sure, Oracle Database 23ai has lots of great technical features for traditional RDBMS operations … but did you know just how amazing its support is for things like geospatial applications? Save your DevOps teams from fruitless explorations into third-party databases, steep learning curves for obscure toolsets, and considerable technical debt by embracing what’s already included within the 23ai Converged Database.

DevOps teams have been building complex geospatial and mapping applications with Oracle Database for quite some time. I’ve used Oracle Spatial Studio and APEX’s Native Map Region to visualize points on a map, find the closest points of interest, and even analyze vehicles traversing specific map areas over time (see Figure 1). But the time it takes to display, drill into, and move around within a map when there are thousands of map points present really depends on the computing resources I can bring to bear, and even a laptop with plenty of memory and a powerful GPU may not be able to handle those demands.

GIS applications have actually struggled with these issues since the early 1970s. One intriguing solution involves encoding the mapping data itself into a special compressed binary format called vector tiles that specialized GIS applications like QGIS or MapLink can interpret. Since a lot less data needs to be shipped over the network to the system displaying it, this improves map response time by several orders of magnitude.

_{Figure 1. A Sample Oracle Spatial Studio Map Implementation}

Preparing for Vector Tiles: Building the Spatial Infrastructure

Starting in Oracle 23ai, it’s possible to create vector tiles directly from existing spatial data stored within an SDO_GEOMETRY column with calls to new functions and procedures built into the SDO_UTIL package. I’ll illustrate this with a straightforward use case: A public utility needs to identify and display several thousand points on a map representing optimal locations to place either wind turbines or solar panels for alternative energy generation – ideally, close enough to existing electrical vehicle charging stations so their power output can be utilized directly and immediately.

To tackle this, I’ve built and populated a table named EV_CHARGING_STATIONS with about 80,000 distinct points throughout the United States (Figure 2). Each row contains specific information about each site, including its address, the number of Level 1, 2, or 3 chargers available, and its latitude and longitude.

DROP TABLE IF EXISTS ev_charging_stations PURGE;
CREATE TABLE IF NOT EXISTS ev_charging_stations (

station_id NUMBER(9)

, fuel_type VARCHAR2(12)

, station_name VARCHAR2(128)

, address VARCHAR2(90)

, city VARCHAR2(60)

, state_abbr VARCHAR(02)

, zip_code VARCHAR2(05)

, ev_level_1 NUMBER(6)

, ev_level_2 NUMBER(6)

, ev_level_3 NUMBER(6)

, ev_network VARCHAR2(30)

, connector_types VARCHAR2(128)

, facility_type VARCHAR2(30)

, longitude NUMBER(15,8)

, latitude NUMBER(15,8)

, geometry SDO_GEOMETRY

);

_{Figure 2. EV_CHARGING_STATIONS Table Creation}

After loading data I downloaded and curated from the US National Renewal Energy Laboratory (NREL) website, I updated the contents of the geometry column with the latitude and longitude of every EV charging site and then created a spatial index on that column as well (Figure 3). This enables all the special features of the SDO_GEOMETRY datatype and the related Oracle Spatial features for interactions within mapping technology.

UPDATE ev_charging_stations

   SET geometry = SDO_GEOMETRY(longitude, latitude);

COMMIT;

CREATE INDEX ev_charging_stations_spidx

ON ev_charging_stations (geometry)

INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2

PARAMETERS ('LAYER_GTYPE=POINT');

_{Figure 3. Updating Spatial Column and Creating a Spatial Index}

Constructing Vector Tiles From SDO_GEOMETRY Columns

Here’s an example of using the new 23ai SDO_UTIL.GET_VECTORTILE function to capture data from the EV_CHARGING_STATIONS table and return it as a series of vector tiles in binary format. Note that in this case, I specified the exact zoom level and X and Y coordinates of the tiles I’m interested in retrieving – more on that in just a bit (Figure 4).

SELECT SDO_UTIL.GET_VECTORTILE(

   TABLE_NAME=>'EV_CHARGING_STATIONS',

   GEOM_COL_NAME=>'GEOMETRY',

   ATT_COL_NAMES=>sdo_string_array('CITY','STATE_ABBR','LONGITUDE','LATITUDE'),

   TILE_X=>129,

   TILE_Y=>187,

   TILE_ZOOM=>9,

   MAX_FEATURES=>255)

   AS gobbledygook;

_{Figure 4. Selecting Vector Tiles from EV_CHARGING_STATIONS}

And here’s just a small portion of the resulting output from the query above: a binary result set of what appears to be gobbledygook to the naked human eye (Figure 5).

_{Figure 5. Example Output from GET_VECTORTILE function}

To a consuming spatial application, however, this binary output consists of tightly compressed spatial data—the vector tiles themselves, plus specific attributes about the particular geometries stored within—that can be displayed with extreme speed and without any future return trips to the sending server.

Making Vector Tiles Useful With ORDS REST API

Vector tiles are basically useless if I can’t retrieve the ones I need and ship them over to the mapping software and application to display them. Fortunately, we have another tool on our 23ai converged database toolbelt: its venerable ORDS REST API capabilities.

Figure 5 below shows how I built an ORDS REST API module named ev_chargers, defined a corresponding template for supplying variable values to the API, and then built a handler that returns a small sample of mapping vector tiles as a BLOB when the handler is called.

BEGIN



ORDS.DEFINE_MODULE(

   P_MODULE_NAME => 'ev_chargers'

, P_BASE_PATH => '/myords/'

, P_ITEMS_PER_PAGE => 50

, P_STATUS => 'PUBLISHED'

, P_COMMENTS => ''

);

COMMIT;

ORDS.DEFINE_TEMPLATE(
 P_MODULE_NAME => 'ev_chargers'

, P_PATTERN => 'vt/:z/:x/:y'

, P_PRIORITY => 0

, P_ETAG_TYPE => 'HASH'

, P_COMMENTS => ''

);

COMMIT;

ORDS.DEFINE_HANDLER(
   P_MODULE_NAME => 'ev_chargers'

, 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 => ''EV_CHARGING_STATIONS''

         ,GEOM_COL_NAME => ''GEOMETRY''

         ,ATT_COL_NAMES =>

        SDO_STRING_ARRAY(

     ''ADDRESS''

     ,''CITY''

     ,''STATE_ABBR''

     ,''EV_LEVEL_1''

     ,''EV_LEVEL_2''

     ,''EV_LEVEL_3''

     ,''LATITUDE''

     ,''LONGITUDE'')

         ,TILE_X => :x

         ,TILE_Y_PBF => :y

         ,TILE_ZOOM => :z) AS vtile

     FROM DUAL'

, P_ITEMS_PER_PAGE => 50

, P_COMMENTS => '');

COMMIT;

END;
/

_{Figure 6. ORDS REST API Module, Template, and Handler Construction}

The code for the ORDS module and template is relatively straightforward, but let’s break down the ORDS handler code a bit more:

• I’ve defined P_SOURCE_TYPE as SOURCE_TYPE_MEDIA to indicate the result set returned will be in binary format that will also include an accompanying HTTP Content-Type header.
• For P_SOURCE, I’ve supplied a GET_VECTORTILE query that captures latitude, longitude, and other pertinent attributes for each EV charger. Note the specification of a column labeled mediatype as a value of application/vnd.mapbox-vector-tile to correspond to the expected output of the vector tile result set binary result set.

The query also expects three bind variable values to be supplied during execution:

• TILE_ZOOM determines the actual number of tiles needed to divide up a map into smaller and smaller pieces. As the value of TILE_ZOOM increases, so does the number of tiles … and thus, finer and finer detail can be retrieved and displayed. If I specify TILE_ZOOM as four (4), the map would be divvied up into 2⁴ x 2⁴ tiles, or 256 (16 x 16) tiles; if it’s set to eight (8), the map would be cut up into 65,536 individual tiles.
• TILE_X defines the X coordinate within the grid of tiles to be fetched.
• Finally, TILE_Y_PBF defines the Y coordinate within the grid of tiles we want to fetch. This version of the Y coordinate is in VARCHAR2 format because it actually refers to the PBF file extension that will eventually be tacked onto it within the HTML used to display the retrieved tiles.

Displaying Vector Tiles: A Quick Checkpoint

Typically, the specialized application that receives vector tiles as input would be deployed on its own web server, usually a variant of QGIS. To demonstrate just how simple it is to consume vector tiles, however, I’ll leverage Microsoft Visual Studio’s Live Server browser extension to display them within an HTML frame using the MapLibre GL JS library.

The first half of the HTML sample code (Figure 7) is primarily responsible for setting up the map server parameters for display of the map itself, including the base map style. In this example, I’ve used the Open Street Map (OSM) “Positron” style, but there are several other OSM styles available, including a “dark” theme and a brighter display of street details and highways. Also, I’ve specified a closer initial zoom level for the map and focused the initial center of the map on the United States.

_{Figure 7. HTML Code for Live Server Demonstration – Part 1}

The second half of the code is where the real magic happens (Figure 8):

• The code in the red box accesses the ORDS endpoint I created earlier. It interacts with the map display to retrieve the corresponding vector tile data, including their zoom level and the corresponding X and Y coordinates within the grid of tiles that are returned.
• The code in the blue box specifies how the individual grid points for each EV charger should be displayed. In this example, I’m displaying each charger as a small red circle with a white outline. Figure 9 shows a wide-area display of nearly 80,000 chargers in the United States.
• Finally, the code in the gold box enables me to click on any of the map points to show specific attributes of the charging station. Figure 10 shows the result of that: the address and number of Level 1, 2, and 3 chargers at a particular station near the state capitol building in Madison, Wisconsin.

_{Figure 8. HTML Code for Live Server Demonstration – Part 2}

_{Figure 9. Tiled Map Example 1: Wide-Area Map Display of US Charging Stations}

_{Figure 10. Tiled Map Example 2: Charging Station Attributes Displayed On Mouse Click}

It’s hard to describe here just how fast these images displayed, even on my underpowered laptop without any significantly powerful GPUs. I was able to traverse, zoom in, zoom out, and navigate within the map frame with significantly less lag time than I’d typically experience when displaying these maps within an APEX Native Map Region.

Conclusion: Look Before You Leap (To Yet Another Tool)

Vector tiles are the perfect example of what the Oracle 23ai converged database brings to bear to solve a vexing and complex problem. Though I’m always on the lookout for the next shiny new doohickey to add to my technical quiver, as a seasoned IT professional, I also recognize that a neat new tool might introduce a hard-to-maintain application and considerable technical debt to my organization once I move on to another project. And that’s when the seasoned DBA inside my head reminds me that it’s always a good idea to check out the latest features of the tools already on my toolbelt.

Happy Holidays! 🎄