SQL filter to show one layer only if another layer is missing

Question

I have two oracle tables that represent buildings in my town. The first table represent buildings that are surveyed and measured accurately but doesn't cover all the parts of the town while the second table represent not so accurately measured buildings but covers all the town.

table_1 - accurate data - partial coverage table_2 - not accurate data - full coverage

I want to write some sort of an SQL filter to show the data from table_2 only if no data in table_1 are present in that area. In other words, I want the whole town to be covered by buildings and to show all the areas that have the accurate buildings and fill up the rest of the areas with the not so accurate buildings!

The tables are stored in a Oracle 11g database. The map application I am using is built on MapServer and the layers are basically defined in XML files that accepts SQL filters.

So far, I have done the following:

select table_2.* from table_2, table_1 WHERE SDO_ANYINTERACT(table_2.geom, table_1.geom)= 'TRUE';

The problem with this method is that it gives me the places of the inaccurate buildings when it interacts with the accurate buildings. What I really want is to set the SDO_ANYINTERACT to 'FALSE' to get the inaccurate buildings in the places where no interaction is occurring between the buildings but of course Oracle gives error when setting that to 'FALSE'.

Any suggestions?

Answer 1

Assuming both tables use some building ID that is the same for a building in either table, something like

SELECT [columns] FROM table_1
UNION
SELECT [columns] FROM table_2 WHERE NOT EXISTS (SELECT 1 FROM table_1 WHERE table_1.BuildingID = table_2.BuildingID) 

Answer 2

You have to take a look at your two data sets and find out some rule that defines if two buildings is the same.

For instance you can check if the centroid of one epolygon is inside a polygon i the other layer.

I am not familliar with the functions in Oracle vut I guess it is about the same as PostGIS.

So, if you can use the method above it woild look something like this in postgis:

SELECT COALESCE(a.geom,b.geom) geom FROM accurateTable a FULL JOIN draftTable b ON ST_Intersects(ST_Centroid(a.geom),b.geom)