Tricky sql distinct with Sql Geometry field

Question

I have an sql statement that is not compatible with the Sql Geometry type so i am trying to revamp it to make it work.

The basic gist of it is something like this:

Select distinct A,B,C,D,SHAPE FROM TABLE1 UNION SELECT A,B,C,D,SHAPE FROM TABLE2

So the results of a complex distinct statement on two different (but similar) tables is unioned.

The problem is that the SQL Geometry type (the SHAPE field) cannot be included in a distinct statement because it is not a comparable type.

I'm okay with removing the SHAPE field from both of the subqueries that take part in the union. But i want to get it back after those subqueries are parsed. I can get the SHAPE field if i know the value of column A.

So my question is: how can i remove a field from the distinct statements in the two subqueries but then get that field back (along with the rest of the fields) in the result set by joining it in on another column (A)?

Answer 1

You can cast to VARBINARY(MAX), and then cast back.

create table tbl1 ( ID int, a int, b char(3), d geometry );
create table tbl2 ( ID int, a int, b char(3), d geometry, other float );
insert tbl1 (ID, a, b, d) values
    (1, 2, 3, geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0)),
    (2, 3, 3, geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0)),
    (3, 4, 3, geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0)),
    (4, 2, 3, geometry::STGeomFromText('POLYGON ((0 0, 250 0, 150 150, 0 150, 0 0))', 0)),
    (5, 2, 3, geometry::STGeomFromText('POLYGON ((0 0, 350 0, 150 150, 0 150, 0 0))', 0))
insert tbl2 (ID, a, b, d) values
    (6, 2, 3, geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0)), -- same as tbl1.ID=1
    (7, 2, 3, geometry::STGeomFromText('POLYGON ((0 0, 950 0, 150 150, 0 150, 0 0))', 0)),
    (8, 2, 3, geometry::STGeomFromText('POLYGON ((0 0, 350 0, 150 150, 0 150, 0 0))', 0)); -- same as tbl1.ID=5

SELECT A, B, cast(c as geometry) c
FROM
(
    Select A,B,cast(D as varbinary(max)) c FROM tbl1
    UNION
    SELECT A,B,cast(D as varbinary(max)) FROM tbl2
) X;

Answer 2

CREATE VIEW GeometryView1 AS
  SELECT A,B,C,D,SHAPE AS tabId FROM TABLE1 
  UNION SELECT A,B,C,D,SHAPE FROM TABLE2;

CREATE VIEW GeometryView2 AS
  SELECT DISTINCT A,B,C,D FROM (
  SELECT A,B,C,D AS tabId FROM TABLE1 
  UNION SELECT A,B,C,D FROM TABLE2) x;

SELECT DISTINCT v2.A,v2.B,v2.C,v2.D,v1.SHAPE FROM GeometryView2 v2 , GeometryView1 v1
WHERE v2.A=v1.A AND v2.B=v1.B AND v2.C=v1.C AND v2.D=v1.D 

Answer 3

Will this work?

with cte as (
Select distinct A,B,C,D FROM TABLE1
)
select c.A,c.B,c.C,c.D,t.SHAPE 
FROM cte as c
inner join TABLE1 as t
   on c.A = t.A
UNION 
SELECT A,B,C,D,SHAPE FROM TABLE2