Home » SQL & PL/SQL » SQL & PL/SQL » randomness sdo_buffer & nearest neighbour
randomness sdo_buffer & nearest neighbour [message #230530] Thu, 12 April 2007 03:11 Go to next message
bibber
Messages: 38
Registered: August 2006
Member
Hi,

We have an web application containing a map, which can show various data-layers. One function gives 'random' errors, which is related with oracle:
the map shows circles of 200m and 300m width around certain locations. Secondly, buildings are shown which have an elevator (e.g.). When an user clicks:
- xy coordinate is returned to the database
- distance to nearest centre of a circle is determined
- based on distance a sdo_buffer is returned
- spatial query selects all buildings inside buffer
- addresses of the buildings are send back to the user

The problem is that sometimes it returns the right amount of addresses, but sometimes it states 'no items selected'. Although I click on the exact same location!

The sql-statements use 'sdo_buffer' and 'nearest-neighbour': are the statements implemented correctly? Or are there caching-issues when using 'sdo_buffer' or 'nearest-neighbour'? I'm looking for an answer for the randomness. Maybe I can use a different function, rather than sdo_buffer

SELECT g.obj_id
FROM   wzw_pand g
WHERE  sdo_relate (g.centroid, geef_buffer(120133, 488623), 'mask=INSIDE querytype=WINDOW') = 'TRUE'
AND   (g.ind_nultrede = 'J');

------------------------------------------------------

CREATE OR REPLACE FUNCTION GEEF_BUFFER
(      p_xcoord number
,      p_ycoord number) return mdsys.sdo_geometry
is
  --
  cursor c1
  is
  SELECT p.centroid.sdo_point.x AS xcoord,
         p.centroid.sdo_point.y AS ycoord,
         sdo_nn_distance(1) AS nn_afstand
  FROM   wzw_pand p
  WHERE  ind_afroep = 'J' -- geen index op deze kolom!
  AND    sdo_nn (p.centroid,
                 mdsys.sdo_geometry(2001,
                                    90112,
                                    mdsys.sdo_point_type(p_xcoord, p_ycoord, NULL),
                                    NULL,
                                    NULL),
                                    'sdo_batch_size=0 unit=meter', 1) = 'TRUE'
and rownum = 1
order by nn_afstand;
  --
  l_afstand number;
  l_bufferwaarde number;
  l_geometrie mdsys.sdo_geometry;
  --
begin
  --
  l_afstand := null;
  l_bufferwaarde := null;
  l_geometrie := null;
  --
  for r1 in c1 loop
   l_afstand := r1.nn_afstand;

    if l_afstand <= 200 then
     l_bufferwaarde := 200;

     else if l_afstand > 200 and l_afstand <= 300 then
      l_bufferwaarde := 300;

      else l_bufferwaarde := 0.001;

     end if;
    end if;

   l_geometrie := mdsys.sdo_geom.sdo_buffer(mdsys.sdo_geometry(2001,
                                                               90112,
                                                               mdsys.sdo_point_type(r1.xcoord, r1.ycoord, NULL),
                                                               NULL,
                                                               NULL), l_bufferwaarde, 0.5);

  end loop;
  --
  return (l_geometrie);
end geef_buffer;
/

Re: randomness sdo_buffer & nearest neighbour [message #230678 is a reply to message #230530] Thu, 12 April 2007 10:22 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I don't know anything about mdsys or the functions that you are using. But with that said, your use of where rownum = 1 doesn't look right, and it would be where I would start my investigation. Consider:

MYDBA@orcl > create table test (a) as select rownum from all_objects where rownum <= 10;

Table created.

MYDBA@orcl > select * from test order by a;

         A
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

MYDBA@orcl > select * from test order by a desc;

         A
----------
        10
         9
         8
         7
         6
         5
         4
         3
         2
         1

10 rows selected.

MYDBA@orcl > select * from test where rownum = 1 order by a desc;

         A
----------
         1

1 row selected.

MYDBA@orcl > select * from (select * from test order by a desc) where rownum = 1;

         A
----------
        10

1 row selected.

Re: randomness sdo_buffer & nearest neighbour [message #230772 is a reply to message #230678] Thu, 12 April 2007 14:42 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
another thing that can cause this is when you use connection pooling and package state isn't cleared when a connection is re-used by a new client. Global variables set by previous clients could be used in your logic. If you use MOD PL/SQL make sure it's set to stateless (the conf file setting in 9iAS is PlsqlSessionStateManagement StatelessWithResetPackageState)

see DBMS_SESSION.RESET_PACKAGE;

http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sessio.htm#i1010767

Previous Topic: DBMS_JOB.SUBMIT
Next Topic: Web service with PL/SQL Server Pages
Goto Forum:
  


Current Time: Sat Dec 03 11:59:55 CST 2016

Total time taken to generate the page: 0.07426 seconds