First of all your polygon is malformed: it does not close. For a polygon, the last point must match the first point. You can detect the error by doing: SQL> select sdo_geom.validate_geometry_with_context (SDO_GEOMETRY(2003, 8307, null, SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(10,10, 10,-10, -10,-10, -10,10)), 0.05) from dual; 13348 [Element <1>] [Ring <1>] 1 row selected....

Refractoring the query using an inner query and a union all seemed to force oracle to use the indexes as expected: SELECT ID,GEOM,LABEL FROM MY_TABLE WHERE ID IN ( (SELECT ID FROM MY_TABLE WHERE sdo_filter(GEOM, mdsys.sdo_geometry(2003,8307,NULL, mdsys.sdo_elem_info_array(1,1003,3), mdsys.sdo_ordinate_array(-180.0,-48.0,-67.0,32.0)), 'querytype=WINDOW')='TRUE') UNION ALL (SELECT ID FROM MY_TABLE WHERE sdo_filter(GEOM, mdsys.sdo_geometry(2003,8307,NULL, mdsys.sdo_elem_info_array(1,1003,3), mdsys.sdo_ordinate_array(157.0,-48.0,180.0,32.0)),...

Your source SRID (1000047) is not a standard EPSG code, neither is it any coordinate system that ships with any version of Oracle: it looks like one you defined yourself. Can you show us the definition (select * from cs_srs where srid=1000047) ? Looking at your input (11.26555560027597,53.87928275206266) - that...

oracle,latitude-longitude,oracle-spatial

The notation you show is not the best one for representing single 2D or 3D points. The common and most efficient way to encode those points is this: SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(51.702814,32.624736,NULL),NULL,NULL) All the GIS tools I have seen use this notation. The one you show is valid too - it just uses...

I got very helpful comments by Rene and Ben. and based on i have solved my issues.. --------------------------- CREATING TABLE -------------------------- create table tbl_location( id int constraint id_pk primary key, unit_code char(2) not null, plot_id number(15) not null, season_cntrl number(2), Ryot_code varchar2(9), share_or_perc_val number(2) not null, plot_no varchar2(18) not null,...

Just compute the intersection between each of the returned geometries and your query window (using SDO_GEOM.SDO_INTERSECTION()), compute the area of each such intersection (using SDO_GEOM.SDO_AREA()) and return the row with the largest area (order the results in descending order of the computed area and only retain the first row). For...

oracle,plsql,spatial,oracle-spatial

Take a look at Oracle Locator, it should be installed by default with all editions of Oracle and gives you access to a subset of the features available in Spatial, including the geometry data types you'd need and hopefully some of the functions too, depending on what you need to...

oracle,oracle-sqldeveloper,oracle-spatial

I've created your table and executed your insert statement OK. Once executed again, I was able to reproduce your problem. The error "SQL Error: ORA-13223: duplicate entry for ROADS.LOCATION in SDO_GEOM_METADATA" definitely points to duplicate data. This trigger MDSYS.SDO_GEOM_TRIG_INS1 just performs a count(*) validation on the following MDSYS table and...