This discussion is archived
9 Replies Latest reply: Dec 17, 2012 4:16 AM by don123 RSS

function to get x, y

don123 Newbie
Currently Being Moderated
hi, can you help me to write the function. The function should take table name, geometry column as input and should give id, x coordinate, y coordinate as output. I have tried with the following. The error is below.
========================================================

SQL> show errors
Errors for FUNCTION GETXY:

LINE/COL ERROR
-------- --------------------------------------------
13/10 PLS-00103: Encountered the symbol "," when e
following:
. ( * @ % & = - + ; < / > at in is mod remai
<an exponent (**)> <> or != or ~= >= <= <> a
like4 likec between || multiset member submu

14/1 PLS-00103: Encountered the symbol "CLOSE"


==============================================================

create or replace function getxy

(name in varchar2, geom in mdsys.sdo_geometry, id out number, x out number, y out number)

return number

IS

n1 number;
n2 number;
n3 number;

cursor c1 is select a.idnumber, t.x, t.y from name a, table(sdo_util.getvertices(a.geometry)) t;

begin

open c1;
fetch c1 into n1, n2, n3;

exit when c1%notfound;

return n1, n2, n3;
close c1;
end getxy;
  • 1. Re: function to get x, y
    Simon Greener Journeyer
    Currently Being Moderated
    Dear Unnamed Person,

    You can't return parameters for a function.

    It much be a procedure.
    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
    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.

    But why would you do this when you can simply do 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
    If you really want a function, try this:
    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
    I think this answers your question.

    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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:
    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
    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.

    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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:
    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"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    Can you see why we need more information from you up front?

    regards
    Simon
  • 7. Re: function to get x, y
    don123 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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