cities and distance by latitute-longitude

Question

I have a table with:

city latitude longitude

And I need a sql query to know all cities are 100 miles from new york.

Could anyone help me before I go crazy ?

Answer 1

Here's ours. You may need to modify it for your table structure. Ours looks up retail locations (and amenities), not cities, but the hard part is the "closest by distance" which works in this statement.

CREATE PROCEDURE [dbo].[GetNearbyLocations] @CenterLatitude FLOAT, @CenterLongitude FLOAT
AS

DECLARE @CntXAxis FLOAT 
DECLARE @CntYAxis FLOAT 
DECLARE @CntZAxis FLOAT 

SET @CntXAxis = COS(RADIANS(@CenterLatitude)) * COS(RADIANS(@CenterLongitude)) 
SET @CntYAxis = COS(RADIANS(@CenterLatitude)) * SIN(RADIANS(@CenterLongitude)) 
SET @CntZAxis = SIN(RADIANS(@CenterLatitude)) 

SELECT LocationId, LocationName, Address, City, State, Zip, Phone, Latitude, Longitude,
               hasATM, hasCarWash, hasDiesel, hasE85, is24hr, hasTrendar, hasWiFi, isTravelCenter, isMiniTravelCenter, isTruckerFriendly, hasScale, hasHotFood,
               ProxDistance = 3961 * ACOS( dbo.XAxis(latitude, longitude)*@CntXAxis + dbo.YAxis(latitude, longitude)*@CntYAxis + dbo.ZAxis(latitude)*@CntZAxis) 
FROM Locations 
WHERE latitude IS NOT NULL 
ORDER BY ProxDistance ASC
GO

Answer 2

Maybe this helps you: http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL
It is a nice introduction. Or just google for mysql distance queries, you will find some tutorials.

If you have the possibility and want to have it easier, switch to PostgreSQL which supports distance queries out of the box.