1

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.

Update 2021/10/22

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

Shapefile layer info

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

SQL Server layer info

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 (please 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...
2
  • Have you seen the example in this doc that casts the 2 geometries to geographies (but still in 4326)?
    – mkennedy
    Oct 14, 2021 at 15:57
  • @mkennedy Yes, I've seen the documentation example, but to get the distance in meters, I have to convert the GEOMETRY field on the fly to GEOGRAPHY, and in the example the casting is done outside the SELECT statement, so I wasn't able to find the correct syntax to do it inside the SELECT. Oct 15, 2021 at 6:46

1 Answer 1

1

Thanks to:

  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 n. 1 above), it's probably not the best solution in terms of performance, but it works!

And thanks to @Ben Thul (Question n. 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)
6
  • 1
    To make the code a bit more readable, I'd do GEOMETRY::Point(@Longitude, @Latitude, @SRID) once and store it in a variable. Also, is there any reason not to fix your source data once rather than call MakeValid() at runtime?
    – Ben Thul
    Oct 15, 2021 at 19:31
  • @BenThul I followed your suggestion about the declaration of a @point variable, so I've created 2: 1 for the GEOMETRY type point and 1 for the GEOGRAPHY type point. About the MakeValid() function, I have to call it at runtime because in my case the polygon table is of type GEOMETRY and I cannot convert it. Oct 18, 2021 at 10:13
  • I'd still advocate fixing the source data unless there's a compelling reason not to. You can still store it as a geometry DT.
    – Ben Thul
    Oct 19, 2021 at 20:27
  • @BenThul Maybe I'm wrong, but the possible solutions I see are: convert the GEOMETRY column to a GEOGRAPHY one, that unfortunately in my case is not possible because of other uses of the table elsewhere, otherwise, create a new GEOGRAPHY column on the table, that would be a "duplicate" of the geometries contained in the GEOMETRY column. Do you have any other suggestion? Oct 20, 2021 at 8:07
  • Without knowing why the current data isn't valid, it's hard to be prescriptive. I don't think you need to full on convert the data to geography, but you could possibly do something like "convert to geography → makevalid → convert back to geometry". Semi-related, I always squint my eyes when geography data is stored as geometry. Maybe it's the right thing for you, but in every instance I've encountered it's because the existence of geography was unknown at time of authorship.
    – Ben Thul
    Oct 20, 2021 at 17:02

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.