CODEWITHSUNDEEP
sqlmysql
xRobot
xRobot

Reputation: 26573

cities and distance by latitude-longitude

I have a table with:

city latitude longitude

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

Upvotes: 2

Views: 854

Answers (3)

Bergen88
Bergen88

Reputation: 177

You need the lat long of NYC, or whichever city you want to analyze against. then simply measure miles between the two set of coordinates using math. In Excel the formula is

getDistance(latitude1, longitude1, latitude2, longitude2)

https://gis.stackexchange.com/questions/88484/excel-distance-calculation

Upvotes: 0

David
David

Reputation: 73554

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

Edit - added (sorry I missed these originally)

-- USER-DEFINED FUNCTIONS
-- XAxis #########################################
CREATE FUNCTION [dbo].[XAxis] (@lat float, @lon float)  
RETURNS float
AS  
BEGIN 
   RETURN COS(4 * (4 * atn2(1, 5) - atn2(1, 239)) / 180 * @lat) * COS(4 * (4 * atn2(1, 5) - atn2(1, 239)) / 180 * @lon) 
END

CREATE FUNCTION [dbo].[YAxis] (@lat float, @lon float)  
RETURNS float AS  
BEGIN 
RETURN COS(4 * (4 * atn2(1,5) - atn2(1,239)) / 180 * @lat) * SIN(4 * (4 * atn2(1,5) - atn2(1,239)) / 180 * @lon)
END

CREATE FUNCTION [dbo].[ZAxis] (@lat float)  
RETURNS float AS  
BEGIN 
RETURN SIN(4 * (4 * atn2(1,5) - atn2(1,239)) / 180 * @lat)
END

Upvotes: 3

Felix Kling
Felix Kling

Reputation: 816262

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.

Upvotes: 1

Related Questions