Geo Location Search with SQL Server

Localizing your search query is important when you want to return local restaurants, your users want a restaurant in the area their area, not on the other side of the town or country

SQL Server has great support for that and let's dive into it.

The Challenge

We want to have a table with restaurants, and search for restaurants in the area of the user

Create the Table

The main "secret" is to set the LatLng column to geography

CREATE TABLE [dbo].[LocalBusinesses](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [BusinessName] [nvarchar](50) NOT NULL,
    [LatLng] [geography] NULL,
    [Phone] [nvarchar](25) NULL,
    [BookingUrl] [nvarchar](100) NULL,
 CONSTRAINT [PK_LocalBusinesses] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Insert Local Business Data

The following code will insert 4 restaurants, in 2 different cities

CREATE PROCEDURE  [dbo].[LocalBusinessAdd]
    @BusinessName as nvarchar(50),
    @Latitude as float,
    @Longitude as float,
    @BusinessPhone as nvarchar(25),
    @BusinessBookingUrl as nvarchar(250)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO LocalBusinesses (BusinessName,  latlng, Phone, BookingUrl)
    VALUES(@BusinessName, geography::Point(@Latitude,@Longitude, 4326), @BusinessPhone,@BusinessBookingUrl);

END

exec dbo.LocalBusinessAdd 'Fiskebar', 46.2079205,6.1485323, '+4122...','https://...';

exec dbo.LocalBusinessAdd 'Restaurant Les Armures', 46.2010473,6.147613, '+4122...','https://...';

exec dbo.LocalBusinessAdd 'Thach', 47.3787218, 8.5298173, '+4143...','https://...';

exec dbo.LocalBusinessAdd 'La Lup Brandschenke', 47.3696334,8.5334346, '+4143...','https://...';

Return Latitude and Longitude

SELECT BusinessName, LatLng.Lat as 'Latitude', LatLng.Long as 'Longitude' 
FROM dbo.LocalBusinesses
WHERE ...

Get The Restaurants Within a Certain Distance

ALTER PROCEDURE  [dbo].[LocalBusinessSearch]
    @Latitude as float,
    @Longitude as float,
    @MaxDistance as int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.

    DECLARE @DistanceFromPoint geography = geography::Point(@Latitude,@Longitude, 4326);

    SET NOCOUNT ON;
    SELECT BusinessName, Phone, BookingUrl, LatLng.STDistance(@DistanceFromPoint) as 'Distance'
    FROM dbo.LocalBusinesses
    WHERE LatLng.STDistance(@DistanceFromPoint) <= @MaxDistance
    ORDER BY LatLng.STDistance(@DistanceFromPoint) 
END

Execute the Search Stored Procedure

When you have the location of the user (via browser or other techniques, you can return the restaurants in the users area.

-- Return Restaurants in Zurich in a 4km radius
exec  [dbo].[LocalBusinessSearch] 47.3744489,8.5410422,4000  

-- Return Restaurants in Geneva in a 2km radius
exec  [dbo].[LocalBusinessSearch] 46.2017559, 6.1466014, 2000  

Comments

[Ayesha Javed]

Thats Great.Geolocation searches in SQL server location you typically use the spatial data types and functions provided by SQL Server's spatial support. Indexing these columns can significantly improve the performance of spatial queries.