Calculate Distances Between Point Geometries in Database through OAC Dataset Definition

Oracle Analytics (OA) is equipped with the capability to use spatial database functions to process datasets containing geometries. One of the ways this can be done in Oracle Analytics is by editing the definition of a data source using spatial SQL syntaxes when you create the dataset from a database connection. Assuming you are connecting to an Oracle Database, you can complete spatial calculations such as distance, area, length, and many more by entering the corresponding SQL function from the SDO_GEOMETRY package in the dataset definition.

In this blog, I will show how to calculate the distances between the hotels and Thai restaurants in some locations in the US using the SDO_DISTANCE function. With this function, users can get the distances of their warehouses to their customers, find the distances of the nearest banks from store locations, and calculate how far the schools are from residential houses. You may refer to this link for more details about this function in the Oracle Database.

Creating the database connection

First, you should connect to the database containing the metadata of the spatial datasets. If you’re connecting to an Oracle database, you may refer to this link. If you’re connecting to an autonomous database, you may refer to this link for more details.

Once you have created the connection to the database, you should be able to see the database when you create a dataset.

For our use case, I’m using an autonomous database connection which I named OA_HERE_MAPS. Select the database connection to open the database and create dataset.

Creating the dataset

In creating a dataset from a database connection, we have the option to manually query the database tables or to first select the database schema to find the data tables that we want to use as data source. For our use case, I’m using the data tables from the OA_HERE_MAPS schema.

Find the data tables from the database and drag or double-click the data table to open. For our use case, I’m using the hotel POI data table found in the OA_HERE_MAPS schema.

Go to the data table and click on Edit Definition found on the upper right.

Editing the dataset definition using SQL editor

In the Edit Definition page, the default view shows the columns selector interface that enables users to select, drag and delete the columns that they want to use for the dataset. For our use case, we need to use custom SQL functions, so we select Enter SQL which will show the SQL editor containing the default statement of the current data table selection.

Once in the SQL editor, we can enter the SQL statement that selects the columns from the data tables and computes the spatial calculation as well. Here we will compute distance on point geometries of the hotels and restaurants in kilometers (KM) using SDO_DISTANCE.

SELECT t1.POI_ID as HOTEL_PO_ID, t1.NAME as HOTEL, t2.POI_ID as RESTO_PO_ID, t2.NAME as RESTO,

SDO_GEOM.SDO_DISTANCE(t1.GEOMETRY, t2.GEOMETRY, 0.005, 'unit=KM') distance_KM

FROM OA_HERE_MAPS.NTC_MAP_POI_HOTEL t1, OA_HERE_MAPS.NTC_MAP_POI_RESTAURANT t2

WHERE t1.ISO_COUNTRY_CODE='USA' AND t2.ISO_COUNTRY_CODE='USA' AND t2.CUISINE_ID=14

In our example, the output dataset is filtered using WHERE clause to limit the query within US for both data tables, and to Thai restaurants only based on CUISINE_ID for the restaurants POI data table.

Click OK and edit the metadata as needed. In this case, we change the treatment for IDs from Measure to Attribute.

 Once done, name and save the dataset.

Finally, we can now create map visualizations using the created dataset. Below is an example of a map visualization showing the Thai restaurants within 1-kilometer distance from the selected hotels.

In this blog, we have learned how to calculate distances on point geometries by editing the definition of a data source using the SQL syntax for SDO_DISTANCE when creating the dataset from a database connection. This function can be used with other spatial functions in the Oracle Database for other use cases such as getting the area and perimeter of land parcels, finding the nearest ATM machines to office buildings, and calculating the length of the streets from one point to another.

Source: oracle.com