REST APIs, GPS Coordinates, Spatial, & GeoJSON in Oracle DB

I’m helping Chris work on a blog post series where he picks up Oracle Database and Python, and builds some cool demos/apps powered by REST APIs.

You can catch-up on what Chris has done already here on his blog.

Chris and I both live in the same town here in North Carolina. When looking at some of the local county’s open data portal data sets, this one looked interesting –

So, downloading this data (as CSV) and putting it into an Oracle Database as relational tables is pretty easy..I can simply drag and drop the CSV into SQL Developer Web, and it’ll give me a table.

What are we going to do with this data?

By ‘we’, I mean Chris. He’s building out his Python app with some popular libraries using ORDS, to show how easy it can be, for folks like him and YOU.

We quickly noticed that the RESTAURANTs data including their GPS coordinates via longitude and latitude numbers.

Show me stuff…close to ME.

I mean it’s cool I can pull up a map of every restaurant in the County…but how useful is a map with 3,700+ pins on it? No amount of zooming would help there.

We need a filter…what if I supplied a given point on the map, and asked the database for a list of the places that fell within say, 1 kilometer of that point?

We can do that with SQL!

with avg_scores as (
    select avg(score) as score, hsisid
    from food_inspections
    group by hsisid
)
Select a.name, a.address1, a.city, a.x, a.y, b.score
from restaurants a, avg_scores b
where sdo_within_distance(hsisid_to_geom(a.hsisid), hsisid_to_geom('1'), 'distance=1000')='TRUE' 
      and a.facilitytype = 'Restaurant'
      and a.hsisid = b.hsisid
order by b.score desc;

The key bit of SQL is this predicate –

where sdo_within_distance(hsisid_to_geom(a.hsisid), hsisid_to_geom('1'), 'distance=1000')='TRUE'

Breaking down Jayant’s code

First I should fess up. I didn’t write this code. I adopted it from this blog post, from one of our experts, Jayant Sharma.

In his post he shows how to ask these ‘how far away are points X & Y’ using Spatial features in the Oracle Database, exactly what I needed!

Step 1: PL/SQL function that takes in a starting point & returns an SDO_GEOMETRY object

I took Jayant’s code as-is, and I might do things differently, I don’t see a need to do an EXECUTE IMMEDIATE vs a SELECT INTO directly…but that’s a different topic for another post.

The function is simple, take in an X and Y and return a SDO_GEOMETRY object. This SDO_GEOMETRY object can then be used in all the useful geometry functions to calculate things like proximity and distance.

X & Y columns are brought up by the RESTAURANT’s primary key, the HSISID string.

Step 2: INSERT a row into USER_SDO_GEOM_METADATA

We’re going to be creating a spatial index in just a moment, but that index needs to know what it’s indexing for it to be useful.

INSERT INTO USER_SDO_GEOM_METADATA VALUES (
    'RESTAURANTS',
    'WAKE.HSISID_TO_GEOM(HSISID)',
    SDO_DIM_ARRAY(
        SDO_DIM_ELEMENT(
            'Longitude', - 180, 180, 0.5
            ),
        SDO_DIM_ELEMENT(
            'Latitude', - 90, 90, 0.5
            )
        ),
    4326
    );

You can’t normally update dictionary views, this is the first exception I’ve come across. This view keeps track of tables with spatial data, and describes the data. I’m referencing two things here:

• the table, RESTAURANTS
• the column with the data, in this case the FUNCTION called to provide this data

Further note that’s I’ve included my SCHEMA for the INDEX and I’ve uppercased the column in the call to the function. The spatial functions won’t work if it’s lowercase…

After that we have the dimension information, the properties of Longitude and Latitude including their boundaries and tolerances (0.5 = within half a meter).

And finally we have the ‘4326’ thing again which tells the database, it’s the 2D GPS stuff again.

Step 3: Creating a function-based SPATIAL INDEX

Indexes do what indexes do, they provide us more efficient access to our data, in this case our spatial data.

create index sch2geom_sidx on restaurants(hsisid_to_geom(hsisid)) indextype is mdsys.spatial_index_v2;

What the what is … spatial_index_v2? It’s a more modern type of spatial index, with the main benefit is it doesn’t need partitioned, even if your underlying table data is. (Docs) The docs also basically say just always use this going forward.

Now we can do our ‘fun’ stuff!

If I have a point of interest, I can ask the database for a list of places that are within a certain distance.

Let’s say, I want to look for things within walking distance, and I’ll call that a thousand meters ~ .6 miles.

I can do that with this SQL –

SELECT
    A.NAME,
    A.ADDRESS1,
    A.CITY,
    A.X,
    A.Y
FROM
    RESTAURANTS A
WHERE
    SDO_WITHIN_DISTANCE(
        HSISID_TO_GEOM(A.HSISID),
        HSISID_TO_GEOM('1'), 'distance=1000') = 'TRUE'
        )

SDO_WITHIN_DISTANCE returns a pseudo-BOOLEAN, a string, ‘TRUE’ if the parameters are met. In this case I have two points and a distance. The distance is also represented as as string, in this case ‘distance=1000.’ In the SDO libraries, the default unit of distance is meters.

These are detailed in SDO_UNITS_OF_MEASURE –

HSISID = 1 pulls up row that represents my house.

REST API returning GeoJSON

So I don’t want to return just normal string attributes in my response for X and Y or LAT and LONG. I want to use GeoJSON.

Here’s that simple call to do the first conversion –

sdo_geometry(2001,4326,sdo_point_type(a.X,a.Y, null), null, null) GEO,

And then the second call to get GEOJSON

SDO_UTIL.TO_GEOJSON(sdo_geometry(2001,4326,sdo_point_type(a.X,a.Y, null), null, null)) "{}GEO"

There are two tricks here.

Trick 1: the template and the bind variable.

ORDs sees this template definition:

restaurants/:distance

And when the GET request comes in, the string that comes after restaurants/ is bound to a bind variable, :distance.

Trick 2: telling ORDS the payload is already JSON

Adding an alias “{}GEO”, tells ORDS that this column is JSON.

Trick 3: Not a trick, just some SQL to make my call to two tables easier

I’m also using an inline view via a WITH, and grabbing the average rating score and the number of times the restaurant has been expected.

The SQL for my REST API

-- get the avg score and number of reviews by restaurant 
with avg_scores as (
    select ROUND(avg(score)+0.005, 2) as score, count(*) as num_reviews, hsisid
    from food_inspections
    group by hsisid
)
Select a.name, a.address1, a.city, SDO_UTIL.TO_GEOJSON(sdo_geometry(2001,4326,sdo_point_type(a.X,a.Y, null), null, null)) "{}GEO", b.score, b.num_reviews
from restaurants a, avg_scores b
where sdo_within_distance(hsisid_to_geom(a.hsisid), hsisid_to_geom('1'), ('distance=' ||:distance))='TRUE' 
      and a.facilitytype = 'Restaurant'
      and a.hsisid = b.hsisid
order by b.score desc

And then what comes back is this JSON, with a GeoJSON attribute called “geo” :

{
	"items": [{
			"name": "KOI RAMEN",
			"address1": "919 N HARRISON AVE",
			"city": "CARY",
			"geo": {
				"type": "Point",
				"coordinates": [
					-78.78340203,
					35.80068085
				]
			},
			"score": 98.38,
			"num_reviews": 4
		},
		{
			"name": "ALEX & TERESA'S PIZZA & TRATTORIA",
			"address1": "941 N HARRISON AVE",
			"city": "CARY",
			"geo": {
				"type": "Point",
				"coordinates": [
					-78.78451708,
					35.80143518
				]
			},
			"score": 96.61,
			"num_reviews": 10
		},
		{
			"name": "NEW CHINA",
			"address1": "949 N HARRISON AVE",
			"city": "CARY",
			"geo": {
				"type": "Point",
				"coordinates": [
					-78.78451708,
					35.80143518
				]
			},
			"score": 96.47,
			"num_reviews": 14
		}
	],
	"hasMore": false,
	"limit": 0,
	"offset": 0,
	"count": 3,
...
}]}

Resources & Acknowledgements

I highly recommend this Oracle LiveLab for learning Spatial and SDO_GEOMETRY basics.

If you’re curious about the Oracle Converged Database model, then this is a great 5 minute video introduction:

I also want to give a shout-out to our Oracle Spatial PM, David Lapp, who really helped me get my head around this feature in the database.