Spatial SQL (in SSMS - using T-SQL) - STIntersect and STContains doesn't return the correct result

Question

Trying to find

1. All the routes that pass through a buffer of 5km around a random point.

Background:

1. 1. I have multiple routes stored as linestring of type geography using the following query. The linestring is valid.

INSERT INTO routes (route_id, route_name, route_geography)
VALUES (
    8,
    'route-test-8',
    geography::STGeomFromText('LINESTRING(50.47959 -80.518649, 50.47923 -80.519601, ......)', 4326)
);

NOTE: For testing purposes, this random point is a point that exists in one of the routes. So in theory I should at least get 1 route in true result

3. Table structure that stores routes:

Columns and it's types: route_geography of type geography (this is a photo of table structure)

Tried the following query:

1. Used STContains: doesn't return any route as true when in fact it should, because the (lat,long) point I select is in the route itself

-- Declare the "random" point

DECLARE @startPoint geography;
SET @startPoint = geography::Point('50.47959','-80.518649' , 4326); -- this is the exact point from one of the routes 

-- Create a buffer around the "random" point

DECLARE @buffer geography;
SET @buffer = @startPoint.STBuffer(5000); -- Create a 5km radius buffer

-- Finding the routes that pass through the buffer

SELECT [route_id], [route_name],[route_geography].
FROM [test-DB].[dbo].[routes]
WHERE @buffer.STContains([route_geography]) = 1; -- when assigned 0 returns all the routes

2. Used STIntersect: same result as with STContains

-- Declare the "random" point

DECLARE @startPoint geography;
SET @startPoint = geography::Point('50.47959','-80.518649' , 4326); -- this is the exact point from one of the routes 

-- Create a buffer around the "random" point

DECLARE @buffer geography;
SET @buffer = @startPoint.STBuffer(5000); -- Create a 5km radius buffer

-- Finding the routes that pass through the buffer

SELECT [route_id], [route_name]
FROM [test-DB].[dbo].[routes]
WHERE [route_geography].STIntersects(@buffer) = 1; -- when assigned 0 returns all the routes

Answer 1

You have fallen trap to an inconsistence between the Point method and STGeomFromText. Point wants the argument as Lat, Long, SRID. But in STGeomFromText, Lat and Long are reversed. While the Point method is more natural, STGeomFromText builds on some standard, I believe.

There is actually a way to see this in SSMS (and that was also how I figured this out). Run this:

DECLARE @startPoint geography;
SET @startPoint = geography::Point('50.47959', '-80.518649' , 4326); -- this is the exact point from one of the routes 
-- Create a buffer around the "random" point
DECLARE @buffer geography;
SET @buffer = @startPoint.STBuffer(5000); -- Create a 5km radius buffer
-- Finding the routes that pass through the buffer
SELECT 1 AS  l, @startPoint AS g
SELECT 2, @buffer
SELECT 3, route
FROM   routes

The look at the tab Spatial results. That makes it very clear that the buffer and the line are in very different places.

Assuming that you are not working with places in Antarctica, your line should be like this:

CREATE TABLE routes (id int NOT NULL primary key,
               name nvarchar(23) NOT NULL, 
               route geography)
INSERT INTO routes 
VALUES (
    8,
    'route-test-8',
    geography::STGeomFromText('LINESTRING(-80.518649 50.47959, -80.519601 50.47923 )', 4326)
);

Also look at the topic for STGeomFromText. The topic itself does not say much, but the example at the end makes it clear that the order is long/lat.

Answer 2

Fix the data and try another condition:

set @startPoint = geography::Point(-80.518649, 50.47959, 4326)  -- this is the exact point from one of the routes 
--or:
--set @startPoint = geography::STGeomFromText('POINT(50.47959 -80.518649)' , 4326) 

. . .

select route_id, route_name
from routes
where @buffer.STIntersects(route_geography) = 1