By Yuli Vasiliev | September 2020
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.
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).
In this example, the idea is that anyone who enters the specified section of the street is likely to pass near the restaurant.
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.
To follow along with the example in this article, you’ll need the following software:
As a Python coder, you can take advantage of a developer-friendly framework from Telegram.
The general idea is as follows. Using the Telegram Bot API, you create a bot in Python, which will receive real-time location data from those who have agreed to share their location with the bot. The bot will check the received data against the database. That is it.
To make a long story short, you need to do the following two tasks:
To accomplish the first task, you can use the app.py script included with this article. Before that, make sure to read the instructions provided in the app.txt file.
For the second task, read the location_share.txt file to share the location of your smartphone with the bot. Once you’re done with it, you can start testing the app by entering and leaving the area you have defined in the database.
Note, however, that you will receive a single message when the app determines you’re in the specified area. If you look at the code of the app.py script, you’ll notice that it is tuned to send a single message to a user per day. You can experiment with the code and change it if you want.
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:
You’ll also need to download and extract the zip file for this article. The zip includes the following files and content:
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.
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:
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!")
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):
print('You are near %s located at %s' %(rslt, rslt))
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
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.
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