Transcript

A common requirement in a database using latitude and longitude geolocations is querying for locations within a certain radius or distance from another location. There are multiple approaches for easily managing such queries. You can use the Postgres earthdistance module with either points or cubes, or you can use the third-party PostGIS extension. In this episode, we're going to focus on using the earthdistance module with point data types.

The earthdistance module is an officially supported Postgres extension that must be manually enabled. It's also important to note that even when using it with point data types, it is dependent upon the Postgres cube extension. I'm going to quickly install both.

create extension if not exists cube;
create extension if not exists earthdistance;

For this example, we're going to be working with an addresses table, which has a geolocation column of type point, and a few entries.

select * from addresses;

We're going to look at two examples for how to use the earthdistance module. The first is determining the distance between locations. The second is finding locations within a certain radius of another location.

Let's start with calculating the distance between two locations. I'm going to use the "\e" metacommand to use vim as my query editor.

To calculate the distance between the Hashrocket Jacksonville office and all of the other addresses in our table, we're going to select from addresses and do a lateral join to get the Hashrocket Jacksonville address as a location to compare the other locations to. With that set up, we can complete our select statement to determine the distance between the Hashrocket Jacksonville geolocation and our other geolocations, using the "<@>" operator provided to us by the earthdistance module. This operator expects to be given two points to compare, and returns the distance in miles between the two points.

select name, (a.geolocation<@>hr_jax.geolocation) as distance
from addresses a,
lateral (select id, geolocation from addresses where name = 'Hashrocket JAX') as hr_jax
where a.id <> hr_jax.id
order by distance;

We can see from our output that we now have the distance of each address from the Hashrocket Jacksonville office.

To only find locations within a certain distance of of Jacksonville office, we can make a quick modification to our existing query. Since we already know how to get the distance between our two points, we can reuse the same logic passing it now as part of our where clause, and using it in an inequality to check that the distance between the locations is less than a specific value, say 100 miles.

select name, (a.geolocation<@>hr_jax.geolocation) as distance
from addresses a,
lateral (select geolocation, id from addresses where name = 'Hashrocket JAX') as hr_jax
where a.id <> hr_jax.id
and (a.geolocation<@>hr_jax.geolocation) < 100
order by distance;

With that change, we can see from our output that we are now only including addresses that are less than 100 miles from our Hashrocket Jacksonville address.

There are some things of note with the earthdistance module and with using the module with points.

The first is that the distance calculations for points break down at the poles and at the 180th meridian. This can be a problem depending on your use case. If you're trying to find nearby pizza joints for a user, odds are you aren't dealing with the north and south poles or the northeasternmost tip of Russia. However, if you don't want to open yourself up to potential problems, then it is recommended that you use the cube based earthdistance calculations.

Second, it's important to remember that the point values for geolocations need to be saved as "longitude, latitude."

Finally, the Postgres earthdistance module assumes that the Earth is a perfect sphere, which is not completely accurate. If you need extreme accuracy when working with geolocations, Postgres recommends that you consider the PostGIS extension.

Thanks for watching!

Setup

create table addresses (
  id serial primary key,
  name varchar,
  street_address varchar,
  city varchar,
  state varchar,
  zip varchar,
  geolocation point
);

insert into addresses (name, street_address, city, state, zip, geolocation)
values ('Hashrocket JAX', '320 1st St N', 'Jacksonville Beach', 'FL', '32250', '-81.3927381,30.2918842'),
('Hashrocket Chicago', '661 W Lake St', 'Chicago', 'IL', '60661', '-87.6473133,41.8853881'),
('Satchel''s Pizza', '1800 NE 23rd Ave', 'Gainesville', 'FL', '32609', '-82.3018702,29.6739466'),
('V Pizza', '528 1st St N', 'Jacksonville Beach', 'FL', '32250', '-81.3905175,30.2938423'),
('Artichoke Pizza', '321 E 14th St', 'New York', 'NY', '10003', '-73.9860525,40.7321652'),
('Giordano''s', '130 E Randolph St', 'Chicago', 'IL', '60601', '-87.6252984,41.8850284');