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

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">My first piece of advice came straight away - enable <code>Lazy Loading</code>. It turns out <code>Lazy Loading</code> was already enabled.</div>
</div>

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1697140002513/a2f008d7-d829-47c4-8f82-04270091d193.png align="center")

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.

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">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.</div>
</div>

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!

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1697147939986/e3b4cca4-f6ad-42ee-8856-4a81b7b0f5a2.png align="center")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1697148287872/df21898e-96ee-4961-9f91-93f206783f27.png align="center")

## The Solution

<div data-node-type="callout">
<div data-node-type="callout-emoji">❇</div>
<div data-node-type="callout-text">1. Generate a new <code>SDO_GEOMETRY</code> type column in our table, based on the <code>Longitude</code>/<code>Latitude</code> information.</div>
</div>

```sql
alter table earthquakes 
add sdo_geom sdo_geometry;
```

<div data-node-type="callout">
<div data-node-type="callout-emoji">❇</div>
<div data-node-type="callout-text">2. Update the new <code>sdo_geom</code> column using the data from <code>longitude</code> and <code>latitude</code> columns and the <code>sdo_util</code> package.</div>
</div>

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

<div data-node-type="callout">
<div data-node-type="callout-emoji">❇</div>
<div data-node-type="callout-text">3. Create a Spatial Index on <code>sdo_geom</code> column.</div>
</div>

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

<div data-node-type="callout">
<div data-node-type="callout-emoji">❇</div>
<div data-node-type="callout-text">4. Use the new <code>sdo_geom</code> column in your APEX Map and turn the <code>Spatial Index</code> option ON ✅.</div>
</div>

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1697180621531/c27c03a9-a4ba-4b02-a1e6-3abef5dde639.png align="center")

## The Result

<div data-node-type="callout">
<div data-node-type="callout-emoji">🏁</div>
<div data-node-type="callout-text">The initial page load time dropped from <code>20 seconds</code> <strong>to</strong> <code>5 seconds</code>. By using <code>SDO_GEOMETRY</code> column and a <code>Spatial Index</code> we now have the <code>Lazy Loading</code> 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.</div>
</div>

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1697181427325/4f71c8ac-d440-4558-99a2-ed2c71095d00.png align="center")

> \*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

[🗺️](https://emojipedia.org/world-map) 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](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](https://apex.oracle.com/pls/apex/r/gamma_dev/demo/75k-points-map-spatial-index)
    

## Conclusion

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text"><strong>Lazy Loading</strong> needs <code>SDO_GEOMETRY</code> data type to work properly. If you only use <code>Longitude</code>/<code>Latitude</code> columns and you don't have a Spatial indexed <code>SDO_GEOMETRY</code> column, you can't benefit from it.</div>
</div>

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1697708133668/7bee0a79-8d6b-4ea2-9a3f-05b529f71014.png align="center")

**💡**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.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1697150851033/84212a6b-cda2-48fb-a742-b0aeeda6b719.png align="center")

## More about APEX Maps

For more information about the Map Region, different data types and interesting use cases - read my previous blog posts on the topic:

%[https://blog.apexapplab.dev/understanding-better-the-apex-map-region] 

%[https://blog.apexapplab.dev/how-to-find-nearest-stadiums-using-oracle-apex-maps-and-geolocation] 

%[https://blog.apexapplab.dev/getting-the-best-address-and-location-searches-in-oracle-apex] 

## Follow me

[![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684769855346/4a53f86b-e5ba-4c0b-bf79-0397a8f3c054.png?auto=compress,format&format=webp&auto=compress,format&format=webp&auto=compress,format&format=webp&auto=compress,format&format=webp align="left")](https://twitter.com/plamen_9?ref_src=hashnode)

[![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684769877594/82a5de36-0e62-48e9-94d7-81620e92018b.png?auto=compress,format&format=webp&auto=compress,format&format=webp&auto=compress,format&format=webp&auto=compress,format&format=webp align="left")](www.linkedin.com/comm/mynetwork/discovery-see-all?usecase=PEOPLE_FOLLOWS&followMember=plamen-mushkov)