0 Replies Latest reply: May 18, 2012 9:02 AM by cptkirkh RSS

    get around sdo_within_distance

    cptkirkh
      I want to utilize the sdo_within_distance but i realize that it is really slow since it doesn't use the spatial index. SO i found the webpage where it talks about using the cosine_bufs table. I thought i could basically do the same thing but instead of creating a whole table just add a new column to my current table and insert the what is the output of using sdo_buffer.

      here is the initial page describing how to not use sdo_within_distance.

      http://docs.oracle.com/cd/B13789_01/appdev.101/b10826/sdo_index_query.htm#i1005386

      my data is basically a table with locations and one sdo_geomotry column with long and lat like this below and one with customers with a sdo_geometry column of long and last for their address. I have created spatial indexes on both columns.

      loc_geo_location = (2001, 8307, (-101.8552, 33.587792, ), , )
      cli_main_geo_location = (2001, 8307, (-99.4112, 35.214412, ), , )

      then using that info i run this query.

      update location_test c set LOC_GEO_BUFFER_100 = (SELECT sdo_geom.SDO_BUFFER (A.LOC_GEO_LOCATION, B.diminfo ,100,'arc_tolerance=0.05 unit=mile' )
      FROM LOCATION_TEST A,USER_SDO_GEOM_METADATA B
      WHERE TABLE_NAME = 'LOCATION_TEST' AND COLUMN_NAME = 'LOC_GEO_LOCATION'
      AND LOC_RID = 11715)

      then loc_geo_buffer_100 = (2003, 8307, , (1, 1003, 1, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ), (-101.8552, 35.0387454303112, -101.965943399719, 35.0358333301785, -102.076226672234, 35.0271091322556, -102.185591902767, 35.0126090855068, -102.293585585148, 34.9923934104449, -102.399760785774, 34.9665460090274, -102.503679259767, 34.9351740615616, -102.604913504506, 34.898407513615, -102.70304873651, 34.8563984567017, -102.797684778758, 34.8093204072418, -102.888437846702, 34.757367488943, -102.974942222584, 34.700753524341, -103.056851809083, 34.63971104174, -103.133841554804, 34.5744902042174, -103.20560874564, 34.5053576676961, -103.27187415759, 34.4325953753404, -103.33238306805, 34.3564992957051, -103.386906124109, 34.2773781121534, -103.435240067688, 34.1955518710791, -103.477208318674, 34.1113505964095, -103.512661418318, 34.025112877749, -103.541477336243, 33.9371844393493, -103.563561645279, 33.8479166968698, -103.578847569117, 33.7576653086261, -103.587295908419, 33.6667887277348, -103.588894851477, 33.5756467612371, -103.583659675929, 33.4845991419514, -103.571632348239, 33.3940041184543, -103.552881027808, 33.3042170682394, -103.527499482587, 33.21558913875, -103.495606423026, 33.1284659206387, -103.457344761032, 33.0431861572689, -103.412880800429, 32.9600804941507, -103.362403365109, 32.8794702716951, -103.306122870838, 32.801666364378, -103.244270346267, 32.7269680691308, -103.177096408423, 32.6556620455178, -103.104870197546, 32.5880213100231, -103.027878275796, 32.5243042865451, -102.946423494007, 32.4647539149951, -102.86082383028, 32.4095968197046, -102.771411203933, 32.3590425391713, -102.678530267947, 32.3132828185111, -102.58253718282, 32.2724909658328, -102.48379837443, 32.2368212736085, -102.382689278279, 32.2064085059832, -102.279593072307, 32.1813674528397, -102.174899400218, 32.1617925513139, -102.069003087168, 32.1477575753453, -101.962302849484, 32.1393153937323))


      if i only update one or two locations and run the query below it runs fast.
      i
      SELECT count(a.CLI_RID) /*,A.CLI_FIRST_NAME,A.CLI_LAST_NAME,A.CLI_MAIN_STATE*/
      FROM clients_test a, LOCATION_TEST b,
      TABLE(SDO_JOIN('CLIENTS_TEST', 'CLI_MAIN_GEO_LOCATION', 'LOCATION_TEST', 'LOC_GEO_BUFFER_100', 'mask=ANYINTERACT')) c
      WHERE c.rowid1 = a.rowid AND c.rowid2 = b.rowid
      AND b.LOC_RID = 11715

      if i go and run the update statement for the whole state of texas the select statement grinds to a halt again. Is my thinking wrong that i can just add the output of sdo_buffer to a new column for each row and index it and it will run my queries fast? Thanks.