SQL Server中STContains等地理函数返回错误结果求助

我用SQL Server存储客户经纬度信息，通过Leaflet在地图上展示；同时用Leaflet绘制城市区域多边形，将其以geography类型存储在另一张SQL表中。我编写了如下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))

1. 经纬度顺序不匹配（核心错误） #

Leaflet输出的坐标顺序是经度在前，纬度在后，但你创建点时用了geography::Point(纬度, 经度)，导致点与多边形的坐标逻辑完全错位，这是判断结果错误的主要原因。

修复：交换经纬度顺序创建点：

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

2. 多边形方向问题 #

SQL Server的geography类型要求多边形顶点按逆时针方向排列（针对WGS84坐标系4326），如果方向错误，STContains会误判点在多边形外部。可以用ReorientObject()修正：

SET @polygon = @polygon.ReorientObject();

3. 冗余查询与游标优化 #

• 内部游标中已经获取了@coordinates，无需重复查询表，直接使用即可：

  SET @polygon = @coordinates; -- 已为geography类型，无需转文本解析
  

• 嵌套游标性能极差，建议换成集合查询替代：

  SELECT COUNT(*)
  FROM Tbl_CustomerAddresses ca
  CROSS JOIN Tbl_GeographicalAreas ga
  WHERE ca.Latitude IS NOT NULL 
    AND ca.Longitude IS NOT NULL
    AND ga.Coordinates.STContains(
          geography::Point(cast(ca.Longitude as float), cast(ca.Latitude as float), 4326)
        ) = 1;
  

内容的提问来源于stack exchange，提问作者Behnam Faghih