CODEWITHSUNDEEP
c#sqlasp.netsql-server
user3810961
user3810961

Reputation: 61

Find Nearest Latitude and Longitude

my question is , i have hotel info alone with their latitude and longitude in my database.now i want to find the nearest hotel from the given latitude and longitude . for example :suppose latitude is 196.98575 and longitude is = 24.985644, now based on this lat long i want to find the nearest hotel in 15 km, which i have available in my database . please suggest me any idea or if you have any stored procedure please tell me so that i can avoid manually task. i am using sql server.

Upvotes: 0

Views: 3351

Answers (2)

Ben Thul
Ben Thul

Reputation: 32737

As was alluded to in a comment above, SQL Server has had native geospatial capabilities since SQL 2008. Here's my stab at a solution:

create table dbo.Hotels (
    HotelID int identity not null,
        constraint [PK_Hotels] primary key clustered (HotelID),
    Longitude decimal(15, 12) not null,
    Latitude decimal(14, 12) not null,
    geo as geography::Point(Latitude, Longitude, 4326)
)

insert into dbo.Hotels 
    (Longitude, Latitude)
values
    (-122.4167, 37.7833);
go

create procedure dbo.findHotels (@point geography, @distanceKM int = 15)
as
begin
    --SRID 4326 measures disances in meters, so use that measure
    declare @distanceM int = @distanceKM * 1000;

    select HotelID, @point.STDistance(geo) as [DistanceInM]
    from dbo.Hotels
    where @point.STDistance(geo) <= @distanceM
    order by @point.STDistance(geo);
end
go

declare @longitude decimal(15, 12) = -122.4168,
    @latitude decimal(14, 12) = 37.7832;
declare @p geography = geography::Point(@latitude, @longitude, 4326);

exec dbo.findHotels @p, 15;

Upvotes: 1

sakir
sakir

Reputation: 3502

Take a look at here.there It find places within a distance d=1000 km from M=(lat, lon)=(1.3963, -0.6981) in a database *Lat and Lon that hold the coordinates in radians

here is the simple query according to this source

SELECT * FROM Places WHERE
    (Lat => 1.2393 AND Lat <= 1.5532) AND (Lon >= -1.8184 AND Lon <= 0.4221)
AND
    acos(sin(1.3963) * sin(Lat) + cos(1.3963) * cos(Lat) * cos(Lon - (-0.6981))) <= 0.1570;

Upvotes: 0

Related Questions