Ho to do UNION on two geometry tables?

Question

I try:

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

  UNION

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

But got error:

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

Is another way to union tables with geometry columns exists?

Answer 1

There are two approaches,

1) Use UNION ALL instead of UNION ,

we know there is difference between them, but maybe it is okay with your need.

2) Use CAST function for converting geometry to varbinary(MAX)

as next:

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

Result: (one Record displayed via using UNION that avoid dublicated records)