This content has been marked as final.
Show 9 replies

1. Re: function to get x, y
Simon Greener Dec 14, 2012 7:31 AM (in response to don123)Dear Unnamed Person,
You can't return parameters for a function.
It much be a procedure.
Your Procedure needs an ID parameter otherwise it can only work against a table with a single row. You need something like an id parameter to locate a specific row within the table.create table foo ( idnumber number, geometry mdsys.sdo_geometry); insert into foo values (1,sdo_geometry(2001,null,sdo_point_type(1,2,null),null,null)); commit; create or replace procedure getxy(name in varchar2, id out number, x out number, y out number) IS begin execute immediate 'select a.idnumber, t.x, t.y from '  name  ' a, table(sdo_util.getvertices(a.geometry)) t' into id, x, y; return; end getxy; / set serveroutput on size unlimited declare id number; x number; y number; begin getxy('FOO',id,x,y); dbms_output.put_line('FOO > '  id  ','  x ','y); end; /  Result  FOO > 1,1,2
But why would you do this when you can simply do this?:
If you really want a function, try this:select a.idnumber, t.x, t.y from name a, table(sdo_util.getvertices(a.geometry)) t;  Results  IDNUMBER X Y    1 1 2
I think this answers your question.drop procedure getxy; create function getxy(name in varchar2) return mdsys.vertex_type deterministic AS v_vertex mdsys.vertex_type; begin  Assuming 11g vertex_type constructor... execute immediate 'select mdsys.vertex_type(t.x,t.y,t.z,t.w,null,null,null,null,null,null,null,a.idnumber) as vertex from '  name  ' a, table(sdo_util.getvertices(a.geometry)) t' into v_vertex; return v_vertex; end getxy; / select a.vertex.id as idnumber, a.vertex.x as x, a.vertex.y as y from (select getxy('foo') as vertex from dual ) a;  Result  IDNUMBER X Y    1 1 2
Please award appropriate points.
regards
Simon
Edited by: Simon Greener on Dec 14, 2012 6:29 PM 
2. Re: function to get x, y
_jum Dec 14, 2012 7:35 AM (in response to don123)You should think over your idea or describe what you really want.
The function you gave cant't work for some reasons:
you can only RETURN one value (this can be a complex type or a list) but not N1, N2, N3
you use a CURSOR and EXIT but no LOOP to fetch it/to exit
you want to use a dynamic table NAME, this is possible with dynamic SQL, SYS_REFCURSOR,...
you give the geometry column as parameter, but you use fixed column a.geometry
...
If you give us a test case with CREATE TABLE, INSERT and the expected result, we could help more.
Oh, I just see @Simon Greener answered the question with in the meantime ;)
Edited by: _jum on 14.12.2012 08:34 
3. Re: function to get x, y
don123 Dec 14, 2012 1:49 PM (in response to _jum)Thanks Simon, Jum
You are right Jum, my problem is i want to write a single program to handle the following.
(1) generate X, Y from one line table
(2) pass these X, Y to compute bearing
So, i want to create procedure / function by usung cursors to handle both above.
How to modify the procedure from Simon, if i my table name is fixed (no need of dynamic SQL) ?
regards 
4. Re: function to get x, y
Simon Greener Dec 14, 2012 11:58 PM (in response to don123)Dear Regards (Please a nickname would be better),
I believe my solution(s) pretty well answers all your problems and gives you a clear lead as to what to do to implement what you want. I am disappointed you did not at least award some helpful points.
Jum asked you not just for a clear statement of what you need (now we know you want to compute a bearing), and for some data as per my example.
This request for an example including real data is something those of us who spend our own time answering questions consistently ask for and demonstrate how to do in our answers.
In addition, now that you want a bearing it is imperative that we know whether your data is geodetic/geographic (ie latitude/longitude) or not as this is a major determinant on the answers we can give.
Also, what version of Oracle are you on? This is important for bearing calculations.
There is a recent thread (*SDO_UTIL.BEARING_TILT_FOR_POINTS in a Select statement*) on calculating a bearing from geographic data from two points. Did you see it before creating this request?
Now, looking at your response:
(1) generate X, Y from one line table
A one line table is a very rare and strange beast. Why is this the case? Normally one expects to see many rows in the table with some sort of ID? (If this
was the case the need to compute a bearing between two points in that table would make sense.)
(2) pass these X, Y to compute bearing
But nowhere in your original function did you indicate that this was the case. So, why are we returning an IDNUMBER, an X and a Y if you want a bearing? Or do you want it all?
Coupling this requirement with:
So, i want to create procedure / function by usung cursors to handle both above.
What do you mean by needing "cursors to handle both above". Personally I would probably never create a procedure or function for this use case. Straight SQL will do all you want.
So, do you want a procedure that returns:
1. idnumber,
2. x
3. y
4. bearing?
But if you want to compute a bearing we need double this, ie 2 idnumbers, 2 Xs and 2Ys (to form start/end of line for which bearing is needed). Which coupled with a returned bearing means you will need 7 out parameters for a procedure!!
Or do you want a function? That returns what?
We have already indicated that a function can't return anything but a single value. That single value maybe complex (such as in my example where I returned a vertex_type), but if you do not want a single value return (such as a bearing) then what do you want?
How to modify the procedure from Simon, if i my table name is fixed (no need of dynamic SQL) ?
Trivially (see also Oracle PL/SQL documentation), see what follows:
Now, if you want a bearing returned you need two coordinates (xy pairs) to form a line. But you haven't indicated how this would be done from table containing a single XY point.drop table foo; create table foo ( idnumber number, geometry mdsys.sdo_geometry); insert into foo values (1,sdo_geometry(2001,null,sdo_point_type(1,2,null),null,null)); commit; drop function getxy; drop procedure getxy; create or replace procedure getxy(p_idnumber out nocopy number, p_x out number, p_y out number) IS begin select a.idnumber, t.x, t.y into p_idnumber, p_x, p_y from foo a, table(sdo_util.getvertices(a.geometry)) t where rownum < 2;  For safety's sake in case table has more than one row return; end getxy; / set serveroutput on size unlimited declare id number; x number; y number; begin getxy(id,x,y); dbms_output.put_line(id  ','  x ','y); end; /  Results  anonymous block completed 1,1,2
I think if you reflect on what we have written, coupled with some study of PL/SQL functions/procedures and sql execution options you will see that we have given you all the information you need to build your solution.
If you want more help, please, please, please provide us with real data, whether it is geographic data, and a worked examples showing how you get the xy pairs for a bearing.
Again, I have been very helpful. Please award some points.
regards
Simon 
5. Re: function to get x, y
don123 Dec 17, 2012 5:10 AM (in response to Simon Greener)Simon,
Sorry for not explaining the problem correctly. I am learning Oracle spatial and PLSQL. In fact, I got more help (answers) from you in this forum in the last one year. I am thankful to you always.
I know how to extract X, Y of a line by using SQL, I know computing bearing between two points by using SQL.
I want to create a SINGLE PLSQL program which can extract X, Y and compute bearing.
The sample data is given below i.e in geodetic coordinates. I hope i explained the problem.
IDNUMBER

GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

101
SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(87.900836, 42.001688, 87.900444, 42.002069))
102
SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(87.925747, 41.969833, 87.925058, 41.969833))
103
SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(87.912084, 41.970584, 87.912093, 41.970222))
104
SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(87.900669, 41.984725, 87.900184, 41.984725))
regards
smb 
6. Re: function to get x, y
Simon Greener Dec 17, 2012 7:43 AM (in response to don123)Smb,
But how does one construct a function that does this for you as you don't give enough information about how to find a pair of XY coordinates.
Also, now you are showing us some linestrings. Do you want the bearing of the linestring? Can a linestring have more than one vertex?
Here's a guess based on the idea of getting a bearing from a single linestring:
Can you see why we need more information from you up front?create or replace function bearing_for_points(p_idnumber in number, p_tolerance in number default 0.05) return number deterministic as v_bearing NUMBER; v_tilt NUMBER; v_points mdsys.vertex_set_type; BEGIN select mdsys.vertex_type(t.x,t.y,t.z,t.w,null,null,null,null,null,null,null,f.idnumber) as vertex bulk collect into v_points from foo f, table(mdsys.sdo_util.getVertices(f.geom)) t where f.idnumber = p_idnumber; SDO_UTIL.BEARING_TILT_FOR_POINTS( mdsys.sdo_geometry(2001,8307,mdsys.sdo_point_type(v_points(1).x,v_points(1).y,v_points(1).z),null,null), mdsys.sdo_geometry(2001,8307,mdsys.sdo_point_type(v_points(2).x,v_points(2).y,v_points(2).z),null,null), p_tolerance,v_bearing,v_tilt); return v_bearing; END; / grant execute on bearing_for_points to public; select dd2dms(degrees(bearing_for_points(f.idnumber,0.005))) as bearing from foo f;  Results  BEARING  37^30'12.051" 89^59'59.171" 181^3'46.478" 89^59'59.416"
regards
Simon 
7. Re: function to get x, y
don123 Dec 17, 2012 8:22 AM (in response to Simon Greener)Simon,
I understand that i should give all info upfront.
I have table of line string with two end points and there are no intermediate vertices, everyline has IDNUMBER as mentioned in sample data and the X, Y pair can be identified by this IDNUMBER.
Your solution will solve my problem. Out of academic interest, i want to know how to pass parameters from one function to another.
I want to learn how to create one function to extract X, Y from a table and create another function to accept these X,Y numbers to compute bearing.
Finally i can call these two functions in suitable way from another sub program.
regards
smb 
8. Re: function to get x, y
Simon Greener Dec 17, 2012 11:02 AM (in response to don123)Smb,
Give me some function type signatures, the name of the table, what is in the geometry column, and I will fill in the body of the function.
regards
Simon 
9. Re: function to get x, y
don123 Dec 17, 2012 12:16 PM (in response to Simon Greener)Simon,
The sample data (table name and geometry points) is given below.
First sub program (function / procedure) has to generate X, Y and second sub program (function / procedure) has to take X, Y values from first sub program and give output of bearing.
I am not sure about signatures.
SQL> select idnumber, geometry from lines;
IDNUMBER

GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

101
SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(87.900836, 42.001688, 87.900444, 42.002069))
102
SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(87.925747, 41.969833, 87.925058, 41.969833))
103
SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(87.912084, 41.970584, 87.912093, 41.970222))
104
SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(87.900669, 41.984725, 87.900184, 41.984725))
regards
smb