Thread: Missing Earth Distance Functions Under Debian

Hi All. I was wondering whether someone could help me explain a weird
PostgreSQL error I've encountered.

I have two machines running Debian Sarge stable, the first has been
operating as a
database server for some time and the other I have just set up identically
to run as a backup database. Both machines used the default database setup
option during installation.

Some of my scripts rely on the earth distance functions contained in the
'postgresql-contrib' package, but for some reason the backup PostgreSQL
database does not recognise these functions. Eg. my latitude/longitude to
distance converter, which runs fine on the main machine, generates this
error message:

"ERROR:  function ll_to_earth(numeric, numeric) does not exist
HINT:  No function matches the given name and argument types. You may need
to add explicit type casts."

I've tried reinstalling the package in question but it had no effect. Does
anyone have a theory why it would run on one and not the other and/or how I
can get the earth distance functions working?

Regards,

       -Duncan

"Duncan McDonald" <duncan@gpats.com.au> writes:
> "ERROR:  function ll_to_earth(numeric, numeric) does not exist
> HINT:  No function matches the given name and argument types. You may need
> to add explicit type casts."

> I've tried reinstalling the package in question but it had no effect.

I think you forgot to execute the earthdistance.sql script to define
the functions within the current database.

            regards, tom lane

Hi Tom,

Thanks for the reply.

No I didn't run the earthdistance.sql script on the backup database, is this
included with the standard PostgreSQL package? If not, would you mind
letting me know how/where to obtain it?

I'm relatively new to PostgreSQL administration so I apologise in advance if
this is an obvious question.

Regards,

       -Duncan

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Duncan McDonald" <duncan@gpats.com.au>
Cc: <pgsql-admin@postgresql.org>
Sent: Tuesday, October 17, 2006 11:37 PM
Subject: Re: [ADMIN] Missing Earth Distance Functions Under Debian

...

> I think you forgot to execute the earthdistance.sql script to define
> the functions within the current database.
>
> regards, tom lane
>

Duncan McDonald wrote:
> Hi Tom,
>
> Thanks for the reply.
>
> No I didn't run the earthdistance.sql script on the backup database, is this
> included with the standard PostgreSQL package? If not, would you mind
> letting me know how/where to obtain it?
>
> I'm relatively new to PostgreSQL administration so I apologise in advance if
> this is an obvious question.
>
> Regards,
>
>        -Duncan

I think that it is in the postgresql-contrib-8.1 deb package available
via aptitude (or whichever package manager you are using).

Duncan McDonald wrote:
> No I didn't run the earthdistance.sql script on the backup database,
> is this included with the standard PostgreSQL package? If not, would
> you mind letting me know how/where to obtain it?


http://packages.debian.org/cgi-bin/search_contents.pl?word=earthdistance.sql&searchmode=searchfiles&case=insensitive&version=stable&arch=i386

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Hi Peter,

That worked like a charm. Thanks for the help!

Regards,

       -Duncan

----- Original Message -----
From: "Peter Eisentraut" <peter_e@gmx.net>
To: <pgsql-admin@postgresql.org>
Cc: "Duncan McDonald" <duncan@gpats.com.au>
Sent: Friday, October 20, 2006 1:09 AM
Subject: Re: [ADMIN] Missing Earth Distance Functions Under Debian


> Duncan McDonald wrote:
>> No I didn't run the earthdistance.sql script on the backup database,
>> is this included with the standard PostgreSQL package? If not, would
>> you mind letting me know how/where to obtain it?
>
>
http://packages.debian.org/cgi-bin/search_contents.pl?word=earthdistance.sql&searchmode=searchfiles&case=insensitive&version=stable&arch=i386
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
>