Sql-server – Sql Server (2017?) Lat/Long index and query

spatialsql serversql-server-2017

TLDR: Given a set of LAT/LON => index and return "nearby" results.

I'm working my way through a MEAN Node.js/MongoDB/Express/Angular book. It's definitely not something I'm comfortable with – I'm more comfortable with Sql Server. I'm also looking at Sql Server 2017 in docker containers.

The exercise I'm working on has a Yelp Type application: Given a location, show nearby businesses and give the user the ability to Rate those businesses.

Where I'm at is planning on moving the backend database/api from MongoDB/Express/Node into Sql Server, Asp.net MVC.

Given the sample layout

Is this where I should be looking? I don't see exactly what I'm looking for…

  • Create table with lat/long coords
  • index the coords
  • query nearby

I'm still looking, but I'm not finding what I'm looking for…

edit: Close question here, but no cigar

edit: getting closer (mainly from this blog post… Now trying to figure out how to index and quickly find "close" points

USE master
GO

IF EXISTS(select * from sys.databases where name='MEAN')
    DROP DATABASE MEAN
GO

CREATE DATABASE MEAN
GO

USE MEAN;
GO


CREATE TABLE [dbo].[Landmark] (
    [ID]                INT IDENTITY(1, 1),
    [LandmarkName]      VARCHAR(100),
    [Location]          VARCHAR(50),
    [Latitude]          FLOAT,
    [Longitude]         FLOAT
)
GO

INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
VALUES ( 'Statue of Liberty', 'New York, USA', 40.689168,-74.044563 ),
       ( 'Eiffel Tower', 'Paris, France', 48.858454, 2.294694),
       ( 'Leaning Tower of Pisa', 'Pisa, Italy', 43.72294, 10.396604 ),
       ( 'Great Pyramids of Giza', 'Cairo, Egypt', 29.978989, 31.134632 ),
       ( 'Sydney Opera House', 'Syndey, Australia', -33.856651, 151.214967 ),
       ( 'Taj Mahal', 'Agra, India', 27.175047, 78.042042 ),
       ( 'Colosseum', 'Rome, Italy', 41.890178, 12.492378 )
GO

ALTER TABLE [dbo].[Landmark]
ADD [GeoLocation] GEOGRAPHY
GO

UPDATE [dbo].[Landmark]
SET [GeoLocation] = geography::Point([Latitude], [Longitude], 4326);

GO

DECLARE @source geography = geography::Point(40.689168,y    -74.044563, 4326); 

SELECT *, @source.STDistance([GeoLocation])
FROM [dbo].[Landmark]

Best Answer

There is a couple of approaches that can be used here depending on the exact requirements.

First of all you will need to create a spatial index on the Landmark table

CREATE SPATIAL INDEX Landmark_GeoLocation_SDX 
ON [dbo].[Landmark] ([GeoLocation])
USING GEOGRAPHY_AUTO_GRID;

Then to query for all points within a specified distance you can do a query similar to the following

DECLARE @withinDistance float = 500.0; -- This is specified in metres
DECLARE @source geography = geography::Point(40.689168, -74.044563, 4326);    

SELECT *, @source.STDistance([GeoLocation])
FROM [dbo].[Landmark]
WHERE @source.STBuffer(@withinDistance).STContains([GeoLocation]) = 1;

The other common query for this type of thing is to return the n closest points, otherwise known as a nearest neighbour or knn query.

DECLARE @neighbourCount int = 5; -- This is specified in metres
DECLARE @source geography = geography::Point(40.689168, -74.044563, 4326);    

SELECT TOP (@neighbourCount) *, @source.STDistance([GeoLocation])
FROM [dbo].[Landmark]
WHERE @source.STDistance([GeoLocation]) IS NOT NULL
ORDER BY @source.STDistance([GeoLocation])
Related Question