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 (
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
- As said, from the test I've made it seems that
TownOfPointare working properly, even if they're making "mixed" use of the
GEOMETRYdata type and Lat/Lon coordinates, but is it really correct or is there another (better) way to do that?
- How can I make the
DistanceFromTownfunction 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.