2023-09-24

STContains, STIntersects and STWithin return wrong result for geography

I'm using SQL server to store customers location info (longitude and latitude) and using leaflet to show them on map. And also I'm using leaflet for drawing polygon to draw city areas, I store polygons in a another SQL table with geography type, finally by using below query I want to know if a customer is inside an area(polygon) or not:

DECLARE @latitude DECIMAL(25,18);
DECLARE @longitude DECIMAL(25,18);
DECLARE @customerId BIGINT;
DECLARE @geographicalAreaId INT;
DECLARE @coordinates GEOGRAPHY;

DECLARE @isInsideArea BIT;

declare @insideCOUNT int;
SET @insideCOUNT=0;

DECLARE @point geography;
DECLARE @polygon geography;

DECLARE getCustomerGeo_CSR CURSOR FAST_FORWARD READ_ONLY 
FOR

    SELECT DISTINCT Fk_CustomerId,ca.Latitude,ca.Longitude FROM Tbl_CustomerAddresses ca
        WHERE ca.Latitude IS NOT NULL AND ca.Longitude IS NOT NULL;

OPEN getCustomerGeo_CSR; 
FETCH NEXT
FROM getCustomerGeo_CSR
INTO @customerId,@latitude, @longitude

WHILE @@FETCH_STATUS = 0
BEGIN

SET @point = geography::Point(cast(@latitude as float), cast(@longitude as float), 4326);

    DECLARE getGeoArea_CSR CURSOR FAST_FORWARD READ_ONLY 
    FOR
        SELECT ga.GeographicalAreaId,ga.Coordinates               
        FROM   Tbl_GeographicalAreas ga         
    
    OPEN getGeoArea_CSR; 
    FETCH NEXT
    FROM getGeoArea_CSR
    INTO @geographicalAreaId, @coordinates

    WHILE @@FETCH_STATUS = 0
    BEGIN
        
        SET @polygon = geography::STGeomFromText((SELECT Coordinates FROM Tbl_GeographicalAreas WHERE GeographicalAreaId = @geographicalAreaId).STAsText(),4326);


        IF @polygon.STContains(@point) = 1
        BEGIN
            SET @insideCOUNT = @insideCOUNT+1;
        END
        
         FETCH NEXT
         FROM getGeoArea_CSR
         INTO @geographicalAreaId, @coordinates
    END
    CLOSE getGeoArea_CSR;
    DEALLOCATE getGeoArea_CSR;
    

    FETCH NEXT
    FROM getCustomerGeo_CSR
    INTO @customerId,@latitude, @longitude
END
CLOSE getCustomerGeo_CSR;
DEALLOCATE getCustomerGeo_CSR;

print @insideCOUNT;

but I always get wrong value....

here is one of my polygons:

POLYGON ((46.389019 38.033642, 46.388397 38.029045, 46.386788 38.027253, 46.383269 38.024701, 46.37872 38.021252, 46.375308 38.020238, 46.374493 38.021861, 46.375351 38.023179, 46.37445 38.02487, 46.37327 38.025478, 46.371167 38.026543, 46.368678 38.026205, 46.367347 38.02727, 46.364343 38.028318, 46.367648 38.030076, 46.368442 38.030329, 46.3696 38.030329, 46.370029 38.030769, 46.370716 38.032036, 46.371725 38.034014, 46.372476 38.035298, 46.372626 38.035772, 46.372755 38.036819, 46.372819 38.037749, 46.373119 38.038814, 46.373441 38.039219, 46.376252 38.03785, 46.378098 38.037259, 46.380415 38.036853, 46.384835 38.036025, 46.386852 38.035079, 46.387968 38.034301, 46.388805 38.033946, 46.389019 38.033642, 46.389019 38.033642))


No comments:

Post a Comment