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.marl...@gmail.com> 09/18/09 11:35 AM >>> On Thu, Sep 17, 2009 at 1:16 PM, Jonathan <jharah...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
