oracle FIRST_VALUE for sdo_geometry column

22 Views Asked by At

I have a table containing some GPS points with their position and a grouping identifier: (In this example I'm using ID as a sorting column - in the real life I have a timestamp column too):

ID   POSITION             GROUP_ID   
     (SDO_GEOMETRY) 
---------------------------------- 
 1   (point w/coords  1)     1
 2   (point w/coords  2)     1 
 3   (point w/coords  3)     1
 4   (point w/coords  4)     2
 5   (point w/coords  5)     3
 6   (point w/coords  6)     3
 7   (point w/coords  7)     3
 8   (point w/coords  8)     3
 9   (point w/coords  9)     3
10   (point w/coords 10)     4
11   (point w/coords 11)     4
12   (point w/coords 12)     4

I want to add to each record the position of the FIRST point in its group:

ID   POSITION             GROUP_ID   FIRST_POSITION
     (SDO_GEOMETRY)                   (SDO_GEOMETRY)
----------------------------------------------------- 
 1   (point w/coords  1)     1     (point w/coords  1)    
 2   (point w/coords  2)     1     (point w/coords  1) 
 3   (point w/coords  3)     1     (point w/coords  1)
 4   (point w/coords  4)     2     (point w/coords  2)
 5   (point w/coords  5)     3     (point w/coords  3)
 6   (point w/coords  6)     3     (point w/coords  3)
 7   (point w/coords  7)     3     (point w/coords  3)
 8   (point w/coords  8)     3     (point w/coords  3)
 9   (point w/coords  9)     3     (point w/coords  3)
10   (point w/coords 10)     4     (point w/coords  4)
11   (point w/coords 11)     4     (point w/coords  4)
12   (point w/coords 12)     4     (point w/coords  4)

I tried the following:

select ID, POSITION, GROUP_ID,
  FIRST_VALUE(POSITION) over(partition by GROUP_ID ORDER BY ID) FIRST_POSITION
from points

but I get the following error: ORA-22901: cannot compare nested table or VARRAY or LOB attributes of an object type Reading this I guess Oracle needs to be able to sort by POSITION in addition to ID.

Sure I could get the first ID and then join to the same table to lookup the position. Somethig like this:

with p as  
select p.ID, p.POSITION, p.GROUP_ID, pp.POSITION FIRST_POSITION
from (
    select ID, POSITION, GROUP_ID,
      FIRST_VALUE(ID) over(partition by GROUP_ID ORDER BY ID) FIRST_ID
    from points
  ) p inner join points pp on (pp.ID = p.ID)

but is there a better way to get FIRST_POSITION?

0

There are 0 best solutions below