我正在使用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))

推荐答案

你的一些多边形(如你展示的例子)的方向是错误的.它们仍然是valid,只是内部和外部都被翻转了.您可以判断它们的面积,如果它们太大,则重新定位它们.

这里绝对不需要光标.您可以使用CROSS APPLY进行中间计算.

SELECT
  insideCOUNT = COUNT(*)

FROM Tbl_CustomerAddresses ca
CROSS APPLY (
    SELECT point =
      geography::Point(CAST(ca.latitude AS float), CAST(ca.longitude as float), 4326)
) v1

JOIN (
    Tbl_GeographicalAreas ga
    CROSS APPLY (
        SELECT polygon =
          IIF(
            ga.Coordinates.STArea() > 255036000000000,
            ga.Coordinates.ReorientObject(),
            ga.Coordinates
          )
    ) v2
  )
  ON v2.polygon.STContains(v1.point) = 1

WHERE ca.Latitude IS NOT NULL
  AND ca.Longitude IS NOT NULL;

Sql相关问答推荐

SQL查询以创建手头的流动余额?

Postgresql -如何计算刷卡和刷卡时间

如何在SQL查询中只比较日期时间的年份和月份(而忽略日期比较)?

在SQL中创建一个计数器,根据BigQuery/SQL中的条件递归地添加行值

使用SQL创建列出两个时间戳之间小时数的列

Select 非重复值并按条件排除行

使用generate_series()时,LEFT联接缺少日期/间隔

删除所有订单中可用的重复值

显示十进制列,但尽可能显示为整数

为什么SQL in中的空子查询有时被视为null

根据日期 Select ID 的上一条记录

在 PostgreSQL 中生成时间序列查询

显示所有组并计算给定组中的项目(包括 0 个结果)

不同计数的 Postgres PIVOT 表

DbUp for sqlserver 在 dbo 授权下为非 dbo 用户创建架构

试图找到两个身份列表的交集(列表的长度不同),但获取列 id 不明确?

Postgres存在限制问题「小值」

比使用NOT EXISTS更高效的SQL删除方法是什么?

如何显示最常引用条目的详细信息

ACCESS SQL - 有没有办法使用通配符仅 Select 字段的特定部分?