Re: haversine formula with postgreSQL

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.