## Re: How many views...

From: Michael Fuhr "Uwe C(dot) Schroeder" pgsql-general(at)postgresql(dot)org Re: How many views... 2004-11-29 08:58:50 20041129085850.GA46935@winnie.fuhr.org (view raw or flat) 2004-11-29 02:41:50 from "Uwe C(dot) Schroeder"  2004-11-29 06:43:28 from Michael Fuhr   2004-11-29 07:06:38 from "Uwe C(dot) Schroeder"    2004-11-29 08:15:24 from Holger Klawitter    2004-11-29 08:44:37 from Tino Wildenhain    2004-11-29 08:58:50 from Michael Fuhr  2004-11-29 06:49:32 from Greg Stark   2004-11-29 07:27:57 from "Uwe C(dot) Schroeder" pgsql-general
```On Sun, Nov 28, 2004 at 11:06:38PM -0800, Uwe C. Schroeder wrote:

> The "unhandy" part is maybe a personal perception. I like stored procs, but in
> this case the calculation is repeated over and over again (ok, it would be
> the same with views).  Basically the part I don't like is that the proc
> currently calculates way more values than needed.  Because something like
> .... where sqrt(pow((lat1 - lat2),2) + pow((long1 - long2),2)) >= 50
> certainly calculates the distance of all the records and then compares the
> result to the 50 mile radius.

The formula you mention is for calculating distances on a plane, not on
the surface of a sphere.  Google for more appropriate formulae (e.g.,
the Haversine Formula) or use the functions in contrib/earthdistance.

> I'd rather have something that excludes most of the records that
> aren't in question anyways. How do you come to the lat/long values
> for the max difference? Is there a general formula for that?

For the longitude bounds, calculate how far away a point one degree
due east or west would be.  For example, the distance between (40, -90)
and (40, -91) is about 53mi, depending on what value you use for the
Earth's radius (the Earth isn't a perfect sphere).  If you want to
find points within 50 miles, limit your search to longitudes within
50/53 (0.94) degrees of -90, or -90.94 to -89.06.

Repeat for latitude.  The distance between (40, -90) and (41, -90)
is about 69mi, so limit your search to latitudes within 50/69 (0.72)
degrees of 40, or 39.28 to 40.72.

Note that one degree of longitude doesn't cover the same distance
as one degree of latitude: that's because longitude lines converge
as they approach the poles.  At the equator, one degree of longitude
covers about 69mi, while at 40N it's only 53mi.

> This looks like I could omit records too far away from the calculation
> in the first place.

That's the idea.  Using the above calculations, you'd make a query
like this:

SELECT ...
FROM ...
WHERE latitude BETWEEN 39.28 AND 40.72
AND longitude BETWEEN -90.94 AND -89.06
AND distance(latitude, longitude, 40, -90) <= 50;

Substitute an appropriate function for distance().

The latitude and longitude checks find the candidate points and the
distance check makes the final selection.  With indexes on latitude
and longitude (or a multicolumn index on both latitude and longitude),
this query should be reasonably fast.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

```

### pgsql-general by date

 Next: From: Nageshwar Rao Date: 2004-11-29 09:20:20 Subject: tool for postgresql Previous: From: Chris Green Date: 2004-11-29 08:50:51 Subject: Re: Adding Reply-To: to Lists configuration ...