{"id":107053,"date":"2025-07-08T14:44:28","date_gmt":"2025-07-08T14:44:28","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=107053"},"modified":"2025-06-16T10:14:26","modified_gmt":"2025-06-16T10:14:26","slug":"creating-uber-fast-maps-with-23ai-vector-tiles-and-h3-indexes-part-1","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/creating-uber-fast-maps-with-23ai-vector-tiles-and-h3-indexes-part-1\/","title":{"rendered":"Creating Uber-Fast Maps With 23ai Vector Tiles and H3 Indexes: Part 1"},"content":{"rendered":"\n

Oracle Database 23ai<\/a> added 300+ new features like the new VECTOR datatype that get most of the attention, but often overlooked are two new features that dramatically expand support for complex geospatial problem-solving. In this article we\u2019ll take a closer look at <\/em>vector tiling<\/em><\/strong> \u2013 a toolset that already has everything your developers need to create ultra-fast maps in standard geospatial applications. <\/em><\/p>\n\n\n\n

OK, I have to admit it: I\u2019m a map junkie. I\u2019ve built all kinds of mapping applications for the past several years using either Oracle APEX\u2019s Native Map Region (NMR) toolset when I wanted a low-code solution with excellent control over features like legends and layers, and Oracle Spatial Studio when I didn\u2019t have time to write complex SQL to quickly review how spatial data was distributed.<\/p>\n\n\n\n

One pain point I\u2019ve run into involves response time, especially when I\u2019m displaying tens or even hundreds of thousands of individual points on a map on different layers. Even my laptop with a reasonably robust GPU and memory cannot cope with those demands. For example, the map in Figure 1<\/strong> was built in APEX 24.2 using NMR to display over 157,000<\/strong> separate longitude\/latitude points on three different map layers, and it typically takes between 45 – 180 seconds to load completely. My pain is not new or just my pain; loading map content quickly when there are huge volumes of individual points to display has been a challenge for GIS applications since they were first born in the mid-1960s.<\/p>\n\n\n\n

\"A<\/figure>\n\n\n\n

<\/p>\n\n\n\n

Figure 1. Displaying +157K Distinct Geographic Points in Oracle APEX 24.2<\/em><\/strong><\/p>\n\n\n\n

But there\u2019s good news for Oracle developers working with geospatial applications today: There\u2019s an intriguing solution in Oracle Database 23ai that significantly reduces map response time. Vector tiles<\/em> encode all mapping data – both point \/ polygonal information and corresponding metadata – into a unique compressed binary format so specialized GIS applications like QGIS<\/a> or MapLink<\/a> can interpret and display this format at blinding speed.<\/p>\n\n\n\n

Since the size of the data for vector tiles is dramatically smaller than previous formats, network response time is also reduced, so ultimately response time for any map activity \u2013 scrolling, zooming, or selecting individual points and displaying their metadata \u2013 often improves by several orders of magnitude.<\/p>\n\n\n\n

So, What Are Vector Tiles?<\/h2>\n\n\n\n

The easiest way to envision vector tiles is to imagine a Mercator map projection of the surface of the Earth. The resulting projection of that geometry onto a flat surface results in a series of evenly-sized squares that can be grouped into just four at the lowest zoom level (Figure 2<\/strong>).<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Figure 2. Vector Tiles: Grouping Ever-Lower Levels of Mercator Projection Squares<\/em><\/strong><\/p>\n\n\n\n

Here\u2019s the real brilliance of vector tiles: At the next<\/em> lowest zoom level, each one of those four squares also contains another<\/em> set of four squares, which at the next zoom level contains yet another<\/em> four squares, and so on until we reach a zoom level of 22, or 223<\/sup> squares. At this lowest level, each vector tile is a square that\u2019s just under one square meter in area.<\/p>\n\n\n\n

Vector tiles work really well for mapping applications because at a high zoom level, it\u2019s easy to see where points are concentrated<\/em> on a map, and as you drill down into the lowest levels of the map, the points are already present so there\u2019s no need to make another round trip to the map server to retrieve them and any related metadata.<\/p>\n\n\n\n

Vector Tiles: Preparing For Implementation<\/h2>\n\n\n\n

Let\u2019s illustrate how easy it is to leverage 23ai\u2019s vector tile capabilities with a straightforward use case: A public utility is researching where it should place long-term battery storage sites close enough to existing wind turbines, solar panel farms, and other renewable energy resources it\u2019s planning to develop. Since EVs aren\u2019t going to disappear and are likely to increase among its customer base, the utility also needs to identify all existing EV charging stations so they can provide them with energy captured at the battery storage sites.<\/p>\n\n\n\n

To build out this use case, I\u2019ve created and populated three different tables containing geolocations for EV charging stations, wind turbines, <\/em>and photovoltaic (PV) solar panel farms<\/em> throughout the United States. These data were downloaded and curated from the US National Renewal Energy Laboratory (NREL) website<\/a>, and contains site-specific information including each wind turbine\u2019s latitude and longitude.<\/p>\n\n\n\n

Note<\/strong>: in the Appendix<\/a> at the end of this article, you can find details about how to get the structures and data to test this out yourself.<\/p>\n\n\n\n

To keep this example simpler for now, I\u2019ll focus on the EXISTING_WIND_TURBINES<\/code> table. The DDL to create that table is shown in Figure<\/strong> 3<\/strong>.<\/p>\n\n\n\n

DROP TABLE IF EXISTS existing_wind_turbines PURGE;\nCREATE TABLE IF NOT EXISTS existing_wind_turbines (\n  usgs_case_id NUMBER(10)\n, faa_digital_obstacle_id VARCHAR2(10)\n, faa_aeronautical_study_nbr VARCHAR2(20)\n, usgs_prior_id NUMBER(10)\n, eia_860_id NUMBER(10)\n, state_abbr VARCHAR2(02)\n, county VARCHAR2(60)\n, fips VARCHAR2(09)\n, project_name VARCHAR2(60)\n, project_year NUMBER(04)\n, project_turbines NUMBER(05)\n, project_capacity NUMBER(15,03)\n, manufacturer VARCHAR2(80)\n, model VARCHAR2(40)\n, turbine_capacity NUMBER(15,03)\n, turbine_hub_height NUMBER(15,03)\n, turbine_rotor_diameter NUMBER(15,03)\n, turbine_rotor_swept_area NUMBER(15,03)\n, turbine_total_height NUMBER(15,03)\n, is_turbine_retrofitted NUMBER(01)\n, retrofit_yr NUMBER(04)\n, is_turbine_offshore NUMBER(01)\n, turbine_attribute_confidence NUMBER(01)\n, turbine_location_confidence NUMBER(01)\n, imaged_on DATE\n, image_source VARCHAR2(20)\n, longitude NUMBER(15,08)\n, latitude NUMBER(15,08)\n, geometry SDO_GEOMETRY\n);\n\nALTER TABLE existing_wind_turbines\n  ADD CONSTRAINT existing_wind_turbines_pk\n  PRIMARY KEY (usgs_case_id)\n  USING INDEX (\n    CREATE UNIQUE INDEX existing_wind_turbines_pk_idx\n        ON existing_wind_turbines (usgs_case_id)\n    );<\/pre><\/div>\n\n\n\n
Figure 3. EXISTING_WIND_TURBINES<\/code> Table Creation<\/em><\/strong><\/p>\n\n\n\n
After uploading the data via the SQL Web Developer data loading facility, I updated the contents of the GEOMETRY <\/code>column with the longitude and latitude of every wind turbine site. Finally, I created a spatial index<\/em> on each table\u2019s GEOMETRY <\/code>column (Figure 4<\/strong>), thus enabling the geospatial features of the SDO_GEOMETRY<\/code> datatype so that mapping technology could interact with that column\u2019s data.<\/p>\n\n\n\n
UPDATE existing_wind_turbines \n   SET geometry = SDO_GEOMETRY(longitude, latitude);\n\nCOMMIT;\n\nDROP INDEX IF EXISTS existing_wind_turbines_spidx;\nCREATE INDEX IF NOT EXISTS existing_wind_turbines_spidx\n  ON existing_wind_turbines (geometry)\n  INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2\n  PARAMETERS ('LAYER_GTYPE=POINT');<\/pre><\/div>\n\n\n\n
Figure 4. Updating EXISTING_WIND_TURBINES.GEOMETRY<\/code> and Creating Its Spatial Index<\/em><\/strong><\/p>\n\n\n\n
Building Vector Tile Output From an SDO_GEOMETRY Column<\/h2>\n\n\n\n
Returning geospatial data from the EXISTING_WIND_TURBINES<\/code> table as a series of vector tiles in binary format is remarkably simple to accomplish via the new 23ai SDO_UTIL.GET_VECTORTILE<\/code> function. Note that in this case, I specified the exact zoom level and X and Y coordinates of the tiles I\u2019m interested in retrieving (Figure 5<\/strong>), but we\u2019ll look at parameterizing this function call shortly.<\/p>\n\n\n\n
SELECT SDO_UTIL.GET_VECTORTILE(\n   TABLE_NAME=>'EXISTING_WIND_TURBINES',\n   GEOM_COL_NAME=>'GEOMETRY',\n   ATT_COL_NAMES=>\n     SDO_STRING_ARRAY('USGS_CASE_ID','COUNTY','STATE_ABBR'\n                     ,'TURBINE_TOTAL_HEIGHT','TURBINE_CAPACITY'\n                     ,'LONGITUDE','LATITUDE'),\n   TILE_X=>129, TILE_Y=>187, TILE_ZOOM=>9, MAX_FEATURES=>255)\n   AS gobbledygook;<\/pre><\/div>\n\n\n\n
Figure 5. Generating Limited Vector Tile Output from EXISTING_WIND_TURBINES<\/code><\/em><\/strong><\/p>\n\n\n\n
So what gets returned from the query in Figure 5<\/strong>? Here\u2019s a snippet of the resulting output (Figure 6<\/strong>); you\u2019ll note it appears as gibberish on first glance. However, a spatial application that consumes this output sees this binary output as tightly-compressed spatial data<\/em> comprising the vector tiles themselves as well as any specific metadata about each point. The spatial application can then display the vector tiles within a separate mapping layer at extreme speed without the need to make a return trip to the sending server.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n

  <\/p>\n\n\n\n
Figure 6. GET_VECTORTILE <\/code>Function: Sample Output<\/em><\/strong><\/p>\n\n\n\n
Generating Vector Tiles Via ORDS REST API<\/h2>\n\n\n\n
The trick to transforming these vector tiles into something that mapping software can use to display them is leveraging Oracle\u2019s ORDS REST<\/code> API toolset. Figure 7<\/strong> shows how I built an ORDS REST API<\/code> module<\/em> named wind_turbines<\/code>, defined a corresponding template<\/em> that accepts variable values, and finally defined a handler<\/em> that returns just the required appropriate vector tiles as a BLOB<\/code> based on the parameter values specified.<\/p>\n\n\n\n
BEGIN\n   ORDS.DELETE_MODULE(wind_turbines');\nEND;\n\/\n\nBEGIN\n  ORDS.DEFINE_MODULE(\n    P_MODULE_NAME => 'wind_turbines'\n  , P_BASE_PATH => '\/existing_turbines\/'\n  , P_ITEMS_PER_PAGE => 50\n  , P_STATUS => 'PUBLISHED'\n  );\n  COMMIT;\nEND;\n\/\n\nBEGIN\n  ORDS.DEFINE_TEMPLATE(\n    P_MODULE_NAME => 'wind_turbines'\n  , P_PATTERN => 'vt\/:z\/:x\/:y'\n  , P_PRIORITY => 0\n  , P_ETAG_TYPE => 'HASH'\n  );\n  COMMIT;\nEND;\n\/\n\nBEGIN\n  ORDS.DEFINE_HANDLER(\n    P_MODULE_NAME => 'wind_turbines'\n  , P_PATTERN => 'vt\/:z\/:x\/:y'\n  , P_METHOD => 'GET'\n  , P_SOURCE_TYPE => ORDS.SOURCE_TYPE_MEDIA\n  , P_SOURCE => 'SELECT ''application\/vnd.mapbox-vector-tile'' AS mediatype\n       ,SDO_UTIL.GET_VECTORTILE(\n          TABLE_NAME => ''EXISTING_WIND_TURBINES''\n         ,GEOM_COL_NAME => ''GEOMETRY''\n         ,ATT_COL_NAMES => SDO_STRING_ARRAY(''USGS_CASE_ID'',''FIPS'',''COUNTY''\n            ,''STATE_ABBR'',''TURBINE_TOTAL_HEIGHT'',''TURBINE_CAPACITY''\n            ,''LATITUDE'',''LONGITUDE'')\n         ,TILE_X => :x\n         ,TILE_Y_PBF => :y\n         ,TILE_ZOOM => :z) AS vtile\n      FROM DUAL'\n  , P_ITEMS_PER_PAGE => 50\n  );\n  COMMIT;\nEND;\n\/<\/pre><\/div>\n\n\n\n
Figure 7. ORDS REST API<\/code> Module, Template, and Handler for EXISTING_WIND_TURBINES<\/code> Vector Tiles<\/em><\/strong><\/p>\n\n\n\n
The ORDS<\/code> module and template code is pretty simple, but the ORDS<\/code> handler code is a bit trickier to understand, so let\u2019s take a closer look:<\/p>\n\n\n
\n