Oracle Spatial vs ArcGIS: Wrong results with spatial join

Question

I have some troubles with a spatial join in Oracle db (version 12c):

I have a table with points with SHAPE in SDO_GEOMETRY format. Now I want to select these points if they are in a polygon via spatial join. But in Oracle DB I got a lot of wrong selections, so I did the same in ArcGIS and there the selection works fine. Any ideas?

Here are the steps what I have done:

Steps in Oracle DB (SQL Developer)

1. Create table with attributes (addresses only without geometry)

create table TABLE_NAME as select distinct address_id, street, house_number, city from ADDRESS_TABLE

2. Add SHAPE-column with geometry in SDO_GEOMETRY format

alter table TABLE_NAME add SHAPE SDO_GEOMETRY

insert into TABLE_NAME (SHAPE) select SHAPE from ADDRESS_TABLE

3. create spatial index on SHAPE-column

create index INDEX_NAME ON TABLE_NAME (SHAPE) INDEXTYPE IS MDSYS.SPATIAL_INDEX

4. Spatial join with polygon

select a.address_id from TABLE_NAME a, POLYGON b where SDO_INSIDE (a.shape, b.shape) = 'TRUE'

so after these steps I got a selection with all my address-points in the polygon, and this works fine for I would say 80 % of the data. But there are also many address-points which are not selected but although they are inside this polygon. The address-points and the polygon have the same coordinate-system (EPSG 4326). The polygon have also a spatial index.

Here is a screenshot: If the address-points are green, they are inside the blue polygon (everything looks fine)... If the address-points are red, they are outside the blue polygon (here you could see some points are red but although they are inside the polygon, so they should have been green)

Answer 1

One oddity in your code: first you create your table with a set of addresses, but without the geometry column. Then you add the geometry column to that table. Then you insert another set or rows with geometries only. Your select will always return NULL for ADDRESS_ID since that column is empty for the rows that contain a geometry.

Then about the results you get: one possible (probable) cause is that the polygons you use are in an invalid orientation. Oracle follows the OGC rule that polygons must be oriented CCW (counter clockwise). If they are oriented CW then you will get wrong results.

You need to validate your polygons: see if you get any errors (there could be others) then correct those errors.

You also need to fix the code that builds your address point table.