Search Within a Certain Area with SQL

Localizing your search query is important when you want to return local restaurants, your users want a restaurant in the area they are in, not on the other side of the town or country <br/> 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 NOT NULL,
	BusinessName] [nvarchar NOT NULL,
	LatLng] [geography] NULL,
	[Phone] [nvarchar NULL,
	BookingUrl] [nvarchar NULL,
 CONSTRAINT [PK_LocalBusinesses] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PADINDEX = OFF, STATISTICSNORECOMPUTE = OFF, IGNOREDUPKEY = OFF, ALLOWROWLOCKS = ON, ALLOWPAGELOCKS = 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