sql-server - 如何对两个几何表进行 UNION？

Question

我尝试:

  SELECT 
  ,6 AS ObjectTypeId
  ,[GeomColumn] AS geom
  FROM [dbo].[myTable1]

  UNION

  SELECT 
  ,objTyleId AS ObjectTypeId
  ,[GeomColumn] AS geom
  FROM [dbo].[myTable2]

但出现错误:

The data type geometry cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.

是否存在另一种将表与几何列联合起来的方法？

Answer 1

有两种方法，

1)使用UNION ALL而不是UNION，

我们知道它们之间存在差异，但也许可以满足您的需求。

2) 使用CAST函数将geometry转换为varbinary(MAX)

如下:

Create table #myTable1 (ObjectTypeId int identity , GeomColumn geometry)
Create table #myTable2 (ObjectTypeId int identity , GeomColumn geometry)

INSERT INTO #myTable1 (GeomColumn)  
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));  

INSERT INTO #myTable2 (GeomColumn)  
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));  


select 1 AS ObjectTypeId , Cast( GeomColumn as varbinary(MAX)) geom 
from #myTable1
union 
select ObjectTypeId ,  Cast( GeomColumn as varbinary(MAX)) geom 
from #myTable2


Drop table #myTable1
Drop table #myTable2

结果:(通过使用 UNION 显示一条记录，避免重复记录)