Calculate Lat/Long from a Lat/Lng

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Calculate Lat/Long from a Lat/Lng

Author

Topic

zamankazi
Starting Member

5 Posts

Posted - 2008-09-25 : 15:52:56

I would really appreaciate if someone has the anser for this.

I have a Lat/Long and I would like to get a Square box area and get all 4 corners lat/long given a distance.

So I can pick up smaller subset from the address database to do a Radius search.

Any help is much appreicated.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-25 : 17:27:18

Getting the North and South limits is fairly easy. Get the longitude one degree North or South using a Great Circle formula (see link below) to calculate miles or kilometers per degree, and use that to calculate the North and South limits.

Great Circle Distance Function - Haversine Formula
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81360

Getting the distance East or West is more difficult. The distance per degree varies according to your latitude, and also by how many miles East or West you want. For example, the miles per degree is smaller if the distance is 180 degrees than it is for 1 degree, because the shortest distance is along the great circle, not directly East and West.

The only way I have found to do it is to get the degrees per mile starting at 180 degrees using the function, use that to calculate a test latitude, calculate the degrees per mile from there, and repeat until you have a number that is close enough to the correct value. It usually takes 5-6 iterations.

Once you get the limits and do your query, you can use the function on the link to calculate the distance to each location to see if it is inside the circle.

If you have a front end application, it is probably more efficient to do the calculations there to find the limits, and just pass those to the database lookup.

CODO ERGO SUM

zamankazi
Starting Member

5 Posts

Posted - 2008-10-09 : 12:08:23

Michael,

Thanks for your reply. I was looking for a SQl code where I can pass a Lat/Lng and a distance and get the 4 corner of the given lat/lng in lat/lng format. I have gone through all the forums and there are many diffrent formula and calculations which gets confusing on what is a actually right formula to calculate the 4 corners.

Once again your help is much appreaciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-09 : 12:38:19

I have a function at home which calculates the bounding box "1 mile east, west, north and south" of this lat/lon position using haversine formula.

E 12°55'05.63"
N 56°04'39.26"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-10-09 : 12:39:55

What does it do when you're at the North or South pole?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-09 : 12:46:33

Here is the range calculation. You don't need a square:


DECLARE @StartLatitude float,
DECLARE @StartLongitude float,
DECLARE @Miles float,
DECLARE @HighLatitude float
DECLARE @LowLatitude float
DECLARE @HighLongitude float
DECLARE @LowLongitude float
DECLARE @LatitudeRange float
DECLARE @LongitudeRange float


Set @LongitudeRange = @Miles / (((Cos(@StartLatitude * pi() / 180) * 6076.0) / 5280.0) * 60)
Set @LatitudeRange = @Miles / 69.045454545454545454545454545455
Set @LowLatitude = @StartLatitude - @LatitudeRange
Set @HighLatitude = @StartLatitude + @LatitudeRange
Set @LowLongitude = @StartLongitude - @LongitudeRange
Set @HighLongitude = @StartLongitude + @LongitudeRange

SELECT Latitude, Longitude FROM <Yourtable> WHERE (Latitude <= @HighLatitude AND Latitude >= @LowLatitude)  AND (Longitude <= @HighLongitude AND Longitude >= @LowLongitude))

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-10-09 : 14:49:57

quote:

Originally posted by hanbingl

Here is the range calculation. You don't need a square:


DECLARE @StartLatitude float,
DECLARE @StartLongitude float,
DECLARE @Miles float,
DECLARE @HighLatitude float
DECLARE @LowLatitude float
DECLARE @HighLongitude float
DECLARE @LowLongitude float
DECLARE @LatitudeRange float
DECLARE @LongitudeRange float


Set @LongitudeRange = @Miles / (((Cos(@StartLatitude * pi() / 180) * 6076.0) / 5280.0) * 60)
Set @LatitudeRange = @Miles / 69.045454545454545454545454545455
Set @LowLatitude = @StartLatitude - @LatitudeRange
Set @HighLatitude = @StartLatitude + @LatitudeRange
Set @LowLongitude = @StartLongitude - @LongitudeRange
Set @HighLongitude = @StartLongitude + @LongitudeRange

SELECT Latitude, Longitude FROM <Yourtable> WHERE (Latitude <= @HighLatitude AND Latitude >= @LowLatitude)  AND (Longitude <= @HighLongitude AND Longitude >= @LowLongitude))

The calculations appear correct to me, or at least very close.

Do you have a link for the formula?

Also, what is the number you are using for the diameter of the earth in miles?

CODO ERGO SUM

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-09 : 15:09:01

Here is where I found the solution:
TSQL Code Snip: Searching for Locations within a Radius of a Zipcode

http://www.wwwcoder.com/main/parentid/462/site/5746/68/default.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-09 : 15:25:54

This is the function I use to calculate the bounding box

CREATE FUNCTION [dbo].[fnHaversineBox]
(
	@Lat DECIMAL(9, 6),
	@Lon DECIMAL(9, 6),
	@Distance DECIMAL(8, 3)
)
RETURNS @Box TABLE
	(
		minLat DECIMAL(9, 6),
		minLon DECIMAL(9, 6),
		maxLat DECIMAL(9, 6),
		maxLon DECIMAL(9, 6)
	)
AS
BEGIN
	DECLARE	@minLat DECIMAL(9, 6),
		@minLon DECIMAL(9, 6),
		@maxLat DECIMAL(9, 6),
		@maxLon DECIMAL(9, 6)

	SELECT	@minLat = ASIN(SIN(@Lat / 57.2957795130823) * COS(@Distance / 6371.0E) - COS(@Lat / 57.2957795130823) * SIN(@Distance / 6371.0E)),
		@maxLat = ASIN(SIN(@Lat / 57.2957795130823) * COS(@Distance / 6371.0E) + COS(@Lat / 57.2957795130823) * SIN(@Distance / 6371.0E)),
		@minLon = ATN2(COS(@Distance / 6371.0E) - SIN(@Lat / 57.2957795130823) * SIN(@minLat), - SIN(@Distance / 6371.0E) * COS(@Lat / 57.2957795130823)),
		@maxLon = ATN2(COS(@Distance / 6371.0E) - SIN(@Lat / 57.2957795130823) * SIN(@maxLat), + SIN(@Distance / 6371.0E) * COS(@Lat / 57.2957795130823))

	INSERT	@Box
	VALUES	(
			57.2957795130823 * @minLat,
			@Lon - 57.2957795130823 * @minLon + 90.0E,
			57.2957795130823 * @maxLat,
			@Lon - 57.2957795130823 * @maxLon + 90.0E
		)
	RETURN
END