Archives of the TeradataForum

Message Posted: Thu, 04 May 2006 @ 22:08:55 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

The easy way to clean this up is to add a WHERE clause requiring the two points to be within a reasonable fraction of a degree away from each other, eg:

     WHERE
         p1.Lat BETWEEN
         (
                 (p2.Lat - 0.16667) AND
                 (p2.Lat + 0.16667)
         ) AND
         p1.Lon BETWEEN
         (
                 (p2.Lon - 0.16667) AND
                 (p2.Lon + 0.16667)
         )

This enforces a roughly 10-mile radius (bounding box, actually) around p1.

Of course, I'm assuming:

- a spherical Earth where 1 degree on a great circle equals 60 miles

- you're only dealing with temperate latitudes

- you aren't working near the equator or prime meridian.

If those assumptions don't work for you, you can make changes inside the WHERE clause to deal with them. But keep in mind that the US- Canada border is at 49 degrees N. At this latitude a 1-degree change in longitude is about 40 miles, because:

[Distance in miles] ~= [Distance in degrees longitude] * cos [Latitude] * 60;

Therefore simply adding 50% slop to your bounding box should work well enough for any locations in the lower 48. You can put the cosine calculation into the WHERE clause if it's important, but some RDMBS won't use an index on a calculated value. I don't know about TD.

These are a bunch of sloppy assumptions, but remember that all you're trying to do is limit the number of rows that get joined together. The distance calculation can still be as precise as you want.

	<Prev	Next>		<<First	<Prev	Next>	Last>>