Search Postgresql Archives

Re: haversine formula with postgreSQL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



It's the whole query as far as I can tell.  The app takes input from
the user --- the user enters an address and chooses a radius ("show me
all facilities within 5 miles of this address") and then the latitude
and longitude of the address and the radius is passed into the query
so that the database can grab all locations within a certain radius.

The example provided by Google is using MySQL.  The query for MySQL
looks like this:

SELECT address, name, lat, lng, ( 3959 * acos( cos( radians('%s') ) *
cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin
( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers
HAVING distance < '%s' ORDER BY distance LIMIT 0 , 20

And I'm attempting to change it to work with Postgres and have done
this:

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 distance LIMIT 0 OFFSET 20


aaafacilities is my table name in my posgres database.

I'm sorry if this isn't enough info.. like I said, I'm new to this but
definitely interested in learning and figuring this out!

>From what I can tell, the database is supposed to calculate and then
output the distance of each "match" but it seems like in the MySQL
example, it can do this without having an actual distance column in
the database.

Thanks again!

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux