SRIDs do not match for the two geometries in Oracle Spatial

Question

I am trying to resolve where some points from Point Geometry lying outside the Polygon geometry. I want to filter those points which are lying outside the polygon geometry.

I am getting error where SRIDs doesn't match in Oracle Spatial SQL Query. I have two tables viz, Table 1(POS) and Table 2(Territory). The POS table contains the points geometry information and the Territory table contains the polygon geometry information.

Oracle SQL Query:

select sdo_geom.sdo_intersection(x.geoloc,null,
 y.geom1 ,null ,0.005) from 

( select geoloc from Territory b  where  b.territory = 'England') x,
(

select sdo_aggr_convexhull  (
         sdoaggrtype (
           sdo_geometry(2001, 8192, sdo_point_type(p.longitude,p.latitude,  NULL), NULL, NULL),
           0.5
         )
       ).sdo_srid  as geom1
from POS p where p.POS_NAME in(
'LONDON-CENTRAL','LONDON-NORTH','LONDON-SOUTH','LONDON-EAST','LONDON-WEST'
)) y ;

Error while executing the above query:

ORA-13037: SRIDs do not match for the two geometries
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 8
ORA-06512: at "MDSYS.SDO_3GL", line 1657
ORA-06512: at "MDSYS.SDO_GEOM", line 1458
ORA-06512: at "MDSYS.SDO_GEOM", line 1527
13037. 00000 -  "SRIDs do not match for the two geometries"
*Cause:    A Spatial operation is invoked with two geometries where
           one geometry has an SRID and the other geometry does not have an SRID.
*Action:   Make sure that the spatial operations are invoked between two
           geometries with compatible SRIDs.

I have checked other options like searchWithin but I have rule out because some points were lying on the Polygon border and they were excluded with SearchWithin.

POS Table description

Name         Null     Type           
------------ -------- -------------- 
POS_NAME              VARCHAR2(146)  
LATITUDE              NUMBER         
LONGITUDE             NUMBER         
SE_CODE_NAME          VARCHAR2(106)  
MI_STYLE              VARCHAR2(254)  
MI_PRINX     NOT NULL NUMBER(10)     
GEOLOC                SDO_GEOMETRY()

and Territory Table

Name      Null     Type           
--------- -------- -------------- 
T_ID               NUMBER         
TERRITORY          VARCHAR2(78)   
MI_STYLE           VARCHAR2(254)  
MI_PRINX  NOT NULL NUMBER(10)     
GEOLOC             SDO_GEOMETRY()

Types of SRIDs in these table

select * from user_sdo_geom_metadata where table_name in ('POS','TERRITORY')

TABLE_NAME  COLUMN_NAME   DIMINFO                                                               SRID
----------  -----------   -------------------------------------------------------------------   -----
POS         GEOLOC        MDSYS.SDO_DIM_ARRAY([MDSYS.SDO_DIM_ELEMENT],[MDSYS.SDO_DIM_ELEMENT])  8192
TERRITORY   GEOLOC        MDSYS.SDO_DIM_ARRAY([MDSYS.SDO_DIM_ELEMENT],[MDSYS.SDO_DIM_ELEMENT])  8192

Please help me to filter the points which are outside the Territory Polygon.

Answer 1

This is an old questtion but I got here trying to find a solution to a similar problem so I'll share my solutions:

I have two solutions for this problem. One is adding an srid to the table that is missing one, or removing the srid from the one that has an srid. Basically make sure the SRID of the two tables match.

Another way to do this (may be very slow) is to give one geometry a matching SRID without altering the table. This can be done by converting one of them into WKT format and using a SDO_geometry constructor to create a geometry with desired SRID.

SDO_GEOMETRY(SDO_UTIL.TO_WKTGEOMETRY(MyGeometry), SRID)

You can then use your created geometry to do spatial analysis.