This content has been marked as final.
Show 8 replies

1. Re: sdo_ordinates get first and last point
269171 Nov 14, 2007 3:21 PM (in response to 595716)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 Nov 14, 2007 3:23 PM (in response to 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 Nov 14, 2007 7:10 PM (in response to 595716)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 Nov 14, 2007 7:30 PM (in response to 595716)Yoda
For such line string inquiries/manipulations, the LRS package always offers a lot of builtin 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 Nov 16, 2007 8:54 AM (in response to 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?
Thats the correct solution?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;
Can you tell which statement deliver the correct result  I need the length of the geom line (from first to last point)SELECT SDO_LRS.GEOM_SEGMENT_LENGTH(f_Geom) FROM feature where f_id = 251418
best regards 
6. Re: sdo_ordinates get first and last point
Luc Van Linden Nov 19, 2007 4:41 PM (in response to 595716)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 Jun 26, 2012 1:02 PM (in response to 415914)Hi
Can u write query according to what you said.???
Rema 
8. Re: sdo_ordinates get first and last point
Simon Greener Jun 26, 2012 11:56 PM (in response to 595716)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:
Which you can use as: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; /
regardsWith 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)
Simon