我正在使用SQL服务器存储客户的位置信息(经度和纬度),并使用宣传单在 map 上显示它们.我还使用了LEAFLE绘制多边形来绘制城市区域,我将多边形存储在另一个带有地理类型的SQL表中,最后通过使用下面的查询,我想知道客户是否在一个区域(多边形)内:
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;
但我总是得到错误的价值……
这是我的一个多边形:
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))