Transcript

PostGIS is a third party extension for Postgres, and can be added by using the Postgres "create extension" command.

create extension if not exists postgis;

When working with geolocations, where you're typically dealing with distance measurements, PostGIS recommends using the "geography" column type. This type is slower than the "geometry" type and has fewer functions available, but it doesn't require us to have knowledge of projections and planar coordinate systems.

Let's add a geography column to a table and see how we can work with the data.

In this example, we have a table called addresses, with a few existing entries.

\d addresses

select * from addresses;

Creating the Column

We're going to use the alter table command to add a column name geolocation of type geography. The geography type can receive up to two arguments.

The first argument is an optional type modifier, which can be used to restrict the kinds of shapes and dimensions allowed for the column. Since we are going to be using latitude and longitude coordinates, we can pass point as our type modifier.

The second argument is an optional spatial resource identifier, or SRID. If the SRID option is omitted, the geography column will default to a value of 4326, which is the SRID for WGS 84, the World Geodetic System of 1984, and the standard for the Global Positioning System.

alter table addresses add column geolocation geography(point);

By inspecting our table again, we can see the new geography column added, with 4326 as the SRID option.

\d addresses

Inserting Data

With our new geography column in place, we now need to start adding data. There are a few ways we can do this.

The first is to use strings, so we'll update addresses and set geolocation to a string in a specific format, which PostGIS refers to as a Well-Known Text representation, or WKT. In this case, our WKT starts with the type of data we're inserting, which is point, followed by parentheses and the two coordinate values. PostGIS points follow the same rules as Postgres points when it comes to latitude and longitude coordinates; they both expect longitude first, followed by latitude.

update addresses set geolocation = 'point(30 -81)' where id = 1;

We can now read from our table, looking for the row we just tried to update, and see that there is a value for the geolocation.

select * from addresses where id = 1;

The second way to update our geolocation column is to use the PostGIS ST_MakePoint function. This function accepts as arguments our longitude and latitude coordinates, again with longitude first, and saves them as a point in the database.

update addresses set geolocation = ST_MakePoint(longitude, latitude);

A thing to note on the ST_MakePoint function is that it doesn't automatically know the SRID we're looking for. In our last example, because we're inserting into the database and the column has the SRID set, we're covered.

However, if we were trying to compare or select an arbitrary point value with ST_MakePoint, the SRID would be unknown.

select ST_MakePoint(-80, 30);

In such cases, we would need to wrap our ST_MakePoint function up in an ST_SetSRID function, and explicitly tell PostGIS the SRID we're intending to use.

select ST_SetSRID(ST_MakePoint(-80, 30), 4326);

Calculating Distances

With our geolocation column populated, we can now start comparing the distances between our addresses. We're going to look at two distance queries. The first is determining the distance between addresses. The second is finding addresses within a certain radius of another address.

Let's start with calculating the distance between two addresses. 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 "ST_Distance" function provided to us by the PostGIS module. This function expects to be given two points to compare, and returns the distance in meters between the two points.

select name, ST_Distance(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 addresses within a certain distance of the 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 addresses is less than a specific value, say 1 kilometer, or 1000 meters.

select name, ST_Distance(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
and ST_Distance(a.geolocation, hr_jax.geolocation) < 1000
order by distance;

With that change, we can see from our output that we are now only including addresses that are up to a kilometer from our Hashrocket Jacksonville address.

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');