1 2 Previous Next 21 Replies Latest reply: Apr 5, 2013 6:10 PM by 648263 Go to original post RSS
      • 15. Re: sdo_nn to fetch points from point cloud and georaster
        mhorhamm
        Caching should not be required, and your machine is fine.

        Your time for creating the PC is "almost 6 sec", as compared to my 3.82 sec. ok.
        Your time for querying is "0.704 sec", as compared to my 0.25.

        So, your machine is a little slower, and we might look into the config, but the query is still 0.0023 sec / resulting point. For queries with larger result sets, it will be faster. But where do the "100ms per point" you mentioned, come from? Or was this referring to something else?
        • 16. Re: sdo_nn to fetch points from point cloud and georaster
          648263
          The 100milli second i mentioned in my post comes from a log file that finds the intersecting points within a circle. Here is a part of the procedure :

          this log was run against 250,000 points and the code snippet runs through all the points and finds the neighboring points within 35 unit distance.
          ====================================
          select systimestamp into prevtime from dual;

          select sdo_pc_pkg.to_geometry(
          a.points, -- point LOB
          a.num_points, -- # of points in the LOB
          3, -- total dimensionality
          null -- SRID
          ) bulk collect into idw_neighbour_coll
          from table( select sdo_pc_pkg.clip_pc(
          b.pc, -- Input Point Cloud object
          SDO_GEOMETRY(2003,null,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,4),MDSYS.SDO_ORDINATE_ARRAY(x,y-rad,x+rad,y,x,y+rad)),
               null, null, null, null) from base b where rownum=1) a;

          select systimestamp into systime from dual;

          dbms_output.put_line(systime-prevtime);
          ========================================
          The log outputs two time values; the first one is the time of running the above code snippet.

          +000000000 00:00:00.072000000
          +000000000 00:00:00.057000000
          point:0,71,.0316042063380410587737596683447352485907,.0316

          +000000000 00:00:00.071000000
          +000000000 00:00:00.056000000
          point:0,72,.0317384495984179213071959133258833930955,.03173

          +000000000 00:00:00.071000000
          +000000000 00:00:00.053000000
          point:0,73,.0319347114786714036945052474331669428833,.03193

          +000000000 00:00:00.069000000
          +000000000 00:00:00.055000000
          point:0,74,.0321286530705700354468666592787628358586,.03212

          +000000000 00:00:00.099000000
          +000000000 00:00:00.056000000
          point:0,75,.0323034135760429520797206823153100602587,.0323

          +000000000 00:00:00.069000000
          +000000000 00:00:00.060000000
          point:0,76,.0324442058283896508015270822049241491092,.03244

          +000000000 00:00:00.070000000
          +000000000 00:00:00.065000000
          point:0,77,.0326555313773882293393627948330247780067,.03265

          +000000000 00:00:00.068000000
          +000000000 00:00:00.069000000
          point:0,78,.0328799074538467888397320656717338927668,.03287

          ======
          So, for most part the time on an avg was around 0.090 sec or 90 milli sec . So, i roughly posted as 100ms.

          However, this is still too slow for me. When i am doing interpolation for almost million points this will run for hrs. So, i was wondering, if i can speed this stuff so that i can bring the total execution time to may be few minutes.

          Thanks,
          Bal

          Edited by: user645260 on Apr 5, 2013 11:50 AM
          • 17. Re: sdo_nn to fetch points from point cloud and georaster
            mhorhamm
            Hmm, now I understand what you mean with "per point" in your application. You run a CLIP_PC query around a given point, which might return 500 points in that vicinity, taking 0.07 sec. But you are not referring to "0.07 sec per 500 resulting points", but rather "0.07 sec per query center", meaning "0.07 sec per CLIP_PC query".

            Yes, an individual CLIP_PC query might reasonably take 100ms or so. Depending on many factors, it might be faster, of course, but your approach of performing a separate CLIP_PC query, for each center point, out of a large point set, is bound to take a lot of time. A better approach may be to:

            - first group your center points into tight clusters (if possible)
            - perform one CLIP_PC per cluster
            - find the nearest N points, for each center point, within that cluster

            This depends on the set of center points not being spatially too sparse. If these center points are very sparsely clustered, then any query window containing multiple center points might yield millions of resulting points in the CLIP_PC, which would be very expensive in aggregate.

            Do I understand correctly that your application actually tries to find the nearest N neighbors, for each point, IN THE ENTIRE POINT CLOUD? Or for each point, in a separate and much smaller set of center points?
            • 18. Re: sdo_nn to fetch points from point cloud and georaster
              648263
              Yes, it finds the nearest neighbors for each point in the point cloud.

              I am also thinking of parallel execution of the query to reduce time. Do you have any suggestions as to which approach will be better -

              1. running separate tasks in parallel in one user session.
              2. running the same task in multiple user session.

              Thanks,
              Bal
              • 19. Re: sdo_nn to fetch points from point cloud and georaster
                mhorhamm
                There is a better approach:

                (1) for each point cloud block:
                (1a) have your app load that block into main memory
                (1b) for each center point within that block:
                (1b1) find the nearest N points to that center point, from among all the points within that block (can be done, efficiently, in main memory)

                The problem with that simplified approach is that some of the points bordering each block may have some nearer neighbors in another block than within their own block. But we can determine that risk, for each point, by comparing its distance from the block border with the distance from the nth-nearest neighbor within the block. Only if the border is closer than the nth-nearest neighbor, there might be a nearer neighbor in another block. Some additional logic could find that one...

                Might this solve your requirements?
                • 20. Re: sdo_nn to fetch points from point cloud and georaster
                  mhorhamm
                  That logic can be parallelized, by the way.
                  • 21. Re: sdo_nn to fetch points from point cloud and georaster
                    648263
                    ok.

                    but how to load point data into memory. Doesn't oracle automatically loads the data into memory on select statement. Like when i execute

                    select sdo_pc_pkg.to_geometry(
                    a.points, -- point LOB
                    a.num_points, -- # of points in the LOB
                    3, -- total dimensionality
                    null -- SRID
                    ) bulk collect into idw_neighbour_coll
                    from table( select sdo_pc_pkg.clip_pc(
                    b.pc, -- Input Point Cloud object
                    SDO_GEOMETRY(2003,null,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,4),MDSYS.SDO_ORDINATE_ARRAY(x,y-rad,x+rad,y,x,y+rad)),
                         null, null, null, null) from base b where rownum=1) a;

                    will oracle not keep the block data from BASE table in memory for all subsequent point in the loop. If not then how to explicitly load the data in memory.

                    Thanks,
                    Bal
                    1 2 Previous Next