More and more people today use applications that interact with location services available on smartphones and tablets. With the help of such an application, you may, for example, track a specific taxi heading toward you, find the nearest drugstore or cafe, or just locate friends in the area.

Usually a location-based application retrieves not only the whereabouts of a specific object but also some of its nonspatial attributes. For example, you might want to know not only the current location of the taxi assigned to your order but also some information about the driver. If you’re requesting the nearest drugstores, you are probably looking for a specific medicine and want to know its availability and cost in each of these drugstores—in addition to the location points on the map.

Oracle Database provides a foundation that is well suited for building location-based applications, offering the facilities for storing, manipulating, and analyzing spatial data within the database. By following the instructions in this article, you’ll build the database back end for an example application designed to notify users when they are near a certain pizza restaurant. You will also see how to interact with this database back end from within Python code.

A location-enabled app example

It would be nice to be notified when you get close to your favorite pizza restaurant. It can be especially convenient when you prefer a certain restaurant chain. You may not be aware of the location of each restaurant in the chain in your city, but you might use a special app on your smartphone that does know them all and will notify you when you get close to one of them.

Perhaps the main challenge for such an app is that the state of being close to a place is hard to define in an urban setting. It is fairly clear that being a mile from a place does not automatically mean you’ll need to cover exactly 1 mile to get to this place. So, the method that identifies whether two spatial objects are within some specified distance from each other is not the best solution here. Instead, you may need to overlay an irregularly shaped polygon on the map to delineate an area, being in which could be considered close to the place of interest (Figure 1).

Figure 1. An example of an area outlined on a map

In this example, the idea is that anyone who enters the specified section of the street is likely to pass near the restaurant.

How the example app can be implemented

Technically, the app described in the previous section can be organized as follows. The app relies on the GPS sensor built into a smartphone. If the user has agreed to share geolocation with the app, the smartphone can be tuned to send location data to the app for processing. The app checks the user location against the database and then—if the user is in the area found in the database—sends a notification back to the user’s smartphone.

Thus, as Figure 2 shows, the app consists of three parts: the provider of GPS data (a smartphone), the processing solution (Python scripts), and the database back end (an Oracle database). The implementation details are covered further in the article.

Figure 2. A graphical depiction of how the sample app can be implemented

Getting ready

To follow along with the example in this article, you’ll need the following software:

• Python 3.5 or later
• cx_Oracle Python module 8 or later
• Oracle Database 12c or later

Perhaps the easiest way to install the cx_Oracle Python module is via pip:

pip install cx_Oracle

If you don’t have access to Oracle Database yet, you can obtain it in one of the following ways:

• Download and install a prebuilt virtual machine for Oracle VM VirtualBox, which contains preconfigured Oracle Database software.
• Download Oracle Database software from Oracle Technology Network (OTN) and install it in your system.
• Choose one of the Oracle Database Cloud services that provide access to Oracle Database in the cloud.
• Deploy Oracle Database as a Docker container, either in the cloud or on-premises.

You’ll also need to download and extract the zip file for this article. The zip includes the following files and content:

• db_connect_string.py, which contains credentials to connect to the database (you’ll need to modify it, replacing the credential values with your actual credentials)
• db_setup.py, which creates the database tables needed for the example app
• insert_area.py, which inserts a row into the Places table, representing an area on the map
• check_location.py, which issues a query against the Places table to check if a certain location is within the area inserted by insert_area.py
• app.py, which allows you to test the example app by using a smartphone
• app.txt, which provides the steps you must take before you can run the app.py script
• location_share.txt, which provides the steps you must take to use your smartphone as a location data provider for the app.py script

Start with checking db_connect_string.py. You must modify it so it contains your actual Oracle database credentials. Then run the db_setup.py script to create the database tables needed for the example application. The usage of the other files included in the zip and shown in the above list is covered throughout the rest of the article.

Defining an area of interest

In Figure 1, you saw how an area of interest can be marked on a map. But how can you transfer this information to the database? To be more specific, how can the area enclosed by a polygon on a map be represented as a row in a database table, so you can use SQL to check whether a certain location is within this area?

In the context of this article, a location is a geographical reference defined by a pair of coordinates: longitude and latitude.

In a nutshell, to store information about a spatial object (like a polygon area) in an Oracle database, you’ll need a table with a column of type SDO_GEOMETRY. This type is specifically designed to store the spatial attributes of an object. In this same table, you may also need to define columns of common types, such as VARCHAR2 and NUMBER, to hold nonspatial attributes of spatial objects.

An example of such a table is Places, which you should have created in your database after the execution of the db_setup.py script. The Places table uses the shape column of type SDO_GEOMETRY to store the polygon’s coordinates that define an area on a map. The Places table also has other columns—such as name, address, and place_id—for storing nonspatial attributes of the area.

So, by inserting a row in the Places table, you create an object in the database, which represents a polygon area. You can do it right now by executing the insert_area.py script included with this article. The code found in this script is provided below with explanatory comments.

You start by importing the cx_Oracle module and the db_connect_string script that contains your Oracle database credentials:

import db_connect_string
import cx_Oracle

Then you connect to the database and create a cursor object that is used in cx_Oracle to execute statements and fetch their results, if any. Because you will perform an insert operation, you need to take care about committing the changes in the database. Here, you set the autocommit mode on to ensure that the insert statement is committed as soon as it has been executed.

connection = cx_Oracle.connect(db_connect_string.getConnectString())
cursor = connection.cursor()
connection.autocommit = True

SDO_GEOMETRY is an Oracle object type. And some fields of SDO_GEOMETRY, such as SDO_ELEM_INFO and SDO_ORDINATES, are object types too. The cx_Oracle module uses the connection.gettype() method to look up object types:

typeObj = connection.gettype("MDSYS.SDO_GEOMETRY")
elementInfoTypeObj = connection.gettype("MDSYS.SDO_ELEM_INFO_ARRAY")
ordinateTypeObj = connection.gettype("MDSYS.SDO_ORDINATE_ARRAY")

The object types returned by connection.gettype() can then be used to create objects to be bound to the cursor for interaction with the database. In this example, you implement it in a separate function:

def CreateGeometryPolygon(*coords):
    geometry = typeObj.newobject()
    geometry.SDO_GTYPE = 2003
    geometry.SDO_SRID = 8307
    geometry.SDO_ELEM_INFO = elementInfoTypeObj.newobject()
    geometry.SDO_ELEM_INFO.extend([1, 1003, 1])
    geometry.SDO_ORDINATES = ordinateTypeObj.newobject()
    geometry.SDO_ORDINATES.extend(coords)
    return geometry

In the line below, you invoke the above function to construct a Python object to be used as a bind variable in the insert statement. This example uses coordinates (fake ones) that describe a quadrilateral similar to the one shown in Figure 1.

pizzaRestaurantCoords = CreateGeometryPolygon(46.080453, 39.994143, 46.079943, 39.994057, 46.077989, 40.000344, 46.078774, 40.001288, 46.080453, 39.994143)

Then you create the entire list of parameters to be passed to the insert statement:

geodata = [1, 'Pizza restaurant', '21 Cherry St', pizzaRestaurantCoords]

Now you’re ready to execute the statement to define an area in the database:

cursor.execute('insert into places values (:place_id, :name, :address, :ordinates)', geodata)
print("The area defined!")

Checking if a location is in the defined area

Now that you have defined an area in the database, you might want to check whether a certain location is within this area. You can do a quick test by using the check_location.py script that accompanies the article. Let’s briefly look through the key parts of the script.

For simplicity, the location coordinates are hard-coded in this example. (The location described by the coordinates provided below is within the area defined in the previous section.)

location = [46.078636, 40.000623]

Then you issue a SQL query against the Places table, using the SDO_RELATE() function to identify if any spatial interaction occurs between the specified location and the geometries stored in the shape column of the table. For simplicity, in this example, you don’t create a new object of the SDO_GEOMETRY type to be passed as a parameter to the statement. Instead, you bind the coordinates directly to the SDO_GEOMETRY instance.

cursor.execute("""
  SELECT c.name, c.address
  FROM places c
  WHERE SDO_RELATE(c.shape,
            SDO_GEOMETRY(1, 8307, NULL,
              SDO_ELEM_INFO_ARRAY(1,1,1),
              SDO_ORDINATE_ARRAY(:lat,:lon)),
              'mask=anyinteract'
) = 'TRUE' 
""", location)
rslt = cursor.fetchall()

If a spatial interaction is determined, you extract the nonspatial attributes of the area with whose geometry the location interacts (located within, in this example):

if rslt:
print('You are near %s located at %s' %(rslt[0][0], rslt[0][1]))

If you have followed the instructions in this article so far, you should see the following output:

You are near Pizza restaurant located at 21 Cherry St

A more realistic test

To make the example more realistic, it would be good to receive the coordinates of a moving object, getting a new pair of coordinates as the object changes its position. Perhaps the easiest way to conduct such a test is with the help of a smartphone. The point is that smartphones have GPS sensors and can be tuned to share the location data coming from them. In this section, we’ll discuss how you might conduct this test.

To start, you need to define a new area in the database. This area should be near your current location so you can freely enter and leave it. You can use Google Maps to figure out such an area and then find its coordinates. To create a row representing your area in the Places table, you can reuse the insert_area.py script after modifying the area data in it. Once you’re done with it, you are ready to build the code that will dynamically check locations against the newly defined area.

Since the focus of this article is on how you can use Oracle Database spatial features from Python rather than the development of mobile apps, it would be appropriate to take advantage of an existing, well-known app that allows you to obtain location data from a smartphone and then send it to a solution you implement in Python. Telegram, a popular messaging platform, can be tuned to do just that.

Conclusion

Location-based data is becoming more and more prevalent (and necessary) among application developers. I hope this tutorial can help developers take advantage of the native spatial functionality in Oracle Database and apply it for mobile use.

Illustration: Wes Rowell; Figure: Arianna Pulcherelli