Re: Finding points within 50 miles

From: Janning Vygen pgsql-general(at)postgresql(dot)org CSN Re: Finding points within 50 miles 2005-06-27 07:47:36 200506270947.36633.vygen@gmx.de (view raw or flat) 2005-06-26 23:40:03 from CSN  2005-06-27 07:47:36 from Janning Vygen   2005-06-27 08:46:17 from Oleg Bartunov   2005-06-27 14:32:43 from Vivek Khera  2005-06-27 08:09:11 from "Uwe C(dot) Schroeder"   2005-06-27 09:08:35 from Alban Hertroys    2005-06-27 19:50:06 from "Uwe C(dot) Schroeder"     2005-06-27 20:36:07 from John Browne      2005-06-27 21:09:37 from Vivek Khera       2005-06-28 00:42:41 from Bruno Wolff III        2005-06-28 14:34:41 from Vivek Khera  2005-06-27 14:41:20 from Vivek Khera  2005-06-27 15:29:49 from Bruno Wolff III pgsql-general
```Am Montag, 27. Juni 2005 01:40 schrieb CSN:
> If I have a table of items with latitude and longitude
> coordinates, is it possible to find all other items
> that are within, say, 50 miles of an item, using the
> geometric functions
> (http://www.postgresql.org/docs/8.0/interactive/functions-geometry.html)?
> If so, how?

I did it without some special features and datatypes some time ago. feel free
to modify and use for your own. It should give you an idea how to do it.

SELECT
c1.zip,
c2.zip,
6378.388 *
acos(
) AS distance
FROM
coordinates AS c1
CROSS JOIN coordinates AS c2

I had some problems with the calculation inside acos() sometimes being greater
than 1, which should not occur. Please use a
CASE WHEN sin(...) > 1 THEN 1 ELSE sin(...) END
if you have the same problem.

kind regards,
janning

```

pgsql-general by date

 Next: From: Uwe C. Schroeder Date: 2005-06-27 08:09:11 Subject: Re: Finding points within 50 miles Previous: From: Michael Ben-Nes Date: 2005-06-27 06:29:18 Subject: Re: Raid and tablespaces configuration