Basic poly/point spatial queries using the Django ORM

Now that we have a couple sources of information in our database, we’ll look at mashing up the data.

If I need to determine all of the campgrounds available during a trip to Kansas, I can do that with the __within lookup.

>>> from tigerline.models import State
>>> from uscampgrounds.models import *
>>> ks = State.objects.get(usps_code='KS')
>>> kscamp = Campground.objects.filter(point__within=ks.mpoly)
>>> kscamp
[<Campground: 110 Mile Park - Pomona Reservoir>, <Campground: Arrow Rock - Melvern Reservoir>, <Campground: Atchison State Fishing Lake>, <Campground: Bloomington Pubilc Use Area - Clinton Lake>, <Campground: Canning Creek - Council Grove Lake>, <Campground: Carbolyn Park - Pomona Lake>, <Campground: Card Creek - Elk City Lake>, <Campground: Cedar Bluff State Park - North Shore>, <Campground: Cedar Bluff State Park - South Shore>, <Campground: Clinton State Park>, <Campground: Coeur DAlene - Melvern Lake>, <Campground: Curtis Creek - Milford Lake>, <Campground: Damsite - Fall River Lake>, <Campground: Elk City State Park>, <Campground: Ellis Lakeside City Campground>, <Campground: Fall River State Park>, <Campground: Farnum Creek - Milford Lake>, <Campground: French Creek Cove - Marion Lake>, <Campground: Geary State Fishing Lake>, <Campground: Gunn Park>, '...(remaining elements truncated)...']
>>> kscamp.count()

Or, I’ve got a campground and it’s state field in the database is blank – how do I find it? __contains to the rescue.

>>> milford = kscamp[11]
>>> State.objects.get(mpoly__contains=milford.point)
<State: Kansas>

It doesn’t matter as much with points, but if you’re doing __contains queries it’s worth knowing about __contains_properly. What’s the difference? There’s a city on the Texas/Arkansas border called “Texarkana” – so close that the two cities’ Police Departments share the same building – with a hallway where the “state line” was (at least this was the case when I was around 10.) Imagine we have a polygon of Texarkana and want to know which state it’s in. __contains will (first) return a MultipleObjectsReturned exception because it’s contained in two states. __contains_properly will return no results – no state contains ALL of that city.

If you’re planning a trip and absolutely don’t want to camp in Arizona (sorry Arizona, I’m not judging you but had to choose somebody!)

>>> Campground.objects.filter(point__disjoint=az.mpoly)
[<Campground: 110 Mile Park - Pomona Reservoir>, <Campground: A.W. Marion State Park>, <Campground: Ackley Creek County Park>, <Campground: Acorn Valley - Saylorville Lake>, <Campground: Ada Lake>, <Campground: Adair City Park>, <Campground: Addison Oaks County Park>, <Campground: Adrian City Park>, <Campground: Afton State Park>, <Campground: Airport Lake Park>, <Campground: Aitkin County Campground>, <Campground: Akeley City Campground>, <Campground: Akers>, <Campground: Alcona Park 1>, <Campground: Alexander Ramsey Park>, <Campground: Alexandria Lakes  State Rec Area>, <Campground: Algonac State Park>, <Campground: Alley Spring - Ozark National Scenic River>, <Campground: Aloha State Park>, <Campground: Alum Creek State Park>, '...(remaining elements truncated)...']
>>> Campground.objects.filter(point__disjoint=az.mpoly).count()

Now we’ve got every campground not in Arizona.

We can stack these queries, too. How about all campgrounds except Arizona and New Mexico (again, no judgment on the state, after all they host the Albuquerque Balloon Fiesta which is a fantastic event)

>>> nm = State.objects.get(usps_code='NM')
>>> Campground.objects.filter(point__disjoint=az.mpoly).filter(point__disjoint=nm.mpoly).count()

This syntax won’t work with a __within query though because stacking ANDs, so we need to use Q objects and OR like this.

>>> from django.db.models import Q
>>> in_ks = Q(point__within=ks.mpoly)
>>> in_mo = Q(point__within=mo.mpoly)
>>> Campground.objects.filter(in_ks | in_mo)
[<Campground: 110 Mile Park - Pomona Reservoir>, <Campground: Akers>, <Campground: Alley Spring - Ozark National Scenic River>, <Campground: Arrow Rock - Melvern Reservoir>, <Campground: Atchison State Fishing Lake>, <Campground: Aunts Creek>, <Campground: Babler Memorial State Park>, <Campground: Baxter>, <Campground: Beaver Creek>, <Campground: Bennett Spring State Park>, <Campground: Berry Bend - Harry S. Truman Lake>, <Campground: Big M>, <Campground: Big Spring - Ozark National Scenic River>, <Campground: Binder Park>, <Campground: Blue Springs County Campground>, <Campground: Branson City Campground>, <Campground: Bloomington Pubilc Use Area - Clinton Lake>, <Campground: Bucksaw - Harry S. Truman Lake>, <Campground: Campbell Point>, <Campground: Canning Creek - Council Grove Lake>, '...(remaining elements truncated)...']
>>> Campground.objects.filter(in_ks | in_mo).count()

There are certainly other types of spatial lookups, but these are the ones I use regularly for my neogeography projects.

If you’ve noticed we haven’t done anything with distance yet, good catch! We will eventually, but there are more foundations to lay first.

Leave a Reply

Your email address will not be published. Required fields are marked *