2

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]
  • 1
    Look at Spatial Indexes red-gate.com/simple-talk/sql/t-sql-programming/… – Mark Sinkinson Mar 15 '18 at 10:33
  • @MarkSinkinson Giving a quick run over that... looks like it should have hints of what I want - if not the complete. Deep stuff - and I've seen similar. I'll look at it more after work tonight. I've saw roughly similar posts in my research but that might be closer to what I'm looking for - specifically the US addresses and zip code related stuff (I think the other articles dealt more with shades and boundaries - way more advanced than I want I need.) – WernerCD Mar 15 '18 at 16:09
2

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])
|improve this answer|||||
  • The basic idea, I think, falls squarely in the first - although I do like the second as an option as well (and of course, adds the question of next group - ala pagination). I'll be playing more with it tonight - as I've still got to get the data imported from the other source and a couple other minor things before I can try these and give a firm up-vote. – WernerCD Mar 16 '18 at 0:05
  • @WernerCD The indexing is important and sometimes the optimizer forgets that spatial can be expensive. If you find the database decides to do parallel processing and ignore the spatial index try adding a OPTION (MAXDOP 1) to the query – MickyT Mar 16 '18 at 0:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.