Re: Advice on geolocation

Chris Albertson wrote:
> --- Bruno Wolff III <bruno(at)wolff(dot)to> wrote:
>
>>On Fri, Jul 26, 2002 at 09:33:53 -0700,
>> Chris Albertson <chrisalbertson90278(at)yahoo(dot)com> wrote:
>>
>>>I've done quite a bit of this as I used to work on a GIS
>>>product. Given to lat,lon pairs it is not hard to find
>>>the great circle distance between them. This assumes a
>>>spherical Earth but good enough for your purposes as the
>>>error will be under a few hundred meters
>>
>>My concern about this is that it will not be indexable. I suspect,
>>but don't
>>know, that this won't really be an issue for the small number (~5000)
>>points
>>of data (especially since I expect other constraints to be used in
>>most
>>queries).

I haven't really followed this entire thread, but FWIW, here is a
plpgsql function to get great circle distance given a pair of lat/lons
(the Haversine formula). There is also something in contrib (see
contrib/earthdistance) to do this, but I've used this on a webhost where
I couldn't install my own C libraries.

CREATE FUNCTION "geodist" (float8,float8,float8,float8 ) RETURNS float8 AS '
DECLARE
lat1 ALIAS FOR $1;
lon1 ALIAS FOR $2;
lat2 ALIAS FOR $3;
lon2 ALIAS FOR $4;
dist float8;
BEGIN

dist := 0.621 * 6371.2 * 2 *
atan2( sqrt(abs(0 + pow(sin(radians(lat2)/2 - radians(lat1)/2),2) +
cos(radians(lat1)) * cos(radians(lat2)) * pow(sin(radians(lon2)/2 -
radians(lon1)/2),2))),sqrt(abs(1 - pow(sin(radians(lat2)/2 -
radians(lat1)/2),2) + cos(radians(lat1)) * cos(radians(lat2)) *
pow(sin(radians(lon2)/2 - radians(lon1)/2),2))));

return dist;
END;
' LANGUAGE 'plpgsql';

I used this for finding US zipcodes within a certain distance of the
given zipcode. To improve performance, I also used a "box" around the
lat/lon pairs:

. . .
WHERE
abs(z.lat - $lat1d) * 60 * 1.15078 <= $dist
and abs(z.long - $lon1d) * 60 * 1.15078 <= $dist
and geodist($lat1d,$lon1d,lat,long) <= $dist
and z.zip = az.zipcode
. . .

This limits the data being considered to a square area twice the
dimension of your desired distance, and then the distance calc further
restricts down to a circle of radius $dist. Hopefully you can get the
idea from this snippet.

HTH,

Joe