DataChomp

Chomping At The Bits

Radius Queries in Postgres

Today, I found myself in a horrible situation. I was heading down i-235 and I became stricken with an insatiable burrito urge. All the apps on my phone were borked and all I had was my trusty Postgres database. I needed to find a burritos shop and I needed to find one fast.

Where oh where to begin

Luckily, I happen to know the latitude and longitude of all my favorite burrito places in Oklahoma City. I quickly add them to my database:

--Hello Table!
CREATE TABLE burritoplaces
(id serial PRIMARY KEY,
establishment varchar(50) not null,
lat double precision,
lon double precision);  
--Hello Data!
INSERT INTO burritoplaces(establishment, lat,lon)
VALUES ('Verde Bueno Burrito', 35.484388,-97.505035)
,('Locos Lobos', 35.496025,-97.510185)
,('Captain Crustacean', 35.51468,-97.524347)
,('Double Stuffers Cafe', 35.523762,-97.508039)
,('Cafe Truncate of Watonga', 35.866413,-98.473206);

The data checks out:

-- all 5 entries
SELECT * FROM burritoplaces;

Perfect! All my favorite places are there but sadly, I can’t do geospatial math in my head. I can’t even really do it outside of my head so I’m going to have to get some help. Enter the earthdistance postgres extension and its helpful sibling ‘cube’:

--turn on the magic
CREATE EXTENSION cube;
CREATE EXTENSION earthdistance;

I’m becoming so weak… I need to wrap this up. Since I live in one of the four countries in the world that uses the Imperial system of measurement, I need to figure out how to do this whole thing in miles. Skimming over the earthdistance documentation, it looks like I just need to use the point commands. I whip up a query that is as tech tasty as some carne asada:

-- my location on i-235 is a very hungry (35.512363,-97.515678)
SELECT *, point(-97.515678, 35.512363) <@> point(lon, lat)::point AS burrito_distance
FROM burritoplaces
WHERE (point(-97.515678, 35.512363) <@> point(lon, lat)) < 10 --feel free to play this!
ORDER by burrito_distance;

This returns me everything that is less than 10 miles away and orders them by distance. I see that Captain Crustacian is a half mile away and they have amazing guacamole. Thank you Postgres, cube, and earthdistance. You have saved my life once again.

If I lived anywhere else in the world, I would likely use meters and write the query as such:

SELECT *, earth_distance(ll_to_earth(35.512363,-97.515678), ll_to_earth(lat, lon)) as burrito_distance
FROM burritoplaces
WHERE earth_box(ll_to_earth(35.512363,-97.515678), 4000) @> ll_to_earth(lat, lon)
ORDER by burrito_distance;

Comments