September 24, 2013

Postgres and earthdistance extension

First of all, let me describe how one can install extensions for your Postgresql.

It depends whether you have version 8.4+ or 9.1+ installed.

In following sections I will describe procedure related to Debian based OSes.

First of all, you have to install contrib packages for corresponding version:

root@debian:/home/web# apt-cache search postgres | grep contrib
postgresql-contrib - additional facilities for PostgreSQL (supported version)
postgresql-contrib-8.4 - additional facilities for PostgreSQL
postgresql-contrib-9.1 - additional facilities for PostgreSQL

with command:

apt-get install postgresql-contrib postgresql-contrib-x.y

After that, restart postgresql daemon:

/etc/init.d/postgresql restart

Change to the database owner account, e.g.:

su postgres

Change to the contrib modules’ directory:

cd /usr/share/postgresql/8.4/contrib/

Now, there are 2 different way how you could install extensions for your database.

1. Postgres-8.4.x

Load the SQL files for needed extension using command:

psql -U user_name -d database_name -f module_name.sql

for example,

psql -U postgres -d your_database_name -f cube.sql
psql -U postgres -d your_database_name -f earthdistance.sql

2. Postgres-9.1.x

Extensions are stored under /usr/share/postgresql/9.1/extension directory,

so you need to cd to it:

cd /usr/share/postgresql/9.1/extension

and then launch psql.

In opened database console run this query:

CREATE EXTENSION "cube"; -- you will not be able install "earthdistance" w/o "cube" extension
CREATE EXTENSION "earthdistance"; --or any other extension you need

That is all — you can use installed extension.

Okay, now lets proceed to the main topic of the post.

What is earthdistance?

It is a simplified solution to make calculations over spatial coordinates (latitude, longitude), calculate distance between two points in the Earth.

In case if you do not want install and learn PostGIS extension for the Postgres DB, although that extension provides much more functionality, you can go with earthdistance.

In earthdistance the Earth is treated as an ideal sphere and if it works for you — you don’t have too precise calculations -, then you can earthdistance.

Otherwise you definitely should take a look at PostGIS.

So, here is the main point — what the SQL should look like when you want to search location of some subject based on its coordinates?

Just use such expression in WHERE part of you SQL (for example):

... WHERE earth_box(ll_to_earth({longitude_float_value}, {latitude_float_value}), {radius_in_meters}) @> ll_to_earth(some_table.latitude, some_table.longitude)

of course values in curly braces should be just numbers, no need to put { and } outside.