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,...

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)),...

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...

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,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...

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...

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....

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...