Home » Server Options » Spatial » query to find first value of array (3 threads merged by bb) (windows xp)
query to find first value of array (3 threads merged by bb) [message #504525] Tue, 26 April 2011 03:51 Go to next message
swapnabpnn
Messages: 96
Registered: December 2010
Member
Hi,
I am trying to get only first value of sdo_ordinate array.
I had written query as follows:
select a.geom.sdo_ordinates FROM cable a WHERE CABLE_ID = 428578;


The above query gives all the SDO_ORDINATES.But I am looking only for SDO_ORDINATE[1],so how can I write that?Is it possible with single query??If so please help me out.

Thanks....
Re: query to find first value of array (2 threads merged by bb) [message #504535 is a reply to message #504525] Tue, 26 April 2011 05:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
In the following example, the last query is all you need. The rest is just to show the intermediary steps, so that you can see how it works.

SCOTT@orcl_11gR2> create table cable
  2    (cable_id  number,
  3  	geom	  sdo_geometry)
  4  /

Table created.

SCOTT@orcl_11gR2> insert into cable values
  2    (428578,
  3  	SDO_GEOMETRY
  4  	  (2003, NULL, NULL,
  5  	   SDO_ELEM_INFO_ARRAY (1, 1003, 1),
  6  	   SDO_ORDINATE_ARRAY (5, 1, 8, 1, 8, 6, 5, 7, 5, 1)))
  7  /

1 row created.

SCOTT@orcl_11gR2> select * from cable
  2  /

  CABLE_ID
----------
GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
-------------------------------------------------------------------------------------
    428578
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(5,
 1, 8, 1, 8, 6, 5, 7, 5, 1))


1 row selected.

SCOTT@orcl_11gR2> select a.geom.sdo_ordinates
  2  FROM   cable a
  3  WHERE  CABLE_ID = 428578
  4  /

GEOM.SDO_ORDINATES
-------------------------------------------------------------------------------------
SDO_ORDINATE_ARRAY(5, 1, 8, 1, 8, 6, 5, 7, 5, 1)

1 row selected.

SCOTT@orcl_11gR2> select t.column_value, rownum
  2  FROM   cable a,
  3  	    TABLE (a.geom.sdo_ordinates) t
  4  WHERE  a.CABLE_ID = 428578
  5  /

COLUMN_VALUE     ROWNUM
------------ ----------
           5          1
           1          2
           8          3
           1          4
           8          5
           6          6
           5          7
           7          8
           5          9
           1         10

10 rows selected.

SCOTT@orcl_11gR2> select column_value
  2  from   (select t.column_value, rownum rn
  3  	     FROM   cable a,
  4  		    TABLE (a.geom.sdo_ordinates) t
  5  	     WHERE  a.CABLE_ID = 428578)
  6  where  rn = 1
  7  /

COLUMN_VALUE
------------
           5

1 row selected.

SCOTT@orcl_11gR2>

Re: query to find first value of array (2 threads merged by bb) [message #504539 is a reply to message #504535] Tue, 26 April 2011 06:38 Go to previous message
swapnabpnn
Messages: 96
Registered: December 2010
Member
Thnk you.
Previous Topic: query only ONE SDO_ORDINATE
Next Topic: Assigning geometry to variable
Goto Forum:
  


Current Time: Thu Aug 28 06:49:38 CDT 2014

Total time taken to generate the page: 0.12060 seconds