Re: (stupid) Question about indexing

From: Job Miller <jobmiller_at_yahoo.com>
Date: Thu, 12 Jul 2007 06:45:48 -0700 (PDT)
Message-ID: <308492.72635.qm@web53903.mail.re2.yahoo.com>

 

  probably more than you were looking for in regards to lat/lon queries, but here you go.    

>2. if you were to try, how would you know the exact value and precision? -109.92638889 or -109.92639,
   

  you would use a locator nearest neighbor query, and just have the user put in a point and oracle would return the nearest point to the location they put in.    

  if they want to do a box search, they would just use sdo_filter operator for that query given a user defined region of interest, if they want all points within 5 miles, they use a within distance operator.    

> 3. I don't believe that indexes are used when > or < symbols are used in sql, am I correct?
   

  yes, they are used, but for this type of location query ideally, you would utilize locator data type to store lat/lon and than any queries where a user specifies a box(geography) of interest would perform much better with a spatial r-tree index vs b-trees on lat/lon numeric data types.    

  given lower-left (x1,y1), upper right (x2,y2) you can do this:    

  select * from table
  where
  lon between x1 and x2
  and
  lat between y1 and y2    

  given indexes on lat,lon, oracle will use both indexes to find the box of interest if stats/cbo indicate it is a good idea.    

  but, if all the geometries in the table are just points, performance will be much better if the query was instead a:    

  SELECT * FROM table A
   WHERE SDO_FILTER(a.locations,
    SDO_GEOMETRY(2003, NULL, NULL,

      SDO_ELEM_INFO_ARRAY(1,1003,3),
      SDO_ORDINATE_ARRAY(:x1,:y1, :x2,:y2))

    ) = 'TRUE';   --
  if you can't change the app model to add a "location" attribute with a sdo_geometry data type, there are many other approaches to making this work.    

  you can create a function that returns a location object, and create a spatial index on the result of the function.    

  for example, if your data model looks like this:    

create table LONG_LAT_TABLE (longitude number, latitude number, name varchar2(32)); insert into LONG_LAT_TABLE values (10,10, 'Place1'); insert into LONG_LAT_TABLE values (20,20, 'Place2'); insert into LONG_LAT_TABLE values (30,30, 'Place3');    

  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;
/

  Update the USER_SDO_GEOM_METADATA view, using dot-notation to specify the schema name and function name. The following example specifies SCOTT.GET_LONG_LAT_PT(LONGITUDE,LATITUDE) as the COLUMN_NAME (explained in Section 2.6.2) in the metadata view.

• Set up the metadata entry for this table. -- The column name sets up the function on top -- of the two columns used in this function, -- along with the owner of the function. insert into user_sdo_geom_metadata values('LONG_LAT_TABLE', 'scott.get_long_lat_pt(longitude,latitude)', sdo_dim_array( sdo_dim_element('Longitude', -180, 180, 0.005), sdo_dim_element('Latitude', -90, 90, 0.005)), 8307); Create the spatial index, specifying the function name with parameters. For example:

create index LONG_LAT_TABLE_IDX on LONG_LAT_TABLE(get_long_lat_pt(longitude,latitude)) indextype is mdsys.spatial_index;   Perform queries on the data.    

  select name from LONG_LAT_TABLE a
   where sdo_filter(get_long_lat_pt(a.longitude,a.latitude),

      SDO_GEOMETRY(2003, 8307, NULL,
      SDO_ELEM_INFO_ARRAY(1,1003,3),
      SDO_ORDINATE_ARRAY(:x1,:y1, :x2,:y2))

    ) = 'TRUE';

---

  ideally you would add the sdo_geometry column to the table, you can always extract the lat/lon from a point geometry in a view for apps that can't or don't want to handle an object being returned, or you can maintain the geometry via a shadow column/trigger and than you can create the spatial index on the real column, instead of the function based approach above.    

  The docs cover all of this (and much of this is a cut and paste with mods from here:   http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14255/toc.htm              

---

Moody friends. Drama queens. Your life? Nope! - their life, your story.  Play Sims Stories at Yahoo! Games.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jul 12 2007 - 08:45:48 CDT