8 Replies Latest reply: Jun 26, 2012 6:56 PM by Simon Greener RSS

    sdo_ordinates get first and last point

    595716
      Hi!
      Is it possible to get th first and last point from sdo_ordinates? especially I have a line which contains 6 points, now I need just the first and the last point - possible?how?

      best regards
        • 1. Re: sdo_ordinates get first and last point
          269171
          You could use something like......

          select * from table( select sdo_util.getvertices( GEOM ) from zipcode where rownum < 2 );

          Combined with a known number of vertices.......

          select x, y
          from table( select sdo_util.getvertices( GEOM ) from zipcode where rownum < 2 )
          where id in ( 1, 29 );

          I coundn't quite get this to work, but I'm sure there's a way to fix the query.

          select x, y
          from table( select sdo_util.getvertices( GEOM ) from zipcode where rownum < 2 )
          where id in ( 1, sdo_util.GETNUMVERTICES( geom ) );

          Ronan
          • 2. Re: sdo_ordinates get first and last point
            269171
            I was just missing the brackets.

            This query should work:
            select x, y
            from table( select sdo_util.getvertices( GEOM ) from zipcode where rownum < 2 )
            where id in ( 1, (select sdo_util.GETNUMVERTICES( geom )
            from zipcode where rownum < 2 ) )
            • 3. Re: sdo_ordinates get first and last point
              415914
              Hi,

              to get the first and the second koordinate from the sdo_ordinates array is easy. So if you need the first pair and the last pair out of a linestring, get first the first pair. After this reverse the linestring with SDO_UTIL.REVERSE_LINESTRING. The searched second koordinates are now at the start and could be get by the same way as the first pair.

              I didn't try which solution is faster or is the "better" way. Decide it on your own

              Udo
              • 4. Re: sdo_ordinates get first and last point
                Luc Van Linden
                Yoda

                For such line string inquiries/manipulations, the LRS package always offers a lot of built-in functionality.

                Take a look at SDO_LRS.GEOM_SEGMENT_END_PT and SDO_LRS.GEOM_SEGMENT_START_PT in the user manual
                http://download.oracle.com/docs/html/B14255_01/sdo_lrs_ref.htm#sthref1925

                Please come back once you've gone through the reading, you should be able to find your needs there

                succes

                Luc
                • 5. Re: sdo_ordinates get first and last point
                  595716
                  hi, thanks for the response!

                  What I really need is the length of a geom line string, how I can get them correctly?

                  Thats my solution with first and last point, but I dont know if thats correct because just the distance between these both points get calculated = false?
                       SELECT SDO_GEOM.SDO_DISTANCE(
                         (SELECT  SDO_LRS.GEOM_SEGMENT_START_PT(f_geom) FROM feature where f_id = C_REC_FEATURE.F_ID),
                         (SELECT  SDO_LRS.GEOM_SEGMENT_END_PT(f_geom) FROM feature where f_id = C_REC_FEATURE.F_ID),
                         0.0050, 'unit=meter')
                         INTO tmpLength
                         FROM DUAL;
                  Thats the correct solution?
                  SELECT  SDO_LRS.GEOM_SEGMENT_LENGTH(f_Geom)
                    FROM feature where f_id = 251418
                  Can you tell which statement deliver the correct result - I need the length of the geom line (from first to last point)

                  best regards
                  • 6. Re: sdo_ordinates get first and last point
                    Luc Van Linden
                    Yoda

                    The length of geometry linestring is returned by SDO_GEOM.SDO_LENGTH

                    http://download.oracle.com/docs/html/B14255_01/sdo_objgeom.htm#sthref1660



                    SELECT SDO_GEOM.SDO_DISTANCE
                    (
                    SDO_LRS.GEOM_SEGMENT_START_PT(f_geom),
                    SDO_LRS.GEOM_SEGMENT_END_PT(f_geom),
                    0.0050, 'unit=meter'
                    )
                    FROM feature where f_id = 251418

                    will return you the straight distance from the startpoint to the endpoint of a linegeometry.

                    Luc
                    • 7. Re: sdo_ordinates get first and last point
                      920209
                      Hi

                      Can u write query according to what you said.???

                      Rema
                      • 8. Re: sdo_ordinates get first and last point
                        Simon Greener
                        Yoda,

                        Getting the first and last coordinate of a line is best done in SQL but it can mean the SQL looks messy.

                        If so, you can encapsulate this aspect of the problem inside a plsql function(s) as I do in my free PL/SQL packages.

                        Here is a function that will do what you want:
                        create or replace
                        FUNCTION ST_Get_Point (p_geometry     IN MDSYS.SDO_GEOMETRY,
                                                  p_point_number IN NUMBER DEFAULT 1 )
                           RETURN MDSYS.SDO_GEOMETRY
                           IS
                             v_dims  PLS_INTEGER;    -- Number of dimensions in geometry
                             v_gtype NUMBER;         -- SDO_GTYPE of returned geometry
                             v_p     NUMBER;         -- Index into ordinates array
                             v_px    NUMBER;         -- X of extracted point
                             v_py    NUMBER;         -- Y of extracted point
                             v_pz    NUMBER;         -- Z of extracted point
                             v_pm    NUMBER;         -- M of extracted point
                             
                             Function ST_isMeasured( p_gtype in number )
                             return boolean
                             is
                             Begin
                               Return CASE WHEN MOD(trunc(p_gtype/100),10) = 0
                                           THEN False
                                           ELSE True
                                        END;
                             End ST_isMeasured;
                        
                           BEGIN
                             -- Get the number of dimensions from the gtype
                             v_dims := SUBSTR (p_geometry.SDO_GTYPE, 1, 1);
                             -- Verify that the point exists
                             -- and set index in ordinates array
                             IF p_point_number = 0
                                OR ABS(p_point_number) > p_geometry.SDO_ORDINATES.COUNT()/v_dims THEN
                               RETURN NULL;
                             ELSIF p_point_number <= -1 THEN
                               v_p := ( (p_geometry.SDO_ORDINATES.COUNT() / v_dims) + p_point_number ) * v_dims + 1;
                             ELSE
                               v_p := (p_point_number - 1) * v_dims + 1;
                             END IF;
                             -- Extract the X and Y coordinates of the desired point
                             v_gtype := (v_dims*1000) + 1;
                             v_px := p_geometry.SDO_ORDINATES(v_p);
                             v_py := p_geometry.SDO_ORDINATES(v_p+1);
                             IF ( v_dims > 3 ) THEN
                               v_pm := p_geometry.SDO_ORDINATES(v_p+3);
                               v_pz := p_geometry.SDO_ORDINATES(v_p+2);
                             ELSIF ( v_dims = 3 ) THEN
                                 IF ( ST_isMeasured(p_geometry.SDO_GTYPE) ) THEN
                                   v_pm := p_geometry.SDO_ORDINATES(v_p+2);
                                 ELSE
                                   v_pz := p_geometry.SDO_ORDINATES(v_p+2);
                                 END IF;
                             END IF;
                             -- Construct and return the point
                             RETURN CASE WHEN v_dims > 3
                                         THEN MDSYS.SDO_GEOMETRY(v_gtype,
                                                                 p_geometry.SDO_SRID,
                                                                 NULL,
                                                                 MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1),
                                                                 MDSYS.SDO_ORDINATE_ARRAY(v_px, v_py, v_pz, v_pm))
                                         ELSE MDSYS.SDO_GEOMETRY(v_gtype,
                                                                 p_geometry.SDO_SRID,
                                                                 MDSYS.SDO_POINT_TYPE (v_px, v_py,
                                                                 CASE WHEN ST_isMeasured(p_geometry.sdo_gtype) THEN v_pm ELSE v_pz END),
                                                                 NULL,
                                                                 NULL)
                                     END;
                           END ST_Get_Point;
                        /
                        Which you can use as:
                        With line As (
                        SELECT sdo_geometry(2002,NULL,NULL,sdo_elem_info_array(1,2,1),sdo_ordinate_array(1,1,2,2,3,3,4,4,5,5,6,6)) as geom
                          FROM dual
                        )
                        select ST_Get_Point(geom,1) as first_point,
                               ST_Get_Point(geom,-1) as last_point 
                           from line;
                        -- Result
                        --
                        START_POINT                                                    LAST_POINT
                        -------------------------------------------------------------- --------------------------------------------------------------
                        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(6.0,6.0,NULL),NULL,NULL) SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(6.0,6.0,NULL),NULL,NULL)
                        regards
                        Simon