SQL View - Union Between Two Feature Classes

[CoryWilliams]

Hi all,
Forgive me here, I've been looking online, but I can't seem to find the answer...

I'm trying to create a view, with the Union operator within my SDE Database to effectively combine multiple feature classes together.

The tables have the same schema but I'm getting the below error message:

Microsoft SQL Server Native Client 10.0: The geometry data type cannot be selected as DISTINCT because it is not comparable.

This is the syntax I'm using for the view...

SELECT * FROM GDA94_LL_NT_GNAF_GNAF_point
UNION
SELECT * FROM GDA94_LL_SA_GNAF_GNAF_point

I've gone through and tried selecting the Shape field out individually and I'm not having much joy at this stage.

I am organising SQL Training for myself, but until then, I've just hit a small brick wall.

Any help would be greatly appreciated!

Cheers,

Cory

[VinceAngelo]

The only way a UNION ALL join could work with ArcGIS is if the registered rowid
columns were created to be non-overlapping.  But this is a database error, indicating
you have the wrong syntax (UNION vs UNION ALL ?)

If the table schemas are identical and the projection is identical, the data should be
in one table; if they aren't, then there isn't any point in UNIONing them.

- V

[CoryWilliams]

Hi Vangelo,
cheers for that. It worked. 🙂

I would normally have all these datasets within the same table. I'm working with the address points data (GNAF) and it's based on different states/territories within Australia.

I'm using a view to create a single table so that when I receive an updated dataset in the future, I only have to update the 8 datasets, which will flow through to the view which and I'm only using it for Dekho's OneSearch capability.

Cheers!! 🙂