Using SQL geometry type for finding near and intersecting shapes to a lat-lng point

This short blog post will provide you with two SQL stored procedures which work with the SQL geometry data type to figure out how a lat/lng point correlates to spatial shapes on the DB level.

What this means is – you provide lat/lng and the DB returns all the shapes which this point intersects with or in the other case returns the nearest shapes to that point.

You could also use the geography type for this and the code should end up being only slightly more complex than this but we didn’t have the need for that so we ended up using the geometry type.

We used these to identify company branches for a certain location on the map. The geometry data is stored in the SpatialData field (of geometry type), and we’re returning the BranchId and BranchName but you should obviously modify that to your needs.

-- Finds intersecting branches -- Accepts lat and lng CREATE PROC SP_GET_INTERSECTING_BRANCHES   @lat FLOAT,   @lng FLOAT AS BEGIN   DECLARE @point GEOMETRY   SET @point = GEOMETRY::Point(@lng, @lat, 4326)   SELECT BranchId, BranchName FROM [dbo].[Branch]   WHERE @point.STIntersects(SpatialData) = 1 END GO   -- Finds the nearest branches -- Accepts lat, lng and the amount of matching rows to return CREATE PROC SP_GET_CLOSEST_BRANCHES   @lat FLOAT,   @lng FLOAT,   @amount INTEGER AS BEGIN   DECLARE @point GEOMETRY   SET @point = GEOMETRY::Point(@lng, @lat, 4326)   SELECT TOP (@amount) BranchId, BranchName, @point.STDistance(SpatialData) AS Distance FROM [dbo].[Branch]   ORDER BY @point.STDistance(SpatialData) END GO

Hope it helps. Cheers!