Re: haversine formula with postgreSQL - Mailing list pgsql-general
| From | Jonathan Harahush |
|---|---|
| Subject | Re: haversine formula with postgreSQL |
| Date | |
| Msg-id | 822dfc650909171937h56c6c706r9a820e19dfd0b5f@mail.gmail.com Whole thread Raw |
| In response to | Re: haversine formula with postgreSQL ("Brent Wood" <b.wood@niwa.co.nz>) |
| Responses |
Re: haversine formula with postgreSQL
(Scott Marlowe <scott.marlowe@gmail.com>)
|
| List | pgsql-general |
I do have PostGIS installed and I use it for other things (geoserver), but I'm not knowledgeable enough about it to the point where I understand how to get it to work with the Google Maps API. I'll look into it. In the meantime, I was hoping to create something based off of the GMaps/PHP/MySQL example I referenced in an earlier post since I'm still learning.
The reason why I'm using Postgres is because it's installed at work. We don't use MySQL.
Thanks for all of the help so far! I appreciate it.
On Thu, Sep 17, 2009 at 5:50 PM, Brent Wood <b.wood@niwa.co.nz> wrote:
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.>> pg_escape_string($center_lat),
> 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_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)NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
pgsql-general by date: