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.

PHP-5.4 Debian – no suhosin, xcache, xdebug extensions

Today I have upgraded my Debian OS 6.0 (configured with apt-pinning).
Among the updates when called the dist-upgrade rolled a number of modules installed for php5. PHP was updated as well, actually up to version 5.4.0-2. But … then began weird problem – any local site did not work! In the logs were strange errors for, say, a website based on the framework, YII there was a message:

 [Sun Mar 25 13:32:51 2012] [error] [client 127.0.0.1] PHP Fatal error: Call to undefined function (null) () in / home / web / ccc / frontend / index.php on line 4

And on that line 4 was nothing special, except call to dirname (__FILE__)!
Google-searching on the Internet for the similar error I did not find anything. Posted a bug on php.net.
But it’s something nasty! Nothing at all works…
I began to dig further. I tried to run php scripts from the console, in order to check – was really dirname () removed? 🙂 And found a number of outstanding notifications warnings:

rodush @ rdushko :/ var / www $ php test.php
Failed loading / usr/lib/php5/20090626 + lfs / xdebug.so: / usr/lib/php5/20090626 + lfs / xdebug.so: cannot open shared object file: No such file or directory
PHP Warning: PHP Startup: Unable to load dynamic library ‘/ usr/lib/php5/20100525 + lfs / suhosin.so’ – / usr/lib/php5/20100525 + lfs / suhosin.so: cannot open shared object file: No such file or directory in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/ usr/lib/php5/20100525 + lfs / xhprof.so’ – / usr/lib/php5/20100525 + lfs / xhprof.so: cannot open shared object file: No such file or directory in Unknown on line 0

Although the directive dirname() in the test file worked fine, and included a file that I requested.
Having looked in the directory specified in the message, I discovered that those libraries are indeed absent!
So far decided not fooling: once the libraries will be available – I will get them updated, so I decided to remove the configs for php for that libraries.
Theese config ini files are located in the directory / etc/php5/conf.d /
just renamed all the config files, which have not come during update, to files with the suffix “old” (handy for the future 🙂 I think)

suhosin.ini.old xdebug.ini.old xcache.ini.old xhprof.ini.old

I tried to call test script from the console – no more warnings php! I tried to go to the sites in a browser, and – lo and behold! – It works!
Thats what the magic may happen in case if php complains that it is impossible to add extra modules library.
So, now you may know where to start if you faced similar strange issues.