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)
- Create table with attributes (addresses only without geometry)
create table TABLE_NAME as select distinct address_id, street, house_number, city from ADDRESS_TABLE
- 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
- create spatial index on SHAPE-column
create index INDEX_NAME ON TABLE_NAME (SHAPE) INDEXTYPE IS MDSYS.SPATIAL_INDEX
- 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)