Re: haversine formula with postgreSQL

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(dot)wood(at)niwa(dot)co(dot)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(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.
>