0

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?

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) 
  • That would be perfect if they shared the same ID but, they are totally two different tables that come from two different sources! There is no relation between the tables except that they both contain a building polygon in a specific location and the polygons of course are not identical due to different measurements! – GIS_DBA Nov 11 '14 at 21:45
  • 1
    If building geometries are rather similar you could have a try with spatial operators. Take the inaccurate building polygons only if they do not overlap with the accurate ones. Overlap may be too strict operator so try SDO_ANYINTERACT first. For making the usage with MapServer easier make a view or materialize it into a new table. Remember USER_SDO_GEOM_METADATA row. – user30184 Nov 11 '14 at 22:03
  • Also UNION ALL would be required rather than a straight UNION. You could try a spatial filter in the not exists, eg 'WHERE SDO_ANYINTERACT(table2.geom,SDO_GEOM.SDO_POINTONSURFACE(table1.geom,0.001)) = 'TRUE'` – MickyT Nov 11 '14 at 22:03
  • POINTONSURFACE may not be a good idea. It can fall outside the other building even they overlap. In early days pointonsurface returned the first vertex of outer ring in Oracle but it may be improved in 11g. – user30184 Nov 11 '14 at 22:08
  • @user30184 True and rereading the question, probably very unnecessary. – MickyT Nov 11 '14 at 22:19
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.