From: | "Brent Wood" <b(dot)wood(at)niwa(dot)co(dot)nz> |
---|---|
To: | <jharahush(at)gmail(dot)com>, <scott(dot)marlowe(at)gmail(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: haversine formula with postgreSQL |
Date: | 2009-09-17 23:50:47 |
Message-ID: | 4AB374180200007B0001E492@gwia.niwa.co.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)marlowe(at)gmail(dot)com> 09/18/09 11:35 AM >>>
On Thu, Sep 17, 2009 at 1:16 PM, Jonathan <jharahush(at)gmail(dot)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(at)postgresql(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2009-09-17 23:53:29 | Re: NAS |
Previous Message | Tom Lane | 2009-09-17 23:00:13 | Re: haversine formula with postgreSQL |