Table A_SPATIAL has 15 million records with different longitude and latitude values. I also have the column SHAPE SDO_GEOMETRY, which stores SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(LONGITUDE, LATITUDE, NULL), NULL, NULL).
I created the function and index below:
create or replace function get_long_lat_pt(longitude in number, latitude in number) return SDO_GEOMETRY deterministic is begin return sdo_geometry(2001, 8307, sdo_point_type(longitude, latitude, NULL),NULL, NULL); end; / INSERT INTO user_sdo_geom_metadata VALUES( 'A_SPATIAL', -- table 'SHAPE', -- function mdsys.sdo_dim_array( mdsys.sdo_dim_element('LONGITUDE', -180, 180, 0.005), mdsys.sdo_dim_element('LATITUDE', -90, 90, 0.005) ), 8307 -- SRID ); commit; CREATE INDEX A_SPATIAL_SHAPE_IDX ON A_SPATIAL(Shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
Then when I execute the query below, it does a full table scan. Even if I have the index above, the index wouldn't get used because I am using SDO_DISTANCE to wrap around it I suppose. The longitude and latitude provided to the function get_long_lat_pt below are user input and they all vary.
select * from A_SPATIAL where SDO_GEOM.SDO_DISTANCE(SHAPE, get_long_lat_pt(95.224, 31.601), 1, 'unit=MILE') < 20;
1. What is the most optimal way of running such query to perform the best? I guess the full table scan is obvious since I need to calculate the distance, but is there a way to improve performance?
2. Is the index above even useful at all?
Sorry, never looked at Oracle Spatial before, but:
Suppose the user enters longitude and latitude as 95.224 and 31.601 respectively and you want to select from your a_spatial table all the rows within a 20 miles range.
Using equatorial and meridional circumference from Earth - Wikipedia, the free encyclopedia :
40,075.017 km = 24901.46109 mi; 40,007.86 km = 24859.73167 mi (using Kilometers to miles (km to mi) Metric conversion calculator)
you can start by calculating the degrees corresponding to 20 miles distance:
and look only for:
Please recheck the figures (or use those that suit you best)
There is a Spatial forum that should be the best place to ask for help ( https://forums.oracle.com/community/developer/english/oracle_database/spatial/content ), but aren't you trying to emulate the spatial operator SDO_WITHIN_DISTANCE, which should be able to use an appropriate index ?