Longitude Latitude Radius
-
I have a table that hold the lon/lat and I need to determine 1, 5, mile radius.
What would be the best way to go about this?
I have a function, but not sure how I should use it.
Thanks
Susan
/*
* History: 14-Nov-05 CNH Simplified to resolve Divide By Zero problems
* 29-Mar-02 DKS Created by Deepak K Srinivasan
*
* Test Data:
*
* City Latt Long
* ---- ---- ----
* NYC 40.77 74
* SF 37.75 122.68
* Oakland 37.73 122.22
* Burbank 34.2 118.37
*/
CREATE FUNCTION dbo.CalculateDistance(@LatDeg1 FLOAT, @LonDeg1 FLOAT, @LatDeg2 FLOAT, @LonDeg2 FLOAT)
RETURNS FLOAT AS BEGIN
DECLARE @EarthRadiusMiles AS FLOAT,
@LatRad1 AS FLOAT,
@LonRad1 AS FLOAT,
@LatRad2 AS FLOAT,
@LonRad2 AS FLOAT,
@DotProd AS FLOAT
SET @EarthRadiusMiles = 3958.755 -- Volumic Radius of the Earth in Miles
SET @LatRad1 = RADIANS(ISNULL(@LatDeg1, 0.0))
SET @LonRad1 = RADIANS(ISNULL(@LonDeg1, 0.0))
SET @LatRad2 = RADIANS(ISNULL(@LatDeg2, 0.0))
SET @LonRad2 = RADIANS(ISNULL(@LonDeg2, 0.0))
SET @DotProd = SIN(@LatRad1) * SIN(@LatRad2) + COS(@LatRad1) * COS(@LatRad2) * COS(@LonRad1 - @LonRad2)
-- T-SQL provides ACOS. So, there is no need to implement it via ATAN:
RETURN @EarthRadiusMiles * ACOS(@DotProd)
END
-
I'm not sure I understand the question. Do you have two points and need to know if they are within 1 or 5 miles of each other? Do you have only one point? If so, what do you want as output?
And then again, I might be wrong ...
David Webb -
Assume you have the table [tableLatLongs] with the latitudes and longitudes. Then to use the function, you can simply type something like this in Query Analyser or Enterprise Manager:
select LatDeg1, LonDeg1, LatDeg2, LonDeg2, dbo.CalculateDistance(LatDeg1, LonDeg1, LatDeg2, LonDeg2) from dbo.tableLatLongs
The following assumptions were made:
1. You do understand the purpose of the function and how it is to be used;
2. The latitudes and longitudes are stored in the table [tableLatLongs] with column names LatDeg1, LonDeg1, LatDeg2, LonDeg2.
-
You should remove ISNULL's from your function.
If source values not supplied result must be NULL. Now you may just fool yourself and your customers calculating distance to Greenwich meridian when one of longitudes is not stored in DB.
_____________
Code for TallyGenerator -
Paul,
I have a table with only one column each for longitude and latitude. They will be passing the sa_property_id which has a long and lat listed. I need to show what else is withing 1 mile or 5 miles of that properties long/lat
--Table
CREATE TABLE [dbo].[PropertyDetails] (
[SA_PROPERTY_ID] [int] NOT NULL ,
[SA_X_COORD] [float] NULL ,
[SA_Y_COORD] [float] NULL ,
[Longitude] [float] NULL ,
[Latitude] [float] NULL ,
) ON [PRIMARY]
GO
Data output from above table
SASA_PROPERTY_ID Longitude Latitude
-------------- ----------------------------------------------------- -----------------------------------------------------
23790208 -120.619821 39.568587999999998
(1 row(s) affected)
Passed parameter = SA_PROPERTY_ID
Need list of matching records within 1 mile of above record
Would this be the best query for it? how do I define the radius?
select LatDeg1, LonDeg1, LatDeg2, LonDeg2, dbo.CalculateDistance(LatDeg1, LonDeg1, LatDeg2, LonDeg2) from dbo.tableLatLongs
The following assumptions were made:
1. You do understand the purpose of the function and how it is to be used;
2. The latitudes and longitudes are stored in the table [tableLatLongs] with column names LatDeg1, LonDeg1, LatDeg2, LonDeg2.
-
You could easily turn this into a stored procedure or perhaps a table function...
--===== Define the input parameters
DECLARE @PropertyIDtoFind INT
DECLARE @MileageLimit DECIMAL(7,2)
SET @PropertyIDtoFind = 183297
SET @MileageLimit = 5
--===== Select all properties within the mileage limit
SELECT STR(dbo.CalculateDistance(pd1.Latitude,pd1.Longitude,pd2.Latitude,pd2.Longitude),7,1) AS Miles,
pd2.*
FROM dbo.PropertyDetails pd1 WITH (NOLOCK),
dbo.PropertyDetails pd2 WITH (NOLOCK)
WHERE pd1.SA_PropertyID = @PropertyIDtoFind
AND dbo.CalculateDistance(pd1.Latitude,pd1.Longitude,pd2.Latitude,pd2.Longitude) <= @MileageLimit
ORDER BY Miles
I'm not 100% sure, but the SA_X_Coord and SA_Y_Coord look like "Douglas Projection" coordinates (also called "Airline Coordinates"). Are they?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I had an assignment at university some years ago where we had to write a transport journey planner that allowed bus, ferry, train and walking trips between fixed (eg train stations, bus stops, landmarks) and user-defined (street address) points. Was quite fun except for being forced to use MySQL v3.23 - yuk
Anyhoo... We got our coordinate system from lattitude/longitude into something called a mercartor projection (been a few years - this part is a bit sketchy in my memory). This allowed us to simplify the arithmetic as we were only doing the calculations within a city (Brisbane, Aust) rather than over an entire state/country so we could place less emphasis on curvature of the earth when considering "straight" lines.
To make things efficient for SQL Server, you could get those points within a SQUARE of a given size situated around a nominated point. This translates to a where clause in your select query such as
where point.lat >= (@pointLat - @radius)
and point.lat <= (@pointLat + @radius)
and point.long >= (@pointLong - @radius)
and point.long <= (@pointLong + @radius)
With the proper indices, SQL can evaluate this very quickly.
Then, in your client-side code, you can compute distances to cut the square down to a circle. Doing the hard maths in your compiled code outside the DB engine is going to be a LOT more efficient - let SQL use indices and return rows since that's what it's good at doing.
Hope that helps!
-
I got this some time ago from this site:
Calculating distance between 2 points
Posted 2/16/2006 10:29:00 PM David A. Long (Andy)
You can try to convert your Declinations into a 2nd set of Latitude, Longitude, then use:
CREATE FUNCTION dbo.fn_GreatCircleDistance
(@Latitude1 float = NULL,
@Longitude1 float = NULL,
@Latitude2 float = NULL,
@Longitude2 float = NULL)
RETURNS float
AS
BEGIN
IF @Latitude1 IS NULL RETURN 0
IF @Longitude1 IS NULL RETURN 0
IF @Latitude2 IS NULL RETURN 0
IF @Longitude2 IS NULL RETURN 0
RETURN (ACOS(SIN(@Latitude1 * PI() / 180) * SIN(@Latitude2 * PI() / 180) +
COS(@Latitude1 * PI() / 180) * COS(@Latitude2 * PI() / 180) * COS((@Longitude2 - @Longitude1) * PI() / 180)) * 3963.1)
END
GO
-
There is really no need for calculating the distance with the ACOS/ATAN formula, since the Earth's surface doesn't curve that much for 1.5 miles (2.4 km). Pythagoras theorem will suffice well.
Start with a bounding box that limit all cities from each other where side of box equals 1.5 miles.
Then after filtering out most of the combinations, use Pythagora's theorem to "cut off" the corners in the box.
SELECT z.*
FROM (
SELECT c1.City FromCity,
c1.Latt FromLat,
c1.Long FromLong,
c2.City ToCity,
c2.Latt ToLat,
c2.Long ToLong
FROM CitiesTable c1
CROSS JOIN CitiesTable c2
WHERE ABS(c1.Latt - c2.Latt) <= xyz -- whatever latitude difference that equals 1.5 mile radius
AND ABS(c1.Long - c2.Long) <= xyz -- whatever longitude difference that equals 1.5 miles radius
AND c1.Latt < c2.Latt -- No need to calculate City A -> City B and the same distance
AND c1.Long < c2.Long -- City B -> City A. Also no need to Calculate City A -> City A and back.
) z
WHERE SQRT(POWER(FromLat - ToLat, 2) + POWER(FromLong - ToLong, 2)) <= xyz -- whatever distance difference that equals 1.5 mile radius
N 56°04'39.16"
E 12°55'05.25" -
This also presupposes that you convert your lat/longs into a suitably flattened grid projection as on a map with square grid. The surface distance of one minute of either longitude or latitude itself varies with latitude. Surface distance between lines of longitude obviously varies, from a maximum at the equator, to 0 at the poles. The distance between lines of latitude also differs because the earth is slightly flattened, so the diameter of the earth through the equator is larger than the diameter through the poles. So the equally-spaced angles at which the latitudes are declined don't transate into an equally-spaced surface distribution.
I don't know the maths for converting lat/long to grid ref, but if all distances are to be preserved (within acceptable tolerances), it must be a localised process. You surely can't get a single set of grid references that can be used to compare positions all over the world. It's like trying to flatten out an orange-skin and hoping that all distances will still be the same, even on either side of a tear.
So the effects of these curvature-related problems, as Peter intimates, may be largely irrelevant if you are dealing with small enough areas. Just one possibility is to categorise each point by city, if the cities are suffiently well-separated, and store flat grid coordinates specific to the appropriate city area.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
The formulas used in this topic assumes the Earth is a perfect sphere, which it is not.
The average radius of 3,958.755 miles is between the semi-major axis around the equator of 3.963.189 miles and the semi-minor axis around the poles of 3,949.901 miles.
Using the average tends to an error marginal of 0.1~0.2 percent.
Using Pythagora's theorem on small distances tends to an error marginal of 0.04 percent, which is smaller than the average anyway.
And since we are not talking about rocket science here, this is an acceptable approximation for distances as small as 5 miles. The distance from Tokyo to New York will not fall into that range anyway, and calculating the Pythagora's distance and the radius distance will certainly differ a lot! But 5 miles is 8 km, which you drive in less than 5 minutes (maybe not down-town). These are the distances we are talking about.
For more information, see http://en.wikipedia.org/wiki/Earth_radius
N 56°04'39.16"
E 12°55'05.25"
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply