Developing Custom Maps with Oracle Spatial, APEX, REST & GeoJSON

Code Hints from our July 30, 2024 Ask Tom session.

Thank You for Watching Our Presentation

If you are interested in the example code snippets you saw in the presentation today, below you will find complete code samples to help you get started using Oracle Spatial tools.

Quick Jump to:

Point Features | Linear Features | Linear Feature Collections | Transform Function | Vector Refresh Function | Facet Search Query | Spatial Studio Configuration | MapBox Background Style Change | Zoom Control Function

Point Feature GeoJSON

Example code below.

select json_object(
'type' value 'Feature',
'properties' value json_object (
'SCHOOL_ID' value SCHOOL_ID,
'SCHOOL' value SCHOOL,
'SCHOOL_LEVEL' value SCHOOL_LEVEL
),
'geometry' value sdo_cs.transform(c.GEOMETRY,4326).get_geojson() format json
) as geojson
from SCHOOLS c
where c.school_id=:school_id

Linear Features | GeoJSON and CLOB

Example code below.

select json_object(
'type' value 'Feature',
'properties' value json_object (
'ROUTE_ID' value AL_ROUTE,
'FROM_RM' value AL_FROM,
'TO_RM' value AL_TO,
'SIDE_OF_ROAD' value SIDE_OF_ROAD,
'HAZARD' value HAZARD
),
'geometry' value SDO_UTIL.TO_GEOJSON(sdo_cs.transform(c.GEOMETRY,4326))  FORMAT JSON returning clob 
)  as geojson
from GUARD_RAILS c
where c.guardrail_id=:guardrail_id

Questions?

If you have further questions, we would be happy to help. Please fill in the contact form below and we’ll get back to you as soon as possible.



























    Linear Feature Collections

    Example code below.

    select json_object (
'type' value 'FeatureCollection',
'features' value json_arrayagg(
 json_object(
 'type' value 'Feature',
 'properties' value json_object ( 
 'ROUNDABOUT_ID' value  r.roundabout_id
 ,'ROUNDABOUT_APPROACH_ID' value ra.roundabout_approach_id
 ,'APPROACH_ROUTE' value ra.approach_route
 ,'APPROACH_NAME' value ra.approach_name
 ,'APPROACH_DIRECTION' value ra.approach_direction
 ,'ROUNDABOUT_LEG_ID' value rl.roundabout_leg_id 
 ,'ROUTE_ID' value rl.route_id
 ,'FROM_RM' value rl.from_rm
 ,'TO_RM' value rl.to_rm
 ),
 'geometry' value SDO_UTIL.TO_GEOJSON(sdo_cs.transform(rl.GEOMETRY,4326))  FORMAT JSON returning clob) 
 returning clob)
 returning clob) AS geojson 
from spod.roundabouts r
inner join spod.ROUNDABOUT_APPROACHES ra on r.roundabout_id = ra.roundabout_id
inner join ROUNDABOUT_LEGS rl on ra.roundabout_approach_id = rl.roundabout_approach_id
where ra.roundabout_approach_id = :roundabout_approach_id

    Transform Function

    Example code below.

    transformRequest: (url, resourceType) => {
if (resourceType === 'Tile') {
if (url.startsWith('https://api.mapbox.com')){
return {
 url: url,
 };
 } else if (url.startsWith(spatialStudio_Oauth.baseUrl)){
// add the Spatial Studio access token to the tile request header.
 return {
 url : url,
 headers: { 'Authorization': 'Bearer ' + spatialStudio.accessToken },
 }
 } else if (url.startsWith(spatialStudio.baseUrl)){
 return {
 url:url,
 }
 }
 }
 }
 });

    Vector Refresh Function

    This function implements the dataset (vector tile) refresh, when a user clicks on the refresh button. Example code below.

    function refreshvt(v_theme) {
 if(!map){
 return;
 }
 const myHttpHeaders = {
 'Authorization': 'Bearer ' + spatialStudio_Oauth.accessToken 
 }; 
 // Sends an AJAX request to the dataset refresh API endpoint of the spatial studio instance.
 $.ajax({
 type: 'POST',
 url: spatialStudio_Oauth.baseUrl + '/datasets/refresh/'+v_theme,
 dataType: "json",
 headers: myHttpHeaders,  // must add the access token as the custom header
 success: function (response) {
 console.log("refresh success on "+v_theme);
 if ( !response ){
 alert("Cannot get response for the refresh request !");
 return;
 }
 if(!response.dataRefreshed || response.id !== v_theme){
 alert('Error: dataset did not refresh or mismatched dataset id found. ' + JSON.stringify(response) );
 return; 
 }
 },
 error: function(error) {
 // This function is executed if the request fails
 console.log("Error:", error);
 }
 });
 };

    Facet Search Query

    The purpose of this routine is to extract a clob of primary key values from the source DATABASE and place into a clob for later MapBox handling. This function must be executed from inside an Oracel APEX application.

    The steps include:

    1. Obtain the region_id
    2. Obtain query_context. This is the query meta data supporting both sql queries and REST services.
    3. Filter query content in to a listing of primary keys
    4. Output listing of primary keys to CLOB
    declare 
 l_clob        CLOB := EMPTY_CLOB;
 l_clob_out    CLOB := EMPTY_CLOB; 
 l_region_id   number;
 l_context     apex_exec.t_context;
begin
 select region_id
 into l_region_id
 from apex_application_page_regions
 where application_id = v('APP_ID')
 and page_id        = :APP_PAGE_ID
 and static_id      = 'FCRASHID';
 l_context := apex_region.open_query_context( 
 p_page_id      => :APP_PAGE_ID, 
 p_outer_sql    => 'select json_arrayagg( to_number(VCHSTATEREPORTNUMBER) returning clob ) as json_array from #APEX$SOURCE_DATA#',
 p_region_id    => l_region_id );
 if apex_exec.next_row( l_context ) then
 l_clob_out := apex_exec.get_clob( l_context, 1 );
 end if;
 :P20_TEST:=l_clob_out;
end;

    Oracle Spatial Studio Configuration

    Example code below.

    // url setups
 const v_rest_url =	$v('P0_REST_URL');
 const v_map_url = 	$v('P0_MAP_URL');
 const v_token = 	$v('P0_TOKEN'); 
 const spatialStudio_Oauth = {
 baseUrl : v_map_url+'/oauth/v1',
 accessToken: v_token 
 };
 const spatialStudio = {
 baseUrl: v_map_url+'/api/v1/public'
 };

    MapBox Background Style Change Function

    Example code below.

    function setmapstyle(styletype)
 {
 var styleout;
 switch (styletype) {
 case "streets-v11":
 styleout = 'mapbox://styles/mapbox/streets-v11';
 break;
 case "light-v10":
 styleout = 'mapbox://styles/mapbox/light-v10';
 break;
 case "dark-v10":
 styleout = 'mapbox://styles/mapbox/dark-v10';
 break;
 case "satellite-v9":
 styleout = 'mapbox://styles/mapbox/satellite-v9';
 break;
 case "satellite-streets-v11":
 styleout = 'mapbox://styles/mapbox/satellite-streets-v11';
 break;
 case "vector OSM":
 styleout = 'https://api.maptiler.com/maps/openstreetmap/style.json?key=YourKeyHere';
 }
 return styleout;
 };
 function setmapstyle2(styletype)
 {
 var styleout = setmapstyle(styletype);
 map.setstyle(styleout);
 };

    Zoom Control Function

    Example code below.

    class ZoomDisplayControl {
 constructor() {
 this._container = document.createElement('div');
 this._container.className = 'mapboxgl-ctrl mapboxgl-ctrl-group';
 } 
 onAdd(map) {
 this._map = map;
 this._zoomDisplay = document.createElement('div');
 this._zoomDisplay.className = 'mapboxgl-ctrl-icon';
 this._zoomDisplay.style.fontSize = '12px';
 this._zoomDisplay.style.textAlign = 'center';
 this._zoomDisplay.style.lineHeight = '20px';
 this._zoomDisplay.style.width = '30px';
 this._container.appendChild(this._zoomDisplay);
 this.updateZoomDisplay();
 map.on('zoom', () => this.updateZoomDisplay());
 return this._container;
 }
 onRemove() {
 this._container.parentNode.removeChild(this._container);
 this._map = undefined;
 }
 updateZoomDisplay() {
 this._zoomDisplay.textContent = this._map.getZoom().toFixed(2);
 }
 };