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:
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;