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))
Comments
Post a Comment