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 is dependent upon the Postgres cube extension, so 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 latitude and longitude columns of type float8, or double precision, and a few entries.

\d addresses

select * from addresses;

We're going to look at two examples for how to use the cube-based calculations in 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.

For determining the distance between two latitude/longitude coordinates, we're going to use the "earth_distance" function. This function expects to receive two three-coordinate locations to use to compare distance, and returns the distance in meters between the locations. Since we only have two coordinates, latitude and longitude, we need to convert our data. We can do this with the helper method "ll_to_earth", passing first latitude, then longitude as our arguments.

select name, earth_distance(
  ll_to_earth(a.latitude, a.longitude),
  ll_to_earth(hr_jax.latitude, hr_jax.longitude)
) as distance
from addresses a,
lateral (
  select id, latitude, longitude 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 in meters of each address from the Hashrocket Jacksonville office. If we wanted to instead see it in miles, we can do a quick division on our earth_distance, using the conversion value for meters to miles, which is 1609.344.

select name, earth_distance(
  ll_to_earth(a.latitude, a.longitude),
  ll_to_earth(hr_jax.latitude, hr_jax.longitude)
) / 1609.344 as distance
from addresses a,
lateral (
  select id, latitude, longitude from addresses where name = 'Hashrocket JAX'
) as hr_jax
where a.id <> hr_jax.id
order by distance;

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 earth distance between our two locations, 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 is less than a specific value, say 100 miles, meaning we can just move the decimal point from our earlier conversion.

select name, earth_distance(
  ll_to_earth(a.latitude, a.longitude),
  ll_to_earth(hr_jax.latitude, hr_jax.longitude)
) / 1609.344 as distance
from addresses a,
lateral (
  select id, latitude, longitude from addresses where name = 'Hashrocket JAX'
) as hr_jax
where a.id <> hr_jax.id
and earth_distance(
  ll_to_earth(a.latitude, a.longitude),
  ll_to_earth(hr_jax.latitude, hr_jax.longitude)
) < 160934.4
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.

It's important to remember that 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,
  latitude float8,
  longitude float8
);

insert into addresses (name, street_address, city, state, zip, longitude, latitude)
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');