时间戳

首页 > 解决方案 > 基于点位于给定多边形内将多边形表连接到点表时出错

sql-server - 基于点位于给定多边形内将多边形表连接到点表时出错

问题描述

我试图实现的总体目标是基于一个表中的一个点（从以英尺为单位的 xy 坐标创建）将两个表连接在一起，该点落在另一个表的多边形内。预期的结果是记录和它所在的多边形的名称。执行查询时，返回以下错误，总结如下：

执行用户定义的例程或聚合“几何”期间发生 .NET Framework 错误：System.FormatException：24114：输入已知文本 (WKT) 中的标签 395 无效。<

由于不熟悉 SQL 中的几何数据，我不能完全确定我是否走在正确的道路上，所以任何建议都将不胜感激。

我试图加入的多边形表是一个临时表，多边形被格式化为几何图形。通过创建空间输出，单独运行以下代码按预期执行。

抱歉，我的手机似乎无法进行代码格式化，并且不支持我的工作计算机的浏览器。

DECLARE @Hex1 TABLE    (PolyGeom geometry, Hex varchar(6))                 

INSERT INTO @Hex1 
  Values
  (geometry::STPolyFromText('Polygon((7598795.05553838 734372.656,7598217.70526919 735372.656,7597063.00473081 735372.656,7596485.65446162 734372.656,7597063.00473081 733372.656,7598217.70526919 733372.656,7598795.05553838 734372.656))',0),1),
(geometry::STPolyFromText('Polygon((7602259.15715352 734372.656,7601681.80688433 735372.656,7600527.10634595 735372.656,7599949.75607676 734372.656,7600527.10634595 733372.656,7601681.80688433 733372.656,7602259.15715352 734372.656))',0),2),
(geometry::STPolyFromText('Polygon((7605723.25876865 734372.656,7605145.90849947 735372.656,7603991.20796109 735372.656,7603413.8576919 734372.656,7603991.20796109 733372.656,7605145.90849947 733372.656,7605723.25876865 734372.656))',0),3)’’’

多边形表连接到的表没有点，因此创建了一个表，其中包含一个包含计算点的字段。通过返回带有点的记录，单独运行以下代码可以按预期工作。

INSERT INTO #Points (Primary_Key, geom)
select a.rID, geometry::STGeomFromText('POINT('+convert(varchar(20),a.x_coordinate)+' '+convert(varchar(20),a.y_coordinate)+')',0) as geom
from data_a a'''

表连接如下所示

 WITH CTE1 AS --Due the number of polygons exceeding insert limits, multiple tables are created and unioned in a CTE

  ( Select*
  From @Hex1

  UNION ALL
  Select*
  From @Hex2

   UNION ALL
  Select*
  From @Hex3

   UNION ALL
  Select*
  From @Hex4)

select a.rID, C.Hex
from data_a a --Existing table with x y coordinates

left join #points p  --Joins the point created in points table to the same case in go_data
on a.rID = p.Primary_key

left join CTE1 C   --Joins the hexagon to the point if hex containts the point
on p.geom.STIntersects (c.hex) =1'''

下面是完整的代码，每个表格中的多边形数量减少了

IF OBJECT_ID('tempdb..#Points') IS NOT NULL DROP TABLE #Points

create table #Points (Primary_key numeric identity not null,  geom  geometry)
SET IDENTITY_INSERT #Points ON


INSERT INTO #Points (Primary_Key, geom)
select g.rin, geometry::STGeomFromText('POINT('+convert(varchar(20),a.x_coordinate)+' '+convert(varchar(20),a.y_coordinate)+')',0) as geom
from data_a a
;

DECLARE @Hex1 TABLE 
   (PolyGeom geometry, Hex varchar(6))                 

INSERT INTO @Hex1 
  Values
  (geometry::STPolyFromText('Polygon((7598795.05553838 734372.656,7598217.70526919 735372.656,7597063.00473081 735372.656,7596485.65446162 734372.656,7597063.00473081 733372.656,7598217.70526919 733372.656,7598795.05553838 734372.656))',0),1),
(geometry::STPolyFromText('Polygon((7602259.15715352 734372.656,7601681.80688433 735372.656,7600527.10634595 735372.656,7599949.75607676 734372.656,7600527.10634595 733372.656,7601681.80688433 733372.656,7602259.15715352 734372.656))',0),2),
(geometry::STPolyFromText('Polygon((7605723.25876865 734372.656,7605145.90849947 735372.656,7603991.20796109 735372.656,7603413.8576919 734372.656,7603991.20796109 733372.656,7605145.90849947 733372.656,7605723.25876865 734372.656))',0),3)

DECLARE @Hex2 TABLE   
(PolyGeom geometry, Hex varchar(6))  

INSERT INTO @Hex2 
  Values

  (geometry::STPolyFromText('Polygon((7680201.44349411 721372.656,7679624.09322492 722372.656,7678469.39268654 722372.656,7677892.04241735 721372.656,7678469.39268654 720372.656,7679624.09322492 720372.656,7680201.44349411 721372.656))',0),1000),
(geometry::STPolyFromText('Polygon((7683665.54510925 721372.656,7683088.19484006 722372.656,7681933.49430168 722372.656,7681356.14403249 721372.656,7681933.49430168 720372.656,7683088.19484006 720372.656,7683665.54510925 721372.656))',0),1001),
(geometry::STPolyFromText('Polygon((7687129.64672438 721372.656,7686552.29645519 722372.656,7685397.59591681 722372.656,7684820.24564763 721372.656,7685397.59591681 720372.656,7686552.29645519 720372.656,7687129.64672438 721372.656))',0),1002)

DECLARE @Hex3 TABLE 
 (PolyGeom geometry, Hex varchar(6))  

INSERT INTO @Hex3 
  Values
  (geometry::STPolyFromText('Polygon((7765071.93306498 708372.656,7764494.58279579 709372.656,7763339.88225741 709372.656,7762762.53198822 708372.656,7763339.88225741 707372.656,7764494.58279579 707372.656,7765071.93306498 708372.656))',0),1999),
(geometry::STPolyFromText('Polygon((7768536.03468011 708372.656,7767958.68441092 709372.656,7766803.98387254 709372.656,7766226.63360335 708372.656,7766803.98387254 707372.656,7767958.68441092 707372.656,7768536.03468011 708372.656))',0),2000),
(geometry::STPolyFromText('Polygon((7772000.13629525 708372.656,7771422.78602606 709372.656,7770268.08548768 709372.656,7769690.73521849 708372.656,7770268.08548768 707372.656,7771422.78602606 707372.656,7772000.13629525 708372.656))',0),2001)

WITH CTE1 AS 
  ( Select*
  From @Hex1

  UNION ALL
  Select*
  From @Hex2

   UNION ALL
  Select*
  From @Hex3)

select a.rID, C.Hex
from  data_a a

left join #points p  --Joins the point created in points table to the same case in go_data
on g.rin = p.Primary_key

left join CTE1 C   --Joins the hexagon to the point if hex containts the point
on p.geom.STIntersects (c.hex) =1

标签： sql-servergeometry

解决方案

这不只是一个错字吗？您应该将点几何与多边形的几何相交，而不是与十六进制列相交。

left join CTE1 C   --Joins the hexagon to the point if hex containts the point
on p.geom.STIntersects (c.PolyGeom) =1

推荐阅读