The new sample map application that has been provided for Oracle APEX 21.1 is full of great ideas. After reviewing the application, one example I was keen to try for myself was the ‘Nearest Neighbour’ capability.
I had an existing map demo (see previous articles on this site) showing customers in proximity to a restaurant, and I have been able to add additional map layers to my map demo to include this ‘nearest neighbour’ feature. Note that the Nearest Neighbour is based around a call to the Oracle Spatial SDO_NN command.
I can now interactively plant a pin anywhere in the map and flag up the nearest X (from a parameter – in the example below I set it to 4) customers to the pin. I have annotated the screenshot with a hand drawn red ring to show where I dropped the pin. (I also dynamically display the nearest X customers on a report underneath the map).
I thought it’d be great if I had the facility to create a semi-opaque polygon that does that for me. Well, I can do that with another map layer. Here I am using the spatial function SDO_AGGR_CONVEXHULL to create the polygon based on the data from the SDO_NN query I created above. So if I say, “let’s look at the area of coverage of the 10 closest customers to a point where I place my pin”, then I can do this.
This is just what I wanted. However, how did I know that SDO_AGGR_CONVEXHULL was what I needed to achieve this? Well, I am a big fan of Oracle Spatial Studio and one use I make of it is to guide me to some of the spatial techniques that are available in the Oracle Database.
I can create a project in Spatial Studio with one dataset which has a single point of interest (in this case a restaurant, depicted by the star) and another dataset with 100’s of customers, depicted as small circles.
In a Spatial Studio project, I can choose to add a spatial analytic to perform operations between layers. When I do that, I am presented with a menu of available analytics across different types of interaction such as ‘Filter’, ‘Combine’, ‘Measure’, etc. So, when I see one named ‘Return shapes nearest to another‘ which relates to a command called SDO_NN, I think that I would like to see that in action.
When clicking on it I get a template that allows me to select the layer that I am filtering (my customers as I want to see those nearest to my resturant location) and the layer to use as the filter (my restaurant) and I also provide the number of ‘neighbours’ I want to see. There are some more advanced options, but we wont consider these now.
When I add this to the project, and make these 10 ‘Nearest Neighbours’ stand out out as black dots, we see the following.
If we examine the properties of this Nearest Neighbours analysis, we see the code that has been generated to achieve this. Look how it is using the SDO_NN command with the sdo_num_res=10 to restrict to the 10 nearest neighbours.
Now let us add another layer to the map by creating another analysis. If I scroll through the options I see the area defined by a ‘rubber band’ which will envelop a set of shapes. The set of shapes that would like to envelop are the results of my Nearest Neighbours and so I want to add this to my project to see what spatial commands are used to create a polygon around my Nearest Neighbours analytic results
So note that it uses the SDO_AGGR_CONVEXHULL command, and I can click on that icon to get the template. I will create a new analysis and call it ‘NN Polygon’ and feed in the ‘Nearest Neighbours’ dataset we just created and press Run.
I drop this new NN POLYGON analysis data set on the map. I now have my polygon connecting all the neighbours so that I can see the area covered.
OK, let’s look at the properties behind this NN POLYGON.
As we may have suspected, we have the SDO_AGGR_CONVEXHULL call being fed with the results of the SDO_NN results. So using Spatial Studio I have a good idea of the concepts and syntax I need to use in my APEX application to achieve similar results, even though I used different datasets and a slightly different paradigm from the APEX Maps Nearest Neighbours sample application of having a dynamic pin.
I want to narrow down my nearest neighbours with SDO_NN and then feed the results of that into the SDO_AGGR_CONVEXHULL to create my polygon in the format shown below.
select sdo_aggr_convexhull(sdoaggrtype(<GEOCOLUMN>,0.005))
from (
select
<GEOCOLUMN>
from <MY DETAIL TABLE>
where sdo_nn(<GEOCOLUMN>, <GEO POINT WE WANT TO PIVOT AROUND>,
'sdo_num_res=<HOW MANY NEIGHBOURS>')='TRUE'Using this concept in my APEX application, my query dynamically creates the polygon as shown below.
The new APEX Map region certainly has a lot to offer and I’m really enjoying learning more about how to use it.