Oracle Maps and "Spatial Join" (german: VERSCHNEIDEN)

One of the most important tasks of a GIS application is the "Spatial Join" or "das Verschneiden" (in German):




Oracle Maps may fully take advantage of the inbuilt Oracle Spatial Technology as part of the database. The functions ANYINTERACT and/or SDO_RELATE are used to calculate above e.g. which FOI (customer) is located in the Polygon (REGIERUNGSBEZIRK = County).
When you click ('mouse_click') on the polygon the Spatial Function is invoked and no timeconsuming calculations are carried out - and you have the result within a second.

"How To?"

A mouse_click listener is registered to the map.

mapview.setEventListener("mouse_click", showSelection)

and invokes by means of the 2nd parameter "showSelection" the function below:

This listener adds two themes to display the selected county and customers inside it. Both themes take the mouse click location as the geometry binding parameter.

Sourcecode of Oracle Maps Javascript:

var countyTheme = null ;
var customerTheme = null ;

function showSelection(loc)
{
var mouseLoc = mapview.getMouseLocation();

if(countyTheme)
mapview.removeThemeBasedFOI(countyTheme) ;
if(customerTheme)
mapview.removeThemeBasedFOI(customerTheme) ;

countyTheme = new MVThemeBasedFOI('themebasedfoi1','geo.de_regbez_selected_by_click') ;
countyTheme.setQueryParameters(mouseLoc);
mapview.addThemeBasedFOI(countyTheme);

customerTheme = new MVThemeBasedFOI('themebasedfoi2','geo.de_kunden_in_selected_county') ;
customerTheme.setQueryParameters(mouseLoc);
customerTheme.enableEventPropagation(false);
mapview.addThemeBasedFOI(customerTheme);
}

Enabling the above code to work we have to define two themes based on respective tables (for my example REGBEZ and DEKUNDEN or in the MVDEMO * schema: COUNTIES and CUSTOMERS):
1. de_regbez_selected_by_click (for the MVDEMO schema: counties_selected_by_click)
2. de_kunden_in_selected_county (for the MVDEMO schema: customers_in_selected_counties)
and put some code and SQL in the style definitions (we do use the Mapbuilder:)

1. Theme "DE_REGBEZ_SELECTED_BY_CLICK" - we select the polygon by click:







The code within style of theme "DE_REGBEZ_SELECTED_BY_CLICK":

(sdo_relate(geoloc, :1, 'mask=ANYINTERACT')='TRUE' and rownum >2)

Pls look at the "Features Style"! I have created a feature style of type "Line" and named it "L.DE_NUTS2_SELECTED_YELLOW" and applied it when "Rendering" - so the borders of the county turn out to be highlighted in yellow color when clicked.

2. Theme "DE_KUNDEN_IN_SELECTED_COUNTY":



The Style of the theme "DE_KUNDEN_IN_SELECTED_COUNTY" in detail:





The code:

select a.geoloc, a.name, a.regbez, a.umsatz from dekunden a
where SDO_RELATE(a.geoloc,(select geoloc from de_nuts2 b where sdo_contains(b.geoloc, :1)='TRUE' and rownum<2), 'mask=ANYINTERACT'='TRUE'

Admittedly the above code is related to my tables.
Here is the code you may use for the MVDEMO tables "customers" and "counties":

Code for the first theme/style (county_selected_by_click):

(sdo_relate(geom, :1, 'mask=ANYINTERACT')='TRUE' and rownum<2)

Code for the customer theme (customers_in_selected_county):

select a.location, a.name,
a.city, a.sales from customers a where sdo_relate(a.location, (select
geom from counties b where sdo_contains(b.geom, :1)='TRUE' and
rownum<2), 'mask=ANYINTERACT')='TRUE'

* Using MVDEMO data:
In the Javascript funtion you will have to replace "geo.de_regbez_selected_by_click" by "mvdemo.counties_selected_by_click" and
"geo.de_kunden_in_selected_county" by "mvdemo.customers_in_selected_county"

Finally I like to thank LJ and Ji of the Oracle Maps Development team for their support in achieving the above! Thanks!