Re: haversine formula with postgreSQL

A bit out in left field,

Writing your own haversine in Postgres seems a bit like reinventing a wooden wheel when you gan get a free pneumatic one...

Any reason not to just install PostGIS & fully support geometries & projections in Postgres?

You can build the geometries provided to the functions on the fly from lat/lon coordinates stored as numerics in your SQL, so your DB structures don't even have to change if you don't want them to..

http://www.postgis.org/documentation/manual-1.4/ST_Distance_Sphere.html
http://www.postgis.org/documentation/manual-1.4/ST_Distance_Spheroid.html


HTH

  Brent Wood


Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> Scott Marlowe <scott.marlowe@gmail.com> 09/18/09 11:35 AM >>>
On Thu, Sep 17, 2009 at 1:16 PM, Jonathan <jharahush@gmail.com> wrote:
> Hi!
>
> I am looking at the PHP/MySQL Google Maps API store locator example
> here:
>
> http://code.google.com/apis/maps/articles/phpsqlsearch.html
>
> And I'm trying to get this to work with PostgreSQL instead of MySQL.
>
> I've (slightly) modified the haversine formula part of my PHP script
> but I keep getting this error:
>
> Invalid query: ERROR: column "distance" does not exist LINE
> 1: ...ude ) ) ) ) AS distance FROM aaafacilities HAVING distance <...
> ^
>
> I'm new to this, but it doesn't look like I need to create a column in
> my table for distance, or at least the directions didn't say to create
> a distance column.

>
> Here is my PHP with SQL:
> $query = sprintf("SELECT 'ID', 'FACILITY', 'ADDRESS', latitude,
> longitude, ( 3959 * acos( cos( radians('%s') ) * cos( radians
> ( latitude ) ) * cos( radians( longitude ) - radians('%s') ) + sin
> ( radians('%s') ) * sin( radians( latitude ) ) ) ) AS distance FROM
> aaafacilities HAVING distance < '%s' ORDER BY dist LIMIT 0 OFFSET 20",

>  pg_escape_string($center_lat),
>  pg_escape_string($center_lng),
>  pg_escape_string($center_lat),
>  pg_escape_string($radius));
>
> Does anyone have any ideas on how I can get this to work?  I'm not
> sure what is wrong, since it doesn't seem like I need to create a
> distance column and when I do create one, I get this:

Is that really the whole query?  Why a having with no group by?

Can you do me a favor and print out $query instead of the php stuff?
It might help you as well to troubleshoot to see the real query.

--

Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.