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
Top comments (1)
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.