This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Apr 5, 2013 4:10 PM by 648263 RSS

sdo_nn to fetch points from point cloud and georaster

648263 Newbie
Currently Being Moderated
Hi,

I have a requirement to fetch the nearest neighbor from a given point coordinates inside a point cloud/georaster data. Could anyone help me with how can i retrieve a collection of all neighboring points from a given point.

I guess i will have to use sdo_nn operator. So, any help on how to use the operator to achieve the result will be of great help.

Thanks
  • 1. Re: sdo_nn to fetch points from point cloud and georaster
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi Unknown,

    How about the [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_operat.htm#i78067]excellent documentation? That gives excellent help including examples.

    Regards,
    Stefan
  • 2. Re: sdo_nn to fetch points from point cloud and georaster
    648263 Newbie
    Currently Being Moderated
    Thanks stefan.

    I am new to oracle georaster/point cloud. I tried the below statement -

    Georaster example:

    SELECT *
    FROM georaster_table g WHERE georid=1 and
    sdo_nn(g.georaster.spatialextent,sdo_geometry(2001, 4326,
    sdo_point_type(0,0,NULL), NULL, NULL),'sdo_num_res=5')='TRUE';

    Point cloud example:
    select *
    from blktab b
    where sdo_nn(b.blk_extent,sdo_geometry(2001, 8307,
    sdo_point_type(-74,40,NULL), NULL, NULL),'sdo_num_res=5')='TRUE';


    I know that i shd use something else than * in the select statement, but i cant figure out what it shd be such that i will get a handle of all the 5 neighboring cells/points.


    Appreciate your help.

    Thanks,
    Bal
  • 3. Re: sdo_nn to fetch points from point cloud and georaster
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi Bal,
    user645260 wrote:
    get a handle of all the 5 neighboring cells/points.
    What do you mean by "handle"? An ID, a Rownum, the full record? You are getting the full record(s) back now.
    Your question is not clear to me. What do you want with the results of your query? Are those statements returning any records?
    I would doubt the georaster query returns aything, seeing as the point you are using is at 0,0. And if the georaster data and the point cloud are in the same area, it's a bit strange that you use two different SRID's, but that all can happen.

    Without knowing your data, your (business) requirements, and what it is you expect it's a bit difficult to help you further. So, read the documentation, follow the examples, and if Spatial is something that you are going to work with in the future I'd reccommend reading "Pro Oracle Spatial for Oracle Database 11g", an excellent book by Ravi Kothuri, Albert Godfrind, and Euro Beinat which has taught me a lot, I'll say that (been working with Spatial since 1999, but never knew some of the things it could do until I read that book).

    And if you have questions, don't hesitate to ask them on these forums (that's what they are here for :-) ), but check this item of the SQL and PL/SQL Forum: [url https://forums.oracle.com/forums/thread.jspa?threadID=2174552#9360002]How do I ask a question on the forums?. Don't get me wrong, but if we have the information that is asked for like in that FAQ, it will be easier for us to help answer questions quicker. We do not know your data, business, and all the context the way you do, that is why that faq is there.

    HTH,
    Stefan
  • 4. Re: sdo_nn to fetch points from point cloud and georaster
    JeffreyXie Journeyer
    Currently Being Moderated
    Bal,

    your stmts would return the whole georaster objects or point clouds which are in the nearest neighbor. you can use these stmts to search out the specific georaster object to work on (assuming you have a table with many images).

    For GeoRaster, Once you pinned down the specific georaster object, you simply call sdo_geor.getcellvalue to find out the 5 points. First you know the model coordinates of your point, then call sdo_geor.getCellCoordinate to find out its cell coordinates. Then you compute the cell coordinates of its neighboring cells (simply add or minus 1 etc). Finally you call getcellvalue using those cell coordinates. You may take a look at this thread if much more points are needed: Trying to get multiple cell values within a geometry

    For Point Cloud, you need to create your geometry, for example create a point geom then call sdo_geom.sdo_buffer to create a searching window. Then call sdo_pc_pkg.clip_pc, from which you get a blob as the result. Then either read the BLOB directly or convert it to sdo_geometry by calling do_pc_pkg.to_geometry.

    BTW, not sure what is the purpose. If you want to do interpolations, for georaster, you can call sdo_geor.evaluateDouble directly, which interpolates cell values at any point using neighboring cell values.

    Hope this helps,

    Jeffrey
  • 5. Re: sdo_nn to fetch points from point cloud and georaster
    648263 Newbie
    Currently Being Moderated
    thanks

    Edited by: user645260 on Mar 18, 2013 10:17 AM
  • 6. Re: sdo_nn to fetch points from point cloud and georaster
    648263 Newbie
    Currently Being Moderated
    hi,

    I was able to perform idw using your suggestions.

    However, i have a question regarding creating point clouds. Form the documentation, i can see that point clouds are created using SDO_PC_PKG.CREATE_PC method and needs a source table. However, i dont want to create a source table with the points data. I would like to directly read data from a file and create a 3d point on the fly and create point cloud in java.

    Could you point me to any documentation or give suggestions as to how i can achieve this.

    Thanks,
    Bal
  • 7. Re: sdo_nn to fetch points from point cloud and georaster
    JeffreyXie Journeyer
    Currently Being Moderated
    Hi Bal,

    glad to know your IDW works now.

    besides creating a temp table to create sdo_pc, there are two tools you can use to load point clouds in LAS files:

    1. Safe Software FME supports sdo_pc
    2. open source PDAL/libLAS supports direct loading into oracle sdo_pc (http://www.pointcloud.org)

    hope this helps,
    Jeffrey
  • 8. Re: sdo_nn to fetch points from point cloud and georaster
    648263 Newbie
    Currently Being Moderated
    Thanks.

    Also, after running the script i found that the pl/sql procedure takes a long time to execute .Could you pls suggest ways to improve execution time.

    Here is the main component in my script -here x and y are in cartesian coordinates.

    To find all the neighboring points at radius of 'rad' units from a point (x,y): i loop this script for all the points which is close to half a million.
    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;

    I think, if i move the point cloud data in memory then the execution time may increase considerably. However, the column have blob data - so can you suggest how to create a in memory point cloud table.

    Thanks,
    Bal

    Edited by: user645260 on Mar 24, 2013 3:16 PM
  • 9. Re: sdo_nn to fetch points from point cloud and georaster
    mhorhamm Explorer
    Currently Being Moderated
    This will load into memory a multipoint SDO_GEOMETRY, for each block intersecting with your query. You might want to avoid the bulk collect, but this should not be a problem for a sufficiently small value for rad.

    From that you say you intend to find the closest n points.

    You say that the call is slow. What is the size of your entire point cloud object (# of points), and how many points are within the query window (x,y-rad,x+rad,y,x,y+rad))? How long does the CLIP_PC take? How many rows does it bulk collect into idw_neighbour_coll?

    Thanks,
  • 10. Re: sdo_nn to fetch points from point cloud and georaster
    648263 Newbie
    Currently Being Moderated
    Hi,

    For sdo_pc_pkg.clip_pc:
    So, i have in total 262k points. For each point in an average it takes 100 ms. So, in order to compute for all points, this in itself would take close to 7 hrs.

    After clip operation, calculating the idw value:
    Also, there are on an average 1000 neighboring cells in the query window (x,y-rad,x+rad,y,x,y+rad)) for each point. Here, since i have to perform idw operation ... i have to calculate the distance of each neighboring point from x,y. I loop through all the points in the sdo_geometry and calculate the distance using a simple math formula that uses power function. This takes on an average 150 ms to calculate idw value for each point.

    Although the number of points are pretty large but still i think the execution time is pretty high. Please let me know where should i be focusing to bring the execution time down.

    Thanks,
    Bal

    Edited by: user645260 on Mar 25, 2013 4:09 PM

    Edited by: user645260 on Mar 26, 2013 1:25 PM
  • 11. Re: sdo_nn to fetch points from point cloud and georaster
    mhorhamm Explorer
    Currently Being Moderated
    If you try the following, do you get remotely similar times?

    SQL> create table dim (a number) nologging;

    Table created.

    Elapsed: 00:00:00.01
    SQL> begin
    2 for x in 1..65 loop
    insert into dim values (x);
    end loop;
    end;
    /
    3 4 5 6
    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.02
    SQL> create table inptab nologging as (
    2 select
    lpad(rownum, 24, '0') rid,
    d1.a val_d1,
    d2.a val_d2,
    d3.a val_d3
    from
    dim d1,
    dim d2,
    dim d3);
    3 4 5 6 7 8 9 10
    Table created.

    Elapsed: 00:00:00.58
    SQL> create table pcs(
    2 pc_id number,
    pc sdo_pc);
    3
    Table created.

    Elapsed: 00:00:00.08
    SQL> create table pc_blocks as (select * from mdsys.sdo_pc_blk_table where rownum < 0);

    Table created.

    Elapsed: 00:00:00.08
    SQL> declare
    pc sdo_pc;
    begin
    pc :=
    sdo_pc_pkg.init(
    'PCS',
    'PC',
    'PC_BLOCKS',
    'blk_capacity=100000',
    mdsys.sdo_geometry(
    2003,
    null,
    null,
    mdsys.sdo_elem_info_array(
    1, 1003, 3),
    mdsys.sdo_ordinate_array(
    1, 1,
    65, 65)),
    0.005,
    3,
    null);

    sdo_pc_pkg.create_pc(
    pc,
    'INPTAB');

    insert into pcs values (1, pc);
    end;
    /
    2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
    PL/SQL procedure successfully completed.

    Elapsed: 00:00:03.82
    SQL> select
    sdo_pc_pkg.to_geometry(
    a.points,
    a.num_points,
    3,
    null)
    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(
    10, 10 - 1,
    10 + 1, 10,
    10, 10 + 1)),
    null,
    null,
    null,
    null)
    from
    pcs b
    where
    rownum = 1) a;
    2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
    SDO_PC_PKG.TO_GEOMETRY(A.POINTS,A.NUM_POINTS,3,NULL)(SDO_GTYPE, SDO_SRID, SDO_PO
    --------------------------------------------------------------------------------
    SDO_GEOMETRY(3005, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 325), SDO_ORDINATE_ARRA
    Y(9, 10, 39, 9, 10, 56, 9, 10, 43, 9, 10, 48, 9, 10, 23, 9, 10, 53, 9, 10, 58, 9
    , 10, 61, 9, 10, 25, 9, 10, 18, 9, 10, 11, 9, 10, 19, 9, 10, 41, 9, 10, 60, 9, 1
    0, 13, 9, 10, 24, 9, 10, 62, 9, 10, 2, 9, 10, 14, 9, 10, 29, 9, 10, 34, 9, 10, 3
    7, 9, 10, 36, 9, 10, 27, 9, 10, 44, 9, 10, 20, 9, 10, 9, 9, 10, 46, 9, 10, 30, 9
    , 10, 51, 9, 10, 4, 9, 10, 47, 9, 10, 40, 9, 10, 3, 9, 10, 28, 9, 10, 49, 9, 10,
    6, 9, 10, 57, 9, 10, 16, 9, 10, 21, 9, 10, 31, 9, 10, 26, 9, 10, 50, 9, 10, 42,
    9, 10, 65, 9, 10, 10, 9, 10, 32, 9, 10, 63, 9, 10, 7, 9, 10, 45, 9, 10, 17, 9,
    10, 35, 9, 10, 54, 9, 10, 8, 9, 10, 59, 9, 10, 1, 9, 10, 22, 9, 10, 55, 9, 10, 1
    2, 9, 10, 15, 9, 10, 5, 9, 10, 52, 9, 10, 64, 9, 10, 38, 9, 10, 33, 10, 11, 45,
    10, 10, 23, 10, 10, 14, 10, 9, 60, 10, 10, 20, 10, 11, 65, 10, 11, 38, 10, 9, 19

    SDO_PC_PKG.TO_GEOMETRY(A.POINTS,A.NUM_POINTS,3,NULL)(SDO_GTYPE, SDO_SRID, SDO_PO
    --------------------------------------------------------------------------------
    , 10, 11, 46, 10, 11, 39, 10, 10, 2, 10, 10, 36, 10, 10, 17, 10, 11, 58, 10, 10,
    12, 10, 9, 39, 10, 9, 30, 10, 11, 9, 10, 9, 49, 10, 11, 21, 10, 9, 33, 10, 10,
    7, 10, 10, 64, 10, 11, 27, 10, 10, 31, 10, 9, 58, 10, 9, 42, 10, 10, 15, 10, 9,
    5, 10, 9, 59, 10, 10, 40, 10, 9, 1, 10, 9, 45, 10, 9, 36, 10, 11, 61, 10, 11, 63
    , 10, 10, 42, 10, 9, 15, 10, 10, 19, 10, 9, 6, 10, 11, 41, 10, 9, 20, 10, 11, 10
    , 10, 9, 25, 10, 10, 35, 10, 10, 61, 10, 9, 12, 10, 10, 52, 10, 9, 41, 10, 11, 3
    7, 10, 10, 6, 10, 11, 59, 10, 9, 14, 10, 10, 63, 10, 9, 26, 10, 10, 25, 10, 10,
    26, 10, 9, 23, 10, 10, 32, 10, 10, 33, 10, 9, 10, 10, 9, 9, 10, 9, 40, 10, 11, 1
    1, 10, 10, 27, 10, 10, 4, 10, 10, 46, 10, 9, 17, 10, 9, 43, 10, 9, 61, 10, 11, 4
    8, 10, 10, 43, 10, 10, 13, 10, 11, 43, 10, 10, 34, 10, 9, 31, 10, 11, 55, 10, 9,
    54, 10, 10, 50, 10, 10, 5, 10, 10, 60, 10, 10, 45, 10, 9, 22, 10, 9, 18, 10, 9,

    SDO_PC_PKG.TO_GEOMETRY(A.POINTS,A.NUM_POINTS,3,NULL)(SDO_GTYPE, SDO_SRID, SDO_PO
    --------------------------------------------------------------------------------
    8, 10, 11, 34, 10, 10, 48, 10, 11, 17, 10, 10, 1, 10, 11, 13, 10, 11, 23, 10, 1
    1, 15, 10, 11, 30, 10, 10, 54, 10, 10, 10, 10, 11, 1, 10, 9, 11, 10, 10, 59, 10,
    11, 5, 10, 11, 28, 10, 11, 47, 10, 11, 6, 10, 11, 25, 10, 10, 65, 10, 9, 7, 10,
    9, 47, 10, 10, 24, 10, 10, 47, 10, 10, 11, 10, 9, 28, 10, 11, 8, 10, 10, 16, 10
    , 10, 22, 10, 9, 4, 10, 10, 56, 10, 10, 41, 10, 10, 62, 10, 9, 52, 10, 9, 63, 10
    , 9, 57, 10, 11, 24, 10, 11, 62, 10, 9, 16, 10, 10, 37, 10, 11, 33, 10, 9, 62, 1
    0, 9, 13, 10, 9, 53, 10, 11, 16, 10, 10, 44, 10, 11, 53, 10, 11, 60, 10, 10, 21,
    10, 10, 28, 10, 11, 31, 10, 11, 42, 10, 11, 57, 10, 10, 8, 10, 11, 54, 10, 9, 4
    6, 10, 9, 37, 10, 9, 32, 10, 11, 20, 10, 10, 29, 10, 11, 64, 10, 9, 3, 10, 9, 38
    , 10, 11, 12, 10, 11, 26, 10, 10, 30, 10, 9, 64, 10, 9, 44, 10, 11, 36, 10, 10,
    38, 10, 11, 2, 10, 9, 56, 10, 10, 51, 10, 10, 55, 10, 10, 53, 10, 11, 4, 10, 10,

    SDO_PC_PKG.TO_GEOMETRY(A.POINTS,A.NUM_POINTS,3,NULL)(SDO_GTYPE, SDO_SRID, SDO_PO
    --------------------------------------------------------------------------------
    58, 10, 9, 24, 10, 11, 18, 10, 11, 51, 10, 9, 34, 10, 11, 32, 10, 11, 56, 10, 1
    0, 9, 10, 11, 44, 10, 11, 35, 10, 11, 29, 10, 9, 50, 10, 10, 49, 10, 9, 65, 10,
    11, 52, 10, 10, 57, 10, 9, 51, 10, 11, 22, 10, 9, 35, 10, 9, 29, 10, 10, 39, 10,
    11, 49, 10, 10, 18, 10, 9, 55, 10, 11, 19, 10, 9, 48, 10, 11, 7, 10, 11, 3, 10,
    11, 14, 10, 10, 3, 10, 9, 21, 10, 11, 50, 10, 9, 27, 10, 11, 40, 10, 9, 2, 11,
    10, 26, 11, 10, 62, 11, 10, 39, 11, 10, 10, 11, 10, 42, 11, 10, 23, 11, 10, 32,
    11, 10, 2, 11, 10, 36, 11, 10, 14, 11, 10, 8, 11, 10, 63, 11, 10, 16, 11, 10, 64
    , 11, 10, 18, 11, 10, 57, 11, 10, 9, 11, 10, 21, 11, 10, 55, 11, 10, 13, 11, 10,
    7, 11, 10, 11, 11, 10, 35, 11, 10, 41, 11, 10, 20, 11, 10, 3, 11, 10, 4, 11, 10
    , 25, 11, 10, 12, 11, 10, 28, 11, 10, 5, 11, 10, 15, 11, 10, 54, 11, 10, 40, 11,
    10, 19, 11, 10, 58, 11, 10, 51, 11, 10, 1, 11, 10, 34, 11, 10, 43, 11, 10, 65,

    SDO_PC_PKG.TO_GEOMETRY(A.POINTS,A.NUM_POINTS,3,NULL)(SDO_GTYPE, SDO_SRID, SDO_PO
    --------------------------------------------------------------------------------
    11, 10, 59, 11, 10, 60, 11, 10, 50, 11, 10, 44, 11, 10, 48, 11, 10, 49, 11, 10,
    27, 11, 10, 33, 11, 10, 31, 11, 10, 24, 11, 10, 38, 11, 10, 37, 11, 10, 45, 11,
    10, 46, 11, 10, 17, 11, 10, 47, 11, 10, 61, 11, 10, 52, 11, 10, 6, 11, 10, 29, 1
    1, 10, 56, 11, 10, 53, 11, 10, 22, 11, 10, 30))


    Elapsed: 00:00:00.25
    SQL> select sum(num_points) from pc_blocks;

    SUM(NUM_POINTS)
    ---------------
    274625

    Elapsed: 00:00:00.03
    SQL> drop table inptab;

    Table dropped.

    Elapsed: 00:00:00.03
    SQL> drop table dim;

    Table dropped.

    Elapsed: 00:00:00.01
    SQL> drop table pcs;

    Table dropped.

    Elapsed: 00:00:00.04
    SQL> drop table pc_blocks;

    Table dropped.

    Elapsed: 00:00:00.10
    SQL>
  • 12. Re: sdo_nn to fetch points from point cloud and georaster
    mhorhamm Explorer
    Currently Being Moderated
    So, in this example, the CREATE_PC takes 0.000014 sec/pt, and the query takes 0.00077 sec/resulting point.
  • 13. Re: sdo_nn to fetch points from point cloud and georaster
    mhorhamm Explorer
    Currently Being Moderated
    For larger query windows (more resulting points), the query performance per point becomes much faster.
  • 14. Re: sdo_nn to fetch points from point cloud and georaster
    648263 Newbie
    Currently Being Moderated
    Hi,

    i tried your script and for creating point cloud, it took almost 6 seconds.

    Also, the time taken to run the below query was 0.704 sec.

    select
    sdo_pc_pkg.to_geometry(
    a.points,
    a.num_points,
    3,
    null)
    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(
    10, 10 - 1,
    10 + 1, 10,
    10, 10 + 1)),
    null,
    null,
    null,
    null)
    from
    pcs b
    where
    rownum = 1) a;


    I dont think this is to do with my laptop because it is a i7 pc with 8gb ram. Maybe, i need to tune my oracle settings. Also, is there a way i can cache the whole table and index in memory to so the query operation is faster for spatial tables.

    Thanks
    Bal

    Edited by: user645260 on Apr 5, 2013 9:56 AM
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points