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.
I am new to oracle georaster/point cloud. I tried the below statement -
FROM georaster_table g WHERE georid=1 and
sdo_point_type(0,0,NULL), NULL, NULL),'sdo_num_res=5')='TRUE';
Point cloud example:
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.
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.
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,
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.
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,
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.
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
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.
Edited by: user645260 on Mar 24, 2013 3:16 PM
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?
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.
Edited by: user645260 on Mar 25, 2013 4:09 PM
Edited by: user645260 on Mar 26, 2013 1:25 PM
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.
b.pc, -- Input Point Cloud object
10, 10 - 1,
10 + 1, 10,
10, 10 + 1)),
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.
Edited by: user645260 on Apr 5, 2013 9:56 AM