Need Help w/Nested Select

[Rob]

I have 2 tables of locations with Latitude & Longitude coordinates. TableA has about 300 records. TableB has about 85,000 records. I would like to find all records in TableB that are within 1 mile of TableA sites.

Here is my query which keeps giving me the "at most one record can be returned by this subquery". Yes, I'm expecting there to be several records which are returned by the subquery. All I want is the SiteNumberDo you have any suggestions?

SELECT TableB.SiteNumber, TableB.Latitude, TableB.Longitude FROM TableB WHERE (((69.1733*(((Abs((SELECT Latitude FROM TableA))-Abs([TableB].[Latitude]))^2)+((Abs((SELECT Longitude FROM TableA))-Abs([TableB].[Longitude]))^2))^0.5)<1))

[Notes about calculations: I'm using the 69.1733 (assumption about average miles per degree for a perfectly round Earth) and the various square root functions to basically compute the hypotenuse of a fictional right triangle between the 2 points so I can compute the distance. Thanks Pythagoras.]


Thanks,
Rob

 

[Tom Ellison]

Dear Rob:

A basis for this would be a cross-product of all points in A and all points in B. That would look like:

SELECT *
FROM TableA A, TableB B

I show you this because running this will produce all the data for every combination, 26,500,000 combinations. If you run this, it will take a while! But your problem will need to consider every one of these combinations, right?

For what it's worth, I have about 12 years of education and experience in engineering and land surveying.

There are ways to improve that, but I feel it is important to start with some basics.

Pythagoras is OK as long as you stay close to the equator. Differences in latitude at sea level do not vary. They are 60 nautical miles per degree (well, close) no matter where you are. Longitude is also 60 nautical miles per degree when you're at the equator. But they get closer together as you head away from the equator.

The distance from the prime meridian (0 degrees longitude) to the opposite side of the earth (180 degrees) is about 12,500 miles along the equator.

If you are at 89.9 degrees north latitude they are less than 2 miles apart. Without a correction for latitude, your formula will become inaccurate outside the tropics. I don't know what kind of accuracy you expect, but you may not want to use this in Canada.

Whatever formula you use, you can reduce the number of combinations to be considered rather simply. Anything that is within 1 mile is clearly going to be within 1 minute of both latitude and longitude, or 0.017 degrees. Before even beginning all the calculations, we can change the query to reflect which points are "candidates" this way:

SELECT *
FROM TableA A, TableB B
WHERE ABS(A.Latitude - B.Latitude) < .017
AND ABS(A.Longitude - B.Longitude) < .017

You may enjoy seeing if this is much faster than the first query.

From the latitudes and longitudes above, you may employ the formula of your choice to calculate distances. I recommend Napier instead of Pythagoras, but for distances under a mile and in the tropics, you may have a good rough figure.

Tom Ellison


I have 2 tables of locations with Latitude & Longitude coordinates. TableA has about 300 records. TableB has about 85,000 records. I would like to find all records in TableB that are within 1 mile of TableA sites.

Here is my query which keeps giving me the "at most one record can be returned by this subquery". Yes, I'm expecting there to be several records which are returned by the subquery. All I want is the SiteNumberDo you have any suggestions?

SELECT TableB.SiteNumber, TableB.Latitude, TableB.Longitude FROM TableB WHERE (((69.1733*(((Abs((SELECT Latitude FROM TableA))-Abs([TableB].[Latitude]))^2)+((Abs((SELECT Longitude FROM TableA))-Abs([TableB].[Longitude]))^2))^0.5)<1))

[Notes about calculations: I'm using the 69.1733 (assumption about average miles per degree for a perfectly round Earth) and the various square root functions to basically compute the hypotenuse of a fictional right triangle between the 2 points so I can compute the distance. Thanks Pythagoras.]


Thanks,
Rob

 

[Rob]

Thank you SOOOO much, that's a great start!

Sending you a private email about specifics.

-Rob
Dear Rob:

A basis for this would be a cross-product of all points in A and all points in B. That would look like:

SELECT *
FROM TableA A, TableB B

I show you this because running this will produce all the data for every combination, 26,500,000 combinations. If you run this, it will take a while! But your problem will need to consider every one of these combinations, right?

For what it's worth, I have about 12 years of education and experience in engineering and land surveying.

There are ways to improve that, but I feel it is important to start with some basics.

Pythagoras is OK as long as you stay close to the equator. Differences in latitude at sea level do not vary. They are 60 nautical miles per degree (well, close) no matter where you are. Longitude is also 60 nautical miles per degree when you're at the equator. But they get closer together as you head away from the equator.

The distance from the prime meridian (0 degrees longitude) to the opposite side of the earth (180 degrees) is about 12,500 miles along the equator.

If you are at 89.9 degrees north latitude they are less than 2 miles apart. Without a correction for latitude, your formula will become inaccurate outside the tropics. I don't know what kind of accuracy you expect, but you may not want to use this in Canada.

Whatever formula you use, you can reduce the number of combinations to be considered rather simply. Anything that is within 1 mile is clearly going to be within 1 minute of both latitude and longitude, or 0.017 degrees. Before even beginning all the calculations, we can change the query to reflect which points are "candidates" this way:

SELECT *
FROM TableA A, TableB B
WHERE ABS(A.Latitude - B.Latitude) < .017
AND ABS(A.Longitude - B.Longitude) < .017

You may enjoy seeing if this is much faster than the first query.

From the latitudes and longitudes above, you may employ the formula of your choice to calculate distances. I recommend Napier instead of Pythagoras, but for distances under a mile and in the tropics, you may have a good rough figure.

Tom Ellison


I have 2 tables of locations with Latitude & Longitude coordinates. TableA has about 300 records. TableB has about 85,000 records. I would like to find all records in TableB that are within 1 mile of TableA sites.

Here is my query which keeps giving me the "at most one record can be returned by this subquery". Yes, I'm expecting there to be several records which are returned by the subquery. All I want is the SiteNumberDo you have any suggestions?

SELECT TableB.SiteNumber, TableB.Latitude, TableB.Longitude FROM TableB WHERE (((69.1733*(((Abs((SELECT Latitude FROM TableA))-Abs([TableB].[Latitude]))^2)+((Abs((SELECT Longitude FROM TableA))-Abs([TableB].[Longitude]))^2))^0.5)<1))

[Notes about calculations: I'm using the 69.1733 (assumption about average miles per degree for a perfectly round Earth) and the various square root functions to basically compute the hypotenuse of a fictional right triangle between the 2 points so I can compute the distance. Thanks Pythagoras.]


Thanks,
Rob