Search code examples
sqloracleplsqlarcgis

Compare two unrelated tables sql

We're dealing with geographic data with our Oracle database.

There's a function called ST_Insertects(x,y) which returns true if record x intersects y.

What we're trying to do is, compare each record of table A with all records of table B, and check two conditions

condition 1 : A.TIMEZONE = 1 (Timezone field is not unique)
condition 2 : B.TIMEZONE = 1
condition 3 : ST_Intersects(A.SHAPE, B.SHAPE) (Shape field is where the geographical information is stored)

The result we're looking for is records ONLY from the table A that satisfy all 3 conditions above

We tried this in a single select statement but it doesn't seem to make much sense logically

Solution

  • If you want to use the return value from your function in an Oracle SQL statement, you will need to change the function to return 0 or 1 (or 'T'/'F' - some data type supported by Oracle Database, which does NOT support the Boolean data type).

    Then you probably want something like

    select <columns from A>
from   A
where  A.timezone = 1
  and  exists ( select * 
                from   B 
                where  B.timezone = 1
                  and  ST_intersects(A.shape, B.shape) = 1
              )