DEV Community

Frederik Van Lierde
Frederik Van Lierde

Posted on • Updated on

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
Enter fullscreen mode Exit fullscreen mode

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://...';
Enter fullscreen mode Exit fullscreen mode

Return Latitude and Longitude

SELECT BusinessName, LatLng.Lat as 'Latitude', LatLng.Long as 'Longitude' 
FROM dbo.LocalBusinesses
WHERE ...
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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  
Enter fullscreen mode Exit fullscreen mode

Top comments (1)

Collapse
 
techengagepro profile image
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.