CODEWITHSUNDEEP
c#sql-servert-sqlgeolocationlatitude-longitude
tugberk
tugberk

Reputation: 58444

Find the North West and South East among bunch of Latitudes and Longitudes

I have bunch of location (Lat, Lng) data inside my database and let's assume it has the following columns:

Resort

Latitude

Longitude

I would like to define the radius of available resorts and I need to find the place on North West and South East. I need to find the most north-western resort and most south-eastern resort among those my aim is draw a rectangle which contains all of the places.

What would be the algorithm for that?

The table is stored inside SQL Server and I can use SP or Function here. Also, C# code would be helpful as well.

EDIT

I need to find a place on NW and SE to determine the rectangle. I need to first sort this out. I am not looking for a way to find places within that rectangle for now.

EDIT

Here is my imagination for what I am after (sorry for the awful drawing):

enter image description here

think this as a world map and the blue circles are resorts. I need to find the lat and lng of two places which I mark with a brown circle.

EDIT

@Damien_The_Unbeliever pointed out that I am on the wrong direction here. My aim is draw a rectangle which contains all of the places. And my question is telling another story.

Upvotes: 2

Views: 3188

Answers (3)

Hans Kesting
Hans Kesting

Reputation: 39274

If I understand correctly, you have a bunch of resorts with their coordinates and you want a bounding "rectangle" for those resorts.

Then you need to issue two queries, one to find the minimum and maximum latitude, the other to find min and max longitude.

Then combine those values to get the correct corners.

EDIT
You can use SQL like this:

declare @minlat float, @maxlat float
select @minlat = min(latitude), @maxlat = max(latitude)
from resortstable

And similar for longitude. Then (@minlat, @minlong) is one corner and (@maxlat, @maxlong) the other. You might need to change those: I'm not sure whether latitude means N/S.

For the most NW resort you have a problem: what if one resort is the North-most and another the most West one? How are you going to choose?

EDIT2
Say you have only two resorts, one in the NE corner and the other the SW corner. Maybe add some more on a rough line between those two. What are the NW and SE corner-resorts now?

Upvotes: 1

aF.
aF.

Reputation: 66687

To draw a rectangle where all the resorts fit inside here is the full example:

create table #testing (
Resort varchar(20),
Longitude int,
Latitude int
)

insert into #testing values ('Res 1', 10, 20)
insert into #testing values ('Res 2', -12, 30)
insert into #testing values ('Res 3', 3, -122)
insert into #testing values ('Res 4', 120, 120)
insert into #testing values ('Res 5', -2, 230)
insert into #testing values ('Res 6', 32, -2)

select
    min(Longitude) [Min Longitude],
    max(Longitude) [Max Longitude],
    min(Latitude) [Min Latitude],
    max(Latitude) [Max Latitude],
    convert(varchar, min(Longitude)) + "," + convert(varchar, max(Latitude)) [NW],
    convert(varchar, max(Longitude)) + "," + convert(varchar, min(Latitude)) [SE]
    from #testing

drop table #testing

Upvotes: 2

George Duckett
George Duckett

Reputation: 32428

You really want to use a function that takes into account the curvature of the earth, which will make a reasonable difference if the distances are fairly large.

For example, you could create a function based on the Haversine forumula:

CREATE FUNCTION [dbo].[LatLong_HaversineDistanceMiles] (
@lat1 float,
@lon1 float,
@lat2 float,
@lon2 float
)
RETURNS float AS
BEGIN 

declare @dlon float
declare @dlat float
declare @a float
declare @c float
declare @d float

select @dlon=radians(@lon2)-radians(@lon1)
select @dlat=radians(@lat2)-radians(@lat1)

select @a =square(sin(@dlat/2.0)) + (cos(radians(@lat1)) * cos(radians(@lat2)) * square(sin(@dlon/2.0)))
select @c = 2.0 * atn2(sqrt(@a), sqrt(1.0-@a))
select @d = 3956 * @c

return @d


end

You'd use it from a where clause to filter out those with a value > your radius.

Upvotes: 2

Related Questions