How To Optimize APEX Maps Load Time Using Spatial Indexes

The Issue

I recently had an interesting challenge to solve. An APEX map region was taking more than 20 seconds to load. And although the points rendered on the map were over 100,000 this initial load time was unacceptable.

💡

My first piece of advice came straight away - enable Lazy Loading. It turns out Lazy Loading was already enabled.

How exactly does Lazy Loading help:

When lazy loading is specified, the page is rendered immediately, showing an empty region, until the data is loaded. Generally, the page is not displayed until all of the page is loaded and ready to be rendered. Therefore, if it takes 5 seconds to load all of the data for a region, without lazy loading, the end user would have to wait 5 seconds before the page starts to render.

Note - Lazy Loading should only be utilized on data sets that take significant time to load, as this adds unnecessary processing overhead on the database for regions that load quickly.

💡

My next advice would be to initially display just a small portion of the map (zoomed in) so that a minimum amount of the points are loaded.

Since we were happy with the Map being zoomed in (Zoom Level 6), it was expected that just the points inside of the frame would be loaded initially and only when the Map was zoomed out - other points would be loaded.

Unfortunately it wasn't happening - all of the 100,000 points on the map were loaded initially - no matter the zoom level and no matter that just a small portion of it was visible. And it was taking 20 seconds!

The next to check was the Geometry Column Data Type used. Nothing unusual, Latitude/Longitude columns - the most commonly used data type.

The Solution

❇

1. Generate a new SDO_GEOMETRY type column in our table, based on the Longitude/Latitude information.

alter table earthquakes 
add sdo_geom sdo_geometry;

❇

2. Update the new sdo_geom column using the data from longitude and latitude columns and the sdo_util package.

update earthquakes
set sdo_geom = sdo_util.from_json (
                    '{"type":"Point",
                      "coordinates":['||longitude||','||latitude||']}'
                );

❇

3. Create a Spatial Index on sdo_geom column.

create index earthquakes_spatial_idx 
    on earthquakes (sdo_geom)
       indextype is mdsys.spatial_index;

❇

4. Use the new sdo_geom column in your APEX Map and turn the Spatial Index option ON ✅.

The Result

🏁

The initial page load time dropped from 20 seconds to 5 seconds. By using SDO_GEOMETRY column and a Spatial Index we now have the Lazy Loading actually working. As you zoom out the map, you will see the spinner loading icon appear and more points being loaded and displayed on it. You can also see the requests in your browser Developer tools.

*The initial approach with using Longitude/Latitude values and no Spatial Index didn't take advantage of Lazy Loading and everything was served with the initial page load.

Demo

🗺️ What's a blog post without a demo? Here you can see an example of both cases:

75,000 points Map without a Spatial Index, using Longitude/Latitude data:
https://apex.oracle.com/pls/apex/r/gamma_dev/demo/75k-points-map-no-index
75,000 points Map with a Spatial Index, using SDO_GEOMETRY data:
https://apex.oracle.com/pls/apex/r/gamma_dev/demo/75k-points-map-spatial-index

Conclusion

💡

Lazy Loading needs SDO_GEOMETRY data type to work properly. If you only use Longitude/Latitude columns and you don't have a Spatial indexed SDO_GEOMETRY column, you can't benefit from it.

Lessons Learned

💡 When you need to display a large number of data points on an APEX Map or in general you've got a big dataset with geospatial data - generate another SDO_GEOMETRY type column based on the Longitude/Latitude values.

It wouldn't cost much but the benefits would be huge - both performance and functionality. SDO_GEOMETRY data allows you to use Geospatial Analytics functions and packages (such as APEX_SPATIAL, SDO_GEOMETRY, SDO_UTIL, SDO_SAM) and can be used to display a lot more complex objects than just points.

💡Create Spatial Indexes on your SDO_GEOMETRY column. Easy enough and the benefit could be seen above.

💡Always specify Minimum and Maximum Zoom Levels from the Layer attributes. There are rare cases when you would need to show the user a fully zoomed-in or zoomed-out map. Especially with a large number of points on it.

💡Enable Point Clustering - It groups the co-located points on the map and reduces the number of objects displayed on the map at a given point in time. It's especially helpful when you have a huge number of points on the map. The clustering can make it easier to navigate and help for a better user experience. Show details only when the map is zoomed in enough. When zoomed out, show aggregated numbers.