Skip to Main Content

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

User_QGG5QMay 20 2022

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?

Comments
Post Details
Added on May 20 2022
0 comments
8 views