Transcript

The point data type is one of the Postgres geometric types, meant to represent a point on a two dimensional plane. It can be compared to X and Y coordinates on a graph. Points are the building blocks to all the other geometric types in Postgres.

Let's take a look at how the point data type works.

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

select * from addresses;

If we wanted to add something like a geolocation, represented by latitude and longitude coordinates for our addresses, we could use the point data type.

We'll start by altering our addresses table and adding the geolocation column, setting the type to point.

alter table addresses add column geolocation point;

With our geolocation columns in place, we now need to add some data. There are two ways we can insert data into a point column in Postgres. The first is to use a string with comma separated values. Let's find our Jacksonville Breach address and update its geolocation. It's important to note that when using the point data type for something like geolocations, the point expects to receive longitude first, followed by latitude. This is because the idea of longitude most closely follows the intuitive idea of an X-axis, and latitude the Y-axis.

update addresses
set geolocation = '-81.4,30.3'
where city = 'Jacksonville Beach';

We can now read from our table again and see that our Jacksonville Beach address now has a geolocation.

The second way to insert a point into our database is to use the Postgres point constructor. This constructor accepts two arguments, and saves them as the values for the point in the database. Here we're again providing longitude first, followed by latitude.

update addresses
set geolocation = point(-87.6, 41.9)
where city = 'Chicago';

Now when we read from the database, we can see that both of our rows have geolocations set. You'll notice, too, that our geolocations didn't get truncated in any way. The point data type gets saved in Postgres as two float8 values or two double precision floating numbers, occupying 8 bytes each. These types support a precision of up to 15 decimal digits.

Querying our point column is a little different from standard queries in Postgres. The most obvious example of this would be to try to find one of our addresses based on their exact point value.

select * from addresses where geolocation = '-87.6,41.9';

We can see from the output that Postgres is telling us that the operator for what we're doing, which is comparing point to an unknown data type, does not exist. We can try to cast our query to point, however you'll notice that we still get an error.

select * from addresses where geolocation = point '-87.6,41.9';

This time the error is telling us that the operator we're using to compare a point to a point does not exist.

To find a row using an exact point value in Postgres, we have to use a supported geometric function. In our case, we'll be using what Postgres defines as the "Same as?" function, which is a tilde followed by an equal sign.

select * from addresses where geolocation ~= '-87.6,41.9';

Using the "Same as?" operator, we can see that we are finally getting matches for our query. You'll also notice that we didn't have to use the point constructor for our query; Postgres was able to convert our query value for us.

Thanks for watching!

Setup

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

insert into addresses (street_address, city, state, zip)
values ('320 1st St N', 'Jacksonville Beach', 'FL', '32250'),
('661 W Lake St', 'Chicago', 'IL', '60661');

Resources

Geometry Functions in PostgreSQL