{"id":107824,"date":"2025-11-17T12:00:00","date_gmt":"2025-11-17T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=107824"},"modified":"2025-12-11T09:58:38","modified_gmt":"2025-12-11T09:58:38","slug":"creating-uber-fast-maps-with-23ai-vector-tiles-and-h3-indexes-part-2","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/creating-uber-fast-maps-with-23ai-vector-tiles-and-h3-indexes-part-2\/","title":{"rendered":"How to Create Uber-Fast Maps With Oracle 23ai Vector Tiles and H3 Indexes (Part 2)"},"content":{"rendered":"\n<p><em>Since this article was written, <a href=\"https:\/\/www.oracle.com\/database\/free\/\" target=\"_blank\" rel=\"noreferrer noopener\">Oracle AI Database 26ai<\/a> has been released.<\/em><\/p>\n\n\n\n<p>Oracle Database 23ai added 300+ new features like the new <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/26\/vecse\/create-tables-using-vector-data-type.html\" target=\"_blank\" rel=\"noreferrer noopener\">VECTOR datatype<\/a> that get most of the attention, but often overlooked are two additions that dramatically expand support for complex <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/introduction-sql-server-spatial-data\/\" target=\"_blank\" rel=\"noreferrer noopener\">geospatial<\/a> problem-solving.<\/p>\n\n\n\n<p>In the <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/creating-uber-fast-maps-with-23ai-vector-tiles-and-h3-indexes-part-1\/\" target=\"_blank\" rel=\"noreferrer noopener\">prior article<\/a> in this series, I demonstrated how Oracle 23ai\u2019s vector tiles are a solution for improving the speed at which large volumes of map points are displayed within modern mapping applications.<\/p>\n\n\n\n<p>However, rapid display and navigation of point clouds is just one challenge these applications need to solve. There\u2019s also the need to <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/eager-aggregation-in-sql-queries\/\" target=\"_blank\" rel=\"noreferrer noopener\">aggregate <\/a>information from those individual points and display those aggregations meaningfully.<\/p>\n\n\n\n<p>That&#8217;s where the <a href=\"https:\/\/h3geo.org\/docs\/highlights\/indexing\/\" target=\"_blank\" rel=\"noreferrer noopener\">Hierarchical Hexagonal Indexing (H3)<\/a> features in Oracle 23ai come in.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-what-is-hierarchical-hexagonal-indexing-h3\">What is Hierarchical Hexagonal Indexing (H3)?<\/h2>\n\n\n\n<p>Aggregation is at the heart of Hierarchical Hexagonal Indexing (H3) which is a relatively new development and, unsurprisingly, came about from a real-world business use case.<\/p>\n\n\n\n<p><a href=\"https:\/\/www.uber.com\/gb\/en\/\" target=\"_blank\" rel=\"noreferrer noopener\">Uber<\/a> discovered that their drivers may not always know local geography well enough to locate their clients, pick them up, and deliver them to their desired destination &#8211; but there\u2019s also the issue of assigning a driver to a particular ride request based on which drivers are closest to the rider when the request is made.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1005\" height=\"495\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-1.png\" alt=\"A graph showing a summary of H3 Spatial Indexing.\" class=\"wp-image-107825\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-1.png 1005w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-1-300x148.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-1-768x378.png 768w\" sizes=\"auto, (max-width: 1005px) 100vw, 1005px\" \/><figcaption class=\"wp-element-caption\"><em>Figure 1: H3 Indexing Concepts<\/em><\/figcaption><\/figure>\n\n\n\n<p>As Figure 1 shows, <a href=\"https:\/\/www.uber.com\/blog\/h3\/\" target=\"_blank\" rel=\"noreferrer noopener\">Uber\u2019s solution was to develop and deploy hierarchical hexagonal indexing (H3)<\/a> at the heart of its own geospatial mapping tools. Here\u2019s a brief overview of how it works:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>Unlike the solution we saw in the prior article \u2013 a series of <em>square tiles<\/em> regressively mapped within a Mercator projection to a set of squares only a few meters on a side \u2013 H3 uses a series of <em>polygons<\/em> to more accurately cover the entire surface of the earth.<br><\/li>\n\n\n\n<li>They found that <em>hexagons<\/em> worked better than squares for mapping applications because a circle fits better within the sides of a hexagon, as well as touching the points of the hexagon. Circles are important because it\u2019s easier to determine how many drivers are within a specific <em>radius<\/em> of a requesting rider\u2019s location.<br><\/li>\n\n\n\n<li>One wrinkle: Because the earth isn\u2019t a perfect sphere, H3 at its lowest resolution actually required use of 12 <em>pentagons<\/em> plus 110 <em>hexagons<\/em> for accurate coverage. (Circles <em>still<\/em> cover more area within \/ around a pentagon than within a square.)<br><\/li>\n\n\n\n<li>Each polygon contains a set of like polygons within it \u2013 so a hexagon encompasses seven other polygons, which in turn each contain seven other polygons, and so forth until the highest resolution level (23) is reached.<br><\/li>\n\n\n\n<li>The real elegance of this strategy is that each polygon in this hierarchical structure knows exactly which polygon is its <em>parent<\/em> in the level above it (unless it\u2019s at lowest resolution), which polygons are its <em>siblings<\/em> at the same resolution level, and which polygons are its <em>children<\/em> (unless it\u2019s at the highest resolution).<\/li>\n<\/ul>\n<\/div>\n\n\n<h2 class=\"wp-block-heading\">Corralling Data Within H3 Indexed Boundaries<\/h2>\n\n\n\n<p>In the example query in Figure 2 (below), I used the <code>SDO_UTIL.H3_KEY<\/code> and <code>SDO_UTIL.H3_BOUNDARY<\/code> functions to return the corresponding <code>H3_KEY<\/code> and <code>H3_BOUNDARY<\/code> values based on longitude\/latitude pair values at a specific resolution for the photovoltaic array data stored in table <code>EXISTING_PV_SITES<\/code>.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \" title=\"Figure 2. Using H3 Index Values in Simple Queries\">WITH h3_values AS (\n  SELECT \n    usgs_case_id AS case_id\n  , state_abbr\n  , county\n  , pv_array_area\n  , facility_capacity_dc\n  , facility_capacity_ac\n  , SDO_UTIL.H3_KEY(longitude, latitude, :res) AS h3key\n    FROM existing_pv_sites)\nSELECT \n  h3key\n, case_id\n, state_abbr\n, county\n, pv_array_area\n, facility_capacity_dc\n, facility_capacity_ac\n, SDO_UTIL.H3_BOUNDARY(h3key) AS h3geom\n  FROM h3_values\n  WHERE state_abbr = \u2018WI\u2019\n  FETCH FIRST 10 ROWS ONLY\n;<\/pre><\/div>\n\n\n\n<p><em>Figure 2: Using H3 Index Values in Simple Queries<\/em><\/p>\n\n\n\n<p>Here\u2019s what the query returned at resolution level 3 (Figure 3).<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1005\" height=\"312\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-2.png\" alt=\"What the query returned at resolution level 3.\" class=\"wp-image-107826\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-2.png 1005w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-2-300x93.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-2-768x238.png 768w\" sizes=\"auto, (max-width: 1005px) 100vw, 1005px\" \/><figcaption class=\"wp-element-caption\"><em>Figure 3: Result Set, Resolution Level 3<\/em><\/figcaption><\/figure>\n\n\n\n<p>There\u2019s a subtle difference when we compare what\u2019s returned when the same query runs at resolution level 5 (Figure 4): the <code>H3_KEY<\/code> value for the same row is different because it represents a <em>higher resolution<\/em>.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1005\" height=\"313\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-3.png\" alt=\"\" class=\"wp-image-107827\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-3.png 1005w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-3-300x93.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-3-768x239.png 768w\" sizes=\"auto, (max-width: 1005px) 100vw, 1005px\" \/><figcaption class=\"wp-element-caption\"><em>Figure 4: Result Set, Resolution Level 5<\/em><\/figcaption><\/figure>\n\n\n\n<p>Taking a look at the corresponding boundaries that correspond to an <code>H3_KEY<\/code> value, it becomes even clearer: It\u2019s a <em>hexagon <\/em>with its six corners defined as specific <em>longitude and latitude pairs. <\/em>For example, Figure 5 shows the contents of the <code>SDO_GEOMETRY<\/code> column for <code>H3_KEY<\/code> value 085275323FFFFFFFF from the query results in Figure 4.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">MDSYS.SDO_GEOMETRY(2003, 4326, NULL, \n  MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1), \n  MDSYS.SDO_ORDINATE_ARRAY(\n  -91.4076865340508, 44.3821017830468, \n  -91.4955798265095, 44.3196700364542, \n  -91.4583287104107, 44.2339873687158, \n  -91.3335337928282, 44.2106053298784, \n  -91.2455839698387, 44.2728414450628, \n  -91.2824847052138, 44.3586550517715, \n  -91.4076865340508, 44.3821017830468))<\/pre><\/div>\n\n\n\n<p><em>Figure 5: <code>H3_BOUNDARY<\/code> Values of a Specific <code>H3_KEY<\/code> Polygon<\/em><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-grouping-disparate-data-within-h3-indexed-boundaries\">Grouping Disparate Data within H3 Indexed Boundaries<\/h2>\n\n\n\n<p>Another neat thing about <code>H3_KEY<\/code> values: Since they reference <em>identical<\/em> polygon boundaries (either pentagon or hexagon) at a particular resolution level, I can group aggregate data from multiple data sources at multiple resolution levels to provide hierarchical reporting for <a href=\"https:\/\/solutionsreview.com\/data-management\/defining-the-disparate-in-disparate-data\/\" target=\"_blank\" rel=\"noreferrer noopener\">disparate data<\/a>.<\/p>\n\n\n\n<p>The query in Figure 6 shows exactly this strategy by first <em>summarizing<\/em> values within their derived <code>H3_KEY<\/code> for solar arrays and wind turbines <em>at the same resolution level<\/em>, then linking those aggregate values based on their matching <code>H3_KEY<\/code> attributes (i.e. within the same H3 boundary polygon).<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">WITH \n  pv AS (\n      SELECT \n        SDO_UTIL.H3_KEY(longitude, latitude, 5) AS h3key\n      , state_abbr\n      , county\n      , COUNT(usgs_case_id) AS cnt_pv\n      , SUM(pv_array_area) AS tot_pv_area\n      , SUM(facility_capacity_ac) AS tot_pv_cap_ac\n      , SUM(facility_capacity_dc) AS tot_pv_cap_dc\n      FROM existing_pv_sites\n     GROUP BY h3key, state_abbr, county\n     ORDER BY h3key)\n ,wt AS (\n     SELECT \n       SDO_UTIL.H3_KEY(geometry, 5) AS h3key\n     , COUNT(usgs_case_id) AS cnt_wt\n     , SUM(turbine_capacity) AS tot_wt_cap\n      FROM existing_wind_turbines\n     GROUP BY h3key\n     ORDER BY h3key)\n  SELECT \n        pv.h3key \n      , pv.state_abbr\n      , pv.county\n      , pv.cnt_pv \n      , pv.tot_pv_area\n      , pv.tot_pv_cap_ac\n      , pv.tot_pv_cap_dc\n      , wt.cnt_wt \n      , wt.tot_wt_cap \n   FROM pv, wt\n  WHERE pv.h3key = wt.h3key\n  ORDER BY pv.h3key, pv.state_abbr, pv.county;<\/pre><\/div>\n\n\n\n<p><em>Figure 6: Gathering Aggregate Data from Disparate Data Sources through Matching <code>H3_KEY<\/code> Values<\/em><\/p>\n\n\n\n<p>The resulting report (Figure 7) shows aggregated data from both sources, aggregated within the <code>H3_KEY<\/code> level at resolution level 5. Of course, I could also make the resolution level a query <em>bind variable<\/em>, and that would let me aggregate disparate data at that specific resolution within the polygon boundaries returned.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"951\" height=\"382\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-4.png\" alt=\"The aggregated data report, showing aggregated data from both sources.\" class=\"wp-image-107828\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-4.png 951w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-4-300x121.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-4-768x308.png 768w\" sizes=\"auto, (max-width: 951px) 100vw, 951px\" \/><figcaption class=\"wp-element-caption\"><em>Figure 7: Aggregated Data Report<\/em><\/figcaption><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Using H3 Indexing Features in Mapping Applications<\/h2>\n\n\n\n<p>To leverage H3 indexing in a mapping application, I used <code>SDO_UTIL<\/code> procedure <code>H3SUM_CREATE_TABLE<\/code> to aggregate statistics related to H3 polygons into a single table.<\/p>\n\n\n\n<p>The code in Figure 8 creates an H3 table named <code>H3SUM_WIND_TURBINES<\/code> from table <code>EXISTING_WIND_TURBINES<\/code>. I\u2019m using that table as the data source because it has the highest number of disparate geospatial points spread out across the United States, so it\u2019ll make mapping the aggregated data a bit more interesting.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">DROP TABLE IF EXISTS h3sum_wind_turbines;\nDELETE FROM user_sdo_geom_metadata WHERE table_name = 'H3SUM_WIND_TURBINES';\n\nBEGIN\n  SDO_UTIL.H3SUM_CREATE_TABLE(\n    table_out    =&gt; 'H3SUM_WIND_TURBINES'\n  , table_in     =&gt; 'EXISTING_WIND_TURBINES'\n  , geomcol_spec =&gt; 'GEOMETRY'\n  , col_spec     =&gt; '1,CNT\u2019\n  , max_h3_level =&gt; 7\n  );\nEND;\n\/<\/pre><\/div>\n\n\n\n<p><em>Figure 8: Creating an H3 Table from Existing <code>SDO_GEOMETRY<\/code> Data<\/em><\/p>\n\n\n\n<p>A few key points about how this H3 summary table was constructed:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>It will contain a simple<em> count<\/em> of the number of turbines within each aggregate <code>H3KEY<\/code> resolution level, as the query in Figure 9 shows.<br><\/li>\n\n\n\n<li>It will also contain <code>SDO_GEOMETRY<\/code> column aptly named <code>KEY<\/code> that describes the polygon containing the point cloud at that resolution.<br><\/li>\n\n\n\n<li>Since individual wind turbines are still relatively sparse across a geography as vast as the USA, I\u2019ve constrained the maximum H3 resolution to level 7<strong> <\/strong>to limit the creation of polygons beyond that level.<br><\/li>\n\n\n\n<li>The command to delete from <code>USER_SDO_GEOM_METADATA<\/code> simply removes the H3 index that gets created automatically whenever an H3 summary table is created.<\/li>\n<\/ul>\n<\/div>\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT levelnum, COUNT(*) \n  FROM h3sum_wind_turbines\n GROUP BY levelnum\n ORDER BY levelnum;\n\n  LEVELNUM   COUNT(*)\n---------- ----------\n         0         10\n         1         34\n         2        107\n         3        341\n         4        844\n         5       1908\n         6       4853\n         7      15709<\/pre><\/div>\n\n\n\n<p><em>Figure 9: Querying H3 Summary Table Contents<\/em><\/p>\n\n\n\n<p>Just as I did in the <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/creating-uber-fast-maps-with-23ai-vector-tiles-and-h3-indexes-part-1\/\" target=\"_blank\" rel=\"noreferrer noopener\">prior article<\/a>, I\u2019ll transform the vector tiles that get produced from H3 Indexed data into something that mapping software can display using Oracle\u2019s <code>ORDS REST API<\/code> toolset.<\/p>\n\n\n\n<p>Figure 10 shows how I built an <code>ORDS REST API<\/code> <em>module<\/em> named <code>wt_summary<\/code>, defined a corresponding <em>template<\/em> that accepts variable values, and finally defined a <em>handler<\/em> that returns just the required appropriate vector tiles as a <code>BLOB<\/code> based on the parameter values specified.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">BEGIN\n   ORDS.DELETE_MODULE('wt_summary');\nEND;\n\/\n\nBEGIN\n  ORDS.DEFINE_MODULE(\n    P_MODULE_NAME =&gt; 'wt_summary',\n    P_BASE_PATH =&gt; '\/wt_summary\/',\n    P_ITEMS_PER_PAGE =&gt; 25,\n    P_STATUS =&gt; 'PUBLISHED',\n    P_COMMENTS =&gt; 'Wind Turbine H3 Summary Tiling REST API'\n  );\n  COMMIT;\nEND;\n\/\n\nBEGIN\n  ORDS.DEFINE_TEMPLATE(\n    P_MODULE_NAME =&gt; 'wt_summary',\n    P_PATTERN =&gt; 'vt\/:z\/:x\/:y',\n    P_PRIORITY =&gt; 0,\n    P_ETAG_TYPE =&gt; 'HASH',\n    P_COMMENTS =&gt; 'Wind Turbine H3 Summary Tiling Template'\n  );\n  COMMIT;\nEND;\n\/\n\nBEGIN\n  ORDS.DEFINE_HANDLER(\n    P_MODULE_NAME =&gt; 'wt_summary'\n   ,P_PATTERN =&gt; 'vt\/:z\/:x\/:y'\n   ,P_METHOD =&gt; 'GET'\n   ,P_SOURCE_TYPE =&gt; ORDS.SOURCE_TYPE_MEDIA\n   ,P_SOURCE =&gt; \n'SELECT\n  ''application\/vnd.mapbox-vector-tile'' as mediatype \n  ,SDO_UTIL.H3SUM_VECTORTILE(\n     H3_TABLE =&gt; ''H3SUM_WIND_TURBINES''\n    ,LEVELNUM =&gt; \n       CASE \n         WHEN z.z &lt;= 5              THEN z*1 \n         WHEN z.z BETWEEN  6 AND  9 THEN z-1\n         WHEN z.z BETWEEN 10 AND 13 THEN z-2\n         WHEN z.z BETWEEN 12 AND 16 THEN z-3\n         WHEN z.z BETWEEN 14 AND 19 THEN z-4 \n         WHEN z.z &gt; 20              THEN 15\n       END\n    ,TILE_X =&gt; :x\n    ,TILE_Y =&gt; :y\n    ,TILE_ZOOM =&gt; z) as VT\n  FROM (SELECT :z AS z) z');\nEND;\n\/<\/pre><\/div>\n\n\n\n<p><em>Figure 10: ORDS REST API Module, Template, and Handler for <code>EXISTING_WIND_TURBINES<\/code> Vector Tiles<\/em><\/p>\n\n\n\n<p>This ORDS module, template, and handler are nearly identical to the examples I provided for vector tiles in the <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/creating-uber-fast-maps-with-23ai-vector-tiles-and-h3-indexes-part-1\/\" target=\"_blank\" rel=\"noreferrer noopener\">prior article<\/a>, but there\u2019s one interesting wrinkle for the zoom level bind variable (:z). Since H3 resolution is more fine-grained than vector tiles created via the <code>GET_VECTORTILE<\/code> procedure, the <code>CASE<\/code> statement in the ORDS handler ratchets up the zoom level appropriately to a slightly higher value. Otherwise, the polygons returned from the <code>H3SUM_VECTORTILE<\/code> procedure would be so small they\u2019d be unlikely to contain any points.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Displaying H3 Summary Vector Tiles Via MapLibre GL<\/h2>\n\n\n\n<p>Since the <code>H3SUM_VECTORTILE<\/code> procedure returns vector tiles, I can reuse the majority of my code base I had already built to display them using <a href=\"https:\/\/maplibre.org\/maplibre-gl-js\/docs\/\" target=\"_blank\" rel=\"noreferrer noopener\">MapLibre GL<\/a>. I\u2019ve retained the same settings for initial map position, initial zoom level and base map style; the only modifications I needed to make accommodate the <code>wt_summary<\/code> ORDS API module. Figure 11 shows the main differences:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"521\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-5-1024x521.png\" alt=\"An image showing the modifications needed to accommodate the wt_summary ORDS API module.\" class=\"wp-image-107832\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-5-1024x521.png 1024w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-5-300x153.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-5-768x391.png 768w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-5.png 1040w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><figcaption class=\"wp-element-caption\"><em>Figure 11: MapLibre Live Server Specification: Adding the H3 Summary Vector Tiles as a Map Layer<\/em><\/figcaption><\/figure>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>The code in the red box accesses the ORDS endpoint for the existing wind turbines I created in Figure 11. Just as before, it connects with the MapLibreGL interface to move the display to selected vector tile contents at the corresponding <em>zoom level <\/em>for the corresponding<em> X and Y coordinates.<\/em><br><\/li>\n\n\n\n<li>The code in the blue box adds the layer to the MapLibreGL map. I tweaked the code here a bit to display a different color for polygons based on the total count of points captured within each polygon, ranging from <em>light green<\/em> for less than 10 points, <em>yellow<\/em> for 10-14 points, <em>purple<\/em> for 15-19 points, and <em>red<\/em> for 20 points or more.<br><\/li>\n\n\n\n<li>The code in the green box displays a pop-up when one of the polygons is clicked; it contains just the count of points captured within the polygon, since that\u2019s the only aggregate data returned for H3 summary vector tiles.<\/li>\n<\/ul>\n<\/div>\n\n\n<p>And what do the corresponding maps look like? Let\u2019s start at nearly the lowest display level of 10 km resolution (Figure 12), which shows concentrations of wind turbines in south-central Wisconsin in the USA. Note the yellow polygon contains 11 wind turbines, providing a visual clue that area has a higher concentration than the other light-green polygons nearby:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"727\" height=\"595\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-6.png\" alt=\"A map showing the concentrations of wind turbines in south-central Wisconsin in the USA.\" class=\"wp-image-107833\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-6.png 727w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-6-300x246.png 300w\" sizes=\"auto, (max-width: 727px) 100vw, 727px\" \/><figcaption class=\"wp-element-caption\"><em>Figure 12: H3 Summary Vector Tiles at 10km Resolution<\/em><\/figcaption><\/figure>\n\n\n\n<p>Zooming out to 30km resolution (Figure 13), the color coding of the polygons starts to make a bit more sense. Even with fewer polygons, we\u2019re still able to identify different concentrations of wind turbines. Note the pop-up for the red polygon reflects the higher number within (48) than others nearby:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"696\" height=\"570\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-7.png\" alt=\"Another map of wind turbines in Wisconsin, USA.\" class=\"wp-image-107834\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-7.png 696w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-7-300x246.png 300w\" sizes=\"auto, (max-width: 696px) 100vw, 696px\" \/><figcaption class=\"wp-element-caption\"><em>Figure 13: H3 Summary Vector Tiles at 30km Resolution<\/em><\/figcaption><\/figure>\n\n\n\n<p>What does the view at a nearly-continental resolution level look like? At 500km (Figure 14), it\u2019s easy to see the Great Plains of the United States is clearly the champion of wind generation for the country:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"649\" height=\"372\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-8.png\" alt=\"500km resolution map of the United States.\" class=\"wp-image-107835\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-8.png 649w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-8-300x172.png 300w\" sizes=\"auto, (max-width: 649px) 100vw, 649px\" \/><figcaption class=\"wp-element-caption\"><em>Figure 14: H3 Summary Vector Tiles at 500km Resolution<\/em><\/figcaption><\/figure>\n\n\n\n<p>And finally, at 1000km resolution (Figure 15), we\u2019re able to view the data from the minimum zoom level:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"654\" height=\"655\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-9.png\" alt=\"1000km resolution map of the United States and surrounding countries.\" class=\"wp-image-107836\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-9.png 654w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-9-300x300.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-9-150x150.png 150w\" sizes=\"auto, (max-width: 654px) 100vw, 654px\" \/><figcaption class=\"wp-element-caption\"><em>Figure 15: H3 Summary Vector Tiles at 1000km Resolution<\/em><\/figcaption><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Vector Tile Caching<\/h2>\n\n\n\n<p>While vector tiles are easily and speedily generated in the examples I\u2019ve presented, what about a scenario with <em>much larger<\/em> point clouds? For example, imagine a scenario for a financial institution tracking hundreds of thousands of bank branches or automated teller machines (ATMs) around the world.<\/p>\n\n\n\n<p>If I could <em>retain<\/em> the vector tiles generated for the most commonly accessed points, that could save an enormous amount of compute time whenever a mapping application needed to display the tiles containing those points.<\/p>\n\n\n\n<p>The good news is it\u2019s easy to enable <em>vector tile caching<\/em> with a few simple calls to <code>SDO_UTIL<\/code> procedures. Figure 16 shows how I enabled caching for the <code>GEOMETRY<\/code> column in tables <code>EXISTING_EV_CHARGERS<\/code> and <code>EXISTING_PV_SITES<\/code> with the <code>ENABLE_VECTORTILE_CACHE<\/code> procedure:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">BEGIN\n   -- Enable vector tile caching for the GEOMETRY column in EXISTING_EV_CHARGERS\n   SDO_UTIL.ENABLE_VECTORTILE_CACHE(\n     table_name =&gt; 'EXISTING_EV_CHARGERS'\n   , geom_col_name =&gt; 'GEOMETRY'\n   , ts_name =&gt; 'DATA'\n   , min_zoom =&gt; 0\n   , max_zoom =&gt; 23\n   );\n\n   -- Enable vector tile caching for the GEOMETRY column in EXISTING_PV_SITES\n   SDO_UTIL.ENABLE_VECTORTILE_CACHE(\n     table_name =&gt; 'EXISTING_PV_SITES'\n   , geom_col_name =&gt; 'GEOMETRY'\n   , ts_name =&gt; 'DATA'\n   , min_zoom =&gt; 0\n   , max_zoom =&gt; 15\n   );\nEND;\n\/<\/pre><\/div>\n\n\n\n<p><em>Figure 16: Enabling Vector Tile Caching at Different Zoom Levels<\/em><\/p>\n\n\n\n<p>A few points to consider from this example:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>Since there are considerably <em>fewer<\/em> solar array sites than EV chargers, I\u2019ve stopped their caching at zoom level 15; any vector tiles at levels 16-23 would not be cached.<br><\/li>\n\n\n\n<li>Since EV chargers would likely be searched for at deeper zoom levels, I\u2019m permitting them to be cached at the <em>maximum<\/em> zoom level (23).<br><\/li>\n\n\n\n<li>If there was an <em>additional<\/em> column of datatype <code>SDO_GEOMETRY<\/code> in either table, it would not be cached unless specifically mentioned.<\/li>\n<\/ul>\n<\/div>\n\n\n<p>Once vector tile caching is enabled, I can query view <code>SDO_VECTOR_TILE_CACHE$INFO<\/code> to view which tables containing which columns of data type <code>SDO_GEOMETRY<\/code> are enabled for vector tile caching (Figure 17):<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"607\" height=\"414\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-10.png\" alt=\"An image showing which tables containing columns of data type SDO_GEOMETRY are enabled for vector tile caching.\" class=\"wp-image-107837\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-10.png 607w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-10-300x205.png 300w\" sizes=\"auto, (max-width: 607px) 100vw, 607px\" \/><figcaption class=\"wp-element-caption\"><em>Figure 17: Viewing Metadata for Cached Vector Tiles<\/em><\/figcaption><\/figure>\n\n\n\n<p>Figure 18 shows that vector tile caching is actually happening. A simple query against the <code>SDO_VECTOR_TILE_CACHE$TABLE<\/code> view shows which vector tiles have been cached as a result of queries for one or more specific vector tiles \u2013 in this case, the tile located at (X,Y) = (129,187) at zoom level 9:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"975\" height=\"162\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-11.png\" alt=\"Image showing that vector tile caching is actually happening.\" class=\"wp-image-107838\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-11.png 975w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-11-300x50.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/image-11-768x128.png 768w\" sizes=\"auto, (max-width: 975px) 100vw, 975px\" \/><figcaption class=\"wp-element-caption\"><em>Figure 18: Viewing Metadata for Cached Vector Tiles<\/em><\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Controlling Access to Cached Vector Tiles<\/h3>\n\n\n\n<p>I also have the power to limit which user accounts are allowed to leverage cached vector tiles. The code blocks in Figure 19 show two examples of access control for vector tiles:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">BEGIN\n   SDO_UTIL.GRANT_VECTORTILE_CACHE(\n     schema_name =&gt; 'GEOSWARM'\n   , read_only =&gt; TRUE\n   );\nEND;\n\/\n\nBEGIN\n   SDO_UTIL.REVOKE_VECTORTILE_CACHE(schema_name =&gt; 'HRADM');\nEND;<\/pre><\/div>\n\n\n\n<p><em>Figure 19: GRANTing or REVOKing Access to Existing Vector Tile Caches<\/em><\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>The first code block <em>grants<\/em> the <code>GEOSWARM<\/code> user account access to all vector tile caches owned by the <em>executing<\/em> schema via procedure via <code>SDO_UTIL.GRANT_VECTORTILE_CACHE<\/code>.<br><\/li>\n\n\n\n<li>The second code block does the opposite: it <em>revokes<\/em> access to any vector tiles owned by the <em>executing<\/em> schema to the <code>HRADM<\/code> user account.<\/li>\n<\/ul>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\">Deactivating and Purging Vector Tile Caching<\/h3>\n\n\n\n<p>Finally, I have the power to deactivate vector tile caching and even purge cached vector tiles, as shown in Figure 20:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">BEGIN\n   SDO_UTIL.DISABLE_VECTORTILE_CACHE(\n     table_name =&gt; 'EXISTING_WIND_TURBINES'\n   , geom_col_name =&gt; 'GEOMETRY'\n   );\nEND;\n\/\n\nBEGIN\n   SDO_UTIL.PURGE_VECTORTILE_CACHE(table_name =&gt; 'EXISTING_WIND_TURBINES');\nEND;\n\/<\/pre><\/div>\n\n\n\n<p><em>Figure 20: Disabling Caching and Purging Already-Cached Vector Tiles<\/em><\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>In the first code block, I <em>disable<\/em> vector tile caching for specific <code>SDO_GEOMETRY<\/code> datatype columns that could be used to generate vector tiles via <code>SDO_UTIL.DISABLE_VECTORTILE_CACHE<\/code>. &nbsp;Note that if the <code>EXISTING_WIND_TURBINES<\/code> table had <em>another<\/em> <code>SDO_GEOMETRY<\/code> column already being cached for that table, those vector tiles would <em>remain<\/em> cached.<br><\/li>\n\n\n\n<li>Likewise, as the second code block shows, I can <em>purge<\/em> any cached vector tiles for a specific table via <code>SDO_UTIL.PURGE_VECTORTILE_CACHE<\/code>.<\/li>\n<\/ul>\n<\/div>\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">BEGIN\n   SDO_UTIL.DISABLE_VECTORTILE_CACHE(\n     table_name =&gt; 'EXISTING_WIND_TURBINES'\n   , geom_col_name =&gt; 'GEOMETRY'\n   );\nEND;\n\/\n\nBEGIN\n   SDO_UTIL.PURGE_VECTORTILE_CACHE(table_name =&gt; 'EXISTING_WIND_TURBINES');\nEND;\n\/<\/pre><\/div>\n\n\n\n<p><em>Figure 20: Disabling Caching and Purging Already-Cached Vector Tiles<\/em><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-final-thoughts\">Final Thoughts<\/h2>\n\n\n\n<p>I hope these two articles have enlightened you on the new mapping capabilities that Oracle Database 23ai presents, especially if you\u2019re contemplating the deployment of an advanced geospatial solution. <\/p>\n\n\n\n<p>The flexibility and rapid retrieval they offer should make a compelling case for leveraging your existing Oracle Database licensing as your go-to data platform before considering the adoption of other specialized geospatial databases.<\/p>\n\n\n\n<div id=\"callout-block_acf2f3b27681de47e70f3ad605e694cb\" class=\"callout alignnone\">\n    <div class=\"child-last:mb-0 child-first:mt-0 bg-gray-50 dark:bg-gray-950 p-4xl my-3xl\">\n\n<p><strong>Download the code featured in this article<\/strong><br>If you\u2019d like to experiment with the APEX 24.2 app and the MapBox examples to explore the mapping features presented in this article, you can download all application code samples <a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/Code-for-2025-10-Article.zip\">here<\/a>.<img decoding=\"async\" class=\"wp-image-107840\" style=\"width: NaNpx;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/11\/Code-for-2025-10-Article-1.zip\" alt=\"\"><\/p>\n\n<\/div>\n<\/div> \n\n\n<section id=\"faq\" class=\"faq-block my-5xl\">\n    <h2>FAQs: Building High-Performance Maps with Vector Tiles and H3 Indexing<\/h2>\n\n                        <h3 class=\"mt-4xl\">1. What are vector tiles and why are they important for map performance?<\/h3>\n            <div class=\"faq-answer\">\n                <div>Vector tiles store geographic data in small chunks, enabling fast rendering and smooth zooming compared to traditional raster tiles.<\/div>\n            <\/div>\n                    <h3 class=\"mt-4xl\">2. How does the H3 index improve spatial queries?<\/h3>\n            <div class=\"faq-answer\">\n                <div>H3 is a hexagonal grid system that makes spatial indexing more efficient, allowing quick proximity searches and clustering.<\/div>\n            <\/div>\n                    <h3 class=\"mt-4xl\">3. Why combine 23AI vector tiles with H3 indexing?<\/h3>\n            <div class=\"faq-answer\">\n                <div>This combination delivers high-speed map rendering and accurate spatial analysis, ideal for large-scale geospatial applications.<\/div>\n            <\/div>\n                    <h3 class=\"mt-4xl\">4. Can these techniques be used with open-source tools?<\/h3>\n            <div class=\"faq-answer\">\n                <div>Yes, many libraries and frameworks support vector tiles and H3 indexing, making them accessible for developers.<\/div>\n            <\/div>\n                    <h3 class=\"mt-4xl\">5. What are the main benefits for developers?<\/h3>\n            <div class=\"faq-answer\">\n                <div>Faster map performance, reduced data transfer, and improved spatial query accuracy for complex geospatial datasets.<\/div>\n            <\/div>\n            <\/section>\n\n\n\n<section id=\"my-first-block-block_01e42b1b2804f99fcb40fa03846bb65f\" class=\"my-first-block alignwide\">\n    <div class=\"bg-brand-600 text-base-white py-5xl px-4xl rounded-sm bg-gradient-to-r from-brand-600 to-brand-500 red\">\n        <div class=\"gap-4xl items-start md:items-center flex flex-col md:flex-row justify-between\">\n            <div class=\"flex-1 col-span-10 lg:col-span-7\">\n                <h3 class=\"mt-0 font-display mb-2 text-display-sm\">Subscribe to the Simple Talk newsletter<\/h3>\n                <div class=\"child:last-of-type:mb-0\">\n                                            Get selected articles, event information, podcasts and other industry content delivered straight to your inbox every two weeks.                                    <\/div>\n            <\/div>\n                            <a href=\"https:\/\/www.red-gate.com\/simple-talk\/subscribe\/\" class=\"btn btn--secondary btn--lg\">Subscribe now<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Discover how to build ultra-fast mapping applications using Oracle Database 23ai\u2019s new vector tile and H3 hexagonal indexing features. Part 2 of this series walks you through real-world implementation steps, SQL\/API code and performance boosts to handle millions of spatial points with ease.&hellip;<\/p>\n","protected":false},"author":346239,"featured_media":107065,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143523,143533],"tags":[4459,6023],"coauthors":[159330],"class_list":["post-107824","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-oracle-databases","tag-oracle","tag-spatial-geospatial"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107824","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/346239"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=107824"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107824\/revisions"}],"predecessor-version":[{"id":108025,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107824\/revisions\/108025"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/107065"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=107824"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=107824"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=107824"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=107824"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}