TLDR: Given a set of LAT/LON => index and return "local""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/quickly 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]