Forum Stats

  • 3,824,982 Users
  • 2,260,448 Discussions
  • 7,896,369 Comments

Discussions

How to get values of multiple cells in geo raster in Oracle SQL?

User_QGG5Q
User_QGG5Q Member Posts: 1 Green Ribbon

I use SDO_GEORASTER to store raster data in Oracle DB. I have stored the elevation data in georaster table named DEM_RASTER_6. And I want to find the elevation of a cell when the location (latitude and longitude) of that cell is given as input.


I used the below SQL query to do it for the locations (139.6532351, 35.646544), (137.15862129082493, 35.08569941846463) and (130.40386925062816, 33.597688116260485) and it worked well using sdo_geor.getcellvalue function.


SELECT

sdo_geor.getcellvalue(

raster,

0,sdo_geometry(2001, 4326

--,sdo_point_type( 139.6532351, 35.646544, NULL)

,sdo_point_type( 137.15862129082493, 35.08569941846463, NULL)

--,sdo_point_type( 130.40386925062816, 33.597688116260485, NULL)

,NULL, NULL),

1) dem_value

FROM DEM_RASTER_6 a

where

 sdo_anyinteract( a.raster.SPATIALEXTENT,

   sdo_geometry(2001, 4326

     -- ,sdo_point_type( 139.6532351, 35.646544, NULL),

     -- ,sdo_point_type( 130.40386925062816, 33.597688116260485, NULL),

     ,sdo_point_type( 137.15862129082493, 35.08569941846463, NULL),

   NULL, NULL)

 ) = 'TRUE' ;

The above query works only for a single point at a time. Now my question is that if we need to find cell values at multiple points simultaneuosly, then can we use sdo_geor.getcellvalues() function?


For that I tried the below SQL


 SELECT

sdo_geor.getcellvalues(

        raster

        , 0

        , sdo_geometry(

        2005

        ,4326

        ,NULL

        ,SDO_ELEM_INFO_ARRAY(1,1,3)

        ,SDO_ORDINATE_ARRAY(130.40386925062816, 33.597688116260485, 139.6532351, 35.646544, 137.15862129082493, 35.08569941846463)

        ),

        1) dem_value

FROM DEM_RASTER_6 a

  where

 sdo_anyinteract( a.raster.SPATIALEXTENT,

  sdo_geometry(

        2005--multipoint

        ,4326

        ,NULL

        ,SDO_ELEM_INFO_ARRAY(1,1,3)--multipoint with 3 points

        ,SDO_ORDINATE_ARRAY(130.40386925062816, 33.597688116260485, 139.6532351, 35.646544, 137.15862129082493, 35.08569941846463)-- 3 sets of coor for multipoints

        )

 ) = 'TRUE' ; 

I got the error: , but I get the error ORA-13415: invalid or out of scope point specification ORA-06512: at "MDSYS.SDO_GEOR_INT", line 7965 ORA-06512: at "MDSYS.SDO_GEOR", line 3311 13415. 00000 - "invalid or out of scope point specification" *Cause: The point position specified by the <ptGeom, layerNumber> or <rowNumber, colNumber, bandNumber> parameter combination was invalid or out of scope. *Action: Ensure that the parameters specify a valid point that is or can be translated into a cell position inside the cell space of the GeoRaster object.


Is the way I use sdo_geor.getcellvalues() wrong or is there another function I can use?

Tagged: