SQL Server STIntersects, STDistance and STCointains with Lat Lon points and Geometry Polygons [closed]

Question

I need to create 3 functions in SQL Server (2017) that has to do the following:

• IsPointInsideTown(Point, TownCode, [SRID]) -> true/false: indicates if the point is inside the town's polygon.
• TownOfPoint(Point, [SRID]) -> TownCode: indicates which is the town's polygon that contains the point.
• DistanceFromTown(Point, TownCode, [SRID]) -> meters: calculate the minimal distance from the point and the specified town's polygon.

I've created the TownBoundaries table (layer) with a GEOMETRY data type column with EPSG:4326 WGS 84 SRID, using QGIS v3.20.

This is the QGIS Information window about the original source Shapefile of the boundaries.

and this is the Information window about the final converted layer on the SQL Server database.

The source CRS has been converted from EPSG:32632 WGS 84 UTM Zone 32N to EPSG:4326 WGS 84 to make it compatible with the Lat/Lon coordinates that I need to check using the described functions.

The points to check come from GPS devices and are sent to the system in Latitude/Longitude degrees format (ie: 53.553813, 9.99158).

Implementations of the 3 functions

-- IsPointInsideTown: WORKS!

CREATE OR ALTER FUNCTION IsPointInsideTown (
    @Latitude FLOAT,
    @Longitude FLOAT,
    @TownCode CHAR(6),
    @SRID INT = 4326
)
RETURNS BIT
AS
BEGIN
    DECLARE @return_value BIT;
    
    SELECT @return_value = Geom.STIntersects(GEOMETRY::Point(@Longitude, @Latitude, @SRID))
    FROM [GeoData].[dbo].[V_Towns] 
    WHERE TownCode = @TownCode

    RETURN @return_value
END;

-- TownOfPoint: WORKS!

CREATE OR ALTER FUNCTION TownOfPoint (
    @Latitude FLOAT,
    @Longitude FLOAT,
    @SRID INT = 4326
)
RETURNS CHAR(6)
AS
BEGIN
    DECLARE @return_value CHAR(6);
    
    SELECT @return_value = TownCode
    FROM [GeoData].[dbo].[V_Towns]
    WHERE Geom.STContains(GEOMETRY::Point(@Longitude, @Latitude, @SRID)) = 1

    RETURN @return_value
END;

-- DistanceFromTown: NOT WORKING!

CREATE OR ALTER FUNCTION DistanceFromTown (
    @Latitude FLOAT,
    @Longitude FLOAT,
    @TownCode CHAR(6),
    @SRID INT = 4326
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @return_value FLOAT;
    
    SELECT @return_value = Geom.STDistance(GEOMETRY::Point(@Longitude, @Latitude, @SRID))
    FROM [GeoData].[dbo].[V_Towns]
    WHERE TownCode = @TownCode

    RETURN @return_value
END;

The first 2 functions (IsPointInsideTown and TownOfPoint) are working correctly. I've made some tests and then checked on QGIS map.

DistanceFromTown instead, is NOT working properly. It gives me a result that is not the correct measure in meters. It's always a 0,x number when it's actually thousands of meters.

I found out (see STDistance Unit in SQL Server 2008 and MS SQL Server's STDistance output differs from Google Earth/Maps straight line distance) that in order to make the STDistance function works correctly with Lat/Lon coordinates, I should use the GEOGRAPHY data type and not GEOMETRY.

Questions

1. As said, from the test I've made it seems that IsPointInsideTown and TownOfPoint are working properly, even if they're making "mixed" use of the GEOMETRY data type and Lat/Lon coordinates, but is it really correct or is there another (better) way to do that?
2. How can I make the DistanceFromTown function works as well? I initially thought about using STTransform() to convert Lat/Lon Points to Points with plain coordinates (also for the other 2 functions), but I know that unfortunately the function is not implemented on SQL Server.

Answer 1

Using:

1. How to Cast geometry to Geography in SQL script in MS SQL Server?
2. STDistance between point and polygon always returns 0 even though they are miles away

I found a solution!

As said by @PeterS in his answer (Question 1 above), it's probably not the best solution in terms of performance, but it works!

And thanks to @Ben Thul (Question 2 above) I found a solution to distance = 0 issue when the point is inside the polygon.

This is the updated implementation of my DistanceFromTown function.

CREATE OR ALTER FUNCTION DistanceFromTown (
    @Latitude FLOAT,
    @Longitude FLOAT,
    @TownCode CHAR(6),
    @InnerDistance BIT = 0,
    @SRID INT = 4326
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @return_value FLOAT;
    DECLARE @pointAsGeometry GEOMETRY;
    DECLARE @pointAsGeography GEOGRAPHY;

    SET @pointAsGeometry = GEOMETRY::Point(@Longitude, @Latitude, @SRID);
    SET @pointAsGeography = GEOGRAPHY::Point(@Longitude, @Latitude, @SRID);
    
    SELECT @return_value = 
        CASE 
            -- Point outside of Polygon
            WHEN Geom.STIntersects(@pointAsGeometry) = 0 
                THEN GEOGRAPHY::STGeomFromText(Geom.STAsText(), 4326).MakeValid().ReorientObject().STDistance(@pointAsGeography)
            -- Point inside Polygon and inner distance flag FALSE
            WHEN Geom.STIntersects(@pointAsGeometry) = 1 AND @InnerDistance = 0 
                THEN 0
            -- Point inside Polygon (with Envelope angle < 180°) and inner distance flag TRUE
            WHEN Geom.STIntersects(@pointAsGeometry) = 1 AND GEOGRAPHY::STGeomFromText(Geom.STAsText(), 4326).MakeValid().EnvelopeAngle() < 180 AND @InnerDistance = 1 
                THEN GEOGRAPHY::STGeomFromText(Geom.STAsText(), 4326).MakeValid().ReorientObject().STDistance(@pointAsGeography)
            -- Point inside Polygon (with Envelope angle = 180°) and inner distance flag TRUE
            WHEN Geom.STIntersects(@pointAsGeometry) = 1 AND GEOGRAPHY::STGeomFromText(Geom.STAsText(), 4326).MakeValid().EnvelopeAngle() = 180 AND @InnerDistance = 1 
                THEN GEOGRAPHY::STGeomFromText(Geom.STAsText(), 4326).MakeValid().STDistance(@pointAsGeography)
        END
    FROM [GeoData].[dbo].[V_Towns]
    WHERE TownCode = @TownCode

    RETURN @return_value
END;

Usage

DECLARE @Latitude FLOAT = 45.123450
DECLARE @Longitude FLOAT = 10.987650

-- Point outside of Polygon
SELECT [GeoData].[dbo].DistanceFromTown(@Latitude, @Longitude, '00001', DEFAULT, DEFAULT)
-- Point inside of Polygon (not considering inner distance)
SELECT [GeoData].[dbo].DistanceFromTown(@Latitude, @Longitude, '00002', DEFAULT, DEFAULT)
-- Point inside of Polygon (getting the inner distance)
SELECT [GeoData].[dbo].DistanceFromTown(@Latitude, @Longitude, '00002', 1, DEFAULT)