Using Oracle Spatial in my APEX plugin

Using Oracle Spatial in my APEX plugin

Published on: Category: Oracle

The Spatial functionality in the Oracle database is quite comprehensive. When looking at the documentation, it doesn’t seem to be something you can easily learn. However, I decided to have a closer look at it, because I was looking for a “closest points” function. I want the function to extend my OpenStreetMaps train stations example with the option to find all train stations within a range of x meters from my location. I know it’s also possible to get the “closest points” using JavaScript libraries, but when you need to determine the closest points from a really large data set (which will be stored in the database), the spatial functionality might be a better choice.

Getting started

The latitude and longitude of the train stations have been supplied within my external data source. In a previous tech blog, I wrote about getting data from the web sources.
In order to use the SDO_GEOM functions, I have persisted the train station data in my database:

  1. create table ns_train_stations_geo_tab as
  2. select code
  3. , name
  4. , lat
  5. , lng
  6. , SDO_GEOMETRY(2001
  7. ,8307
  8. ,SDO_POINT_TYPE (lat,lng,NULL)
  9. ,NULL
  10. ,NULL ) geo_location
  11. from json_table(apex_web_service.make_rest_request(p_url => ''
  12. ,p_http_method => 'GET'
  13. )
  14. , '$.payload.locations[*]'
  15. columns(code varchar2(20) path '$.code'
  16. ,name varchar2(200) path '$.name'
  17. ,lat varchar2(30) path '$.lat'
  18. ,lng varchar2(30) path '$.lng'
  19. )
  20. )
  21. where set_request_headers=1

The SDO_GEOMETRY spatial object type can be configured with parameters to the geometrical shape that you need. For example, the 2001 value for the SDO_GTYPE parameter means: The 2 indicates two-dimensional, and the 1 indicates a single point.
The 8307 value for the SRID parameter refers to the coordinate system to be used (wgs84 lat lon system).
The SDO_POINT_TYPE object holds the actual coordinates of the point (in this case, the latitude and longitude of the train station).

Within distance example

The table now has a geo_location column that can be directly used in the SDO_GEOM functions. I can now query the train station that is within a range of 5 kilometers of a location:

  1. select code
  2. , name
  3. from ns_train_stations_geo_tab
  4. where SDO_GEOM.WITHIN_DISTANCE(geom1 => SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE (52.042855, 4.336192,NULL), NULL, NULL)
  5. ,dist => 5
  6. ,geom2 => geo_location
  7. ,tol => 0.01
  8. ,units => 'unit=KM'
  9. ) = 'TRUE'

Closest points example

In the previous example, we can see which stations are within a range of 5 kilometers, but we cannot see which station is the closest. So, we can rewrite the query to:

  1. select *
  2. from (
  3. select code
  4. , name
  6. (geom1 => SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE (52.042855, 4.336192,NULL), NULL, NULL)
  7. ,geom2 => geo_location
  8. ,tol => 100
  9. ,unit => 'unit=M'
  10. ) distance
  11. from ns_train_stations_geo_tab
  12. )
  13. where distance < 5000
  14. order by distance;

Get “my location”

In the previous examples, I used fixed coordinates to test the SDO_GEOM functions. Now, I want to use these functions based on the coordinates of my current location. In Oracle APEX, this is quite easy to retrieve, because modern browsers support the W3C Geolocation functionality. With this functionality, the browser obtains the user’s location based on GPS and/or network and makes it available in JavaScript (after the user explicitly agrees on using the location).

In another tech blog, I described how easy it was to build an OpenStreetMap map chart using Leaflet. I extended this plugin to be able to use the “locate” function, and used the Leaflet Locate library for it.

Putting it together

Now, I have my current location in a JavaScript variable, but I actually need it in the SQL query which retrieves the train station data. Since this SQL query is only executed when the plugin is rendered on the page, I decided to do a page submit in order to rerun the query. Another challenge is to get the current location data into the session state (before the page submit). Here is where I got a little stuck, because I actually needed 3 hidden page items for storing the latitude, longitude and the range values. I couldn’t find how to make these in the plugin, so I created them manually on the page where the plugin is to be included. These 3 hidden items are now a prerequisite for using the plugin.
Setting the values of the hidden items can be done with the JavaScript functions that I created in the plugin. Because a plugin can be installed on multiple pages, the names of the hidden page items have to be supplied to the plugin, using Plugin Attributes.

You can see the working end result in my demo application on the “Spatial OSM Plugin” page, where you can also download the plugin.

Peter de Boer
About the author Peter de Boer

Peter de Boer is a consultant at Qualogy.

More posts by Peter de Boer