Oracle Spatial 12C MD.HHENCODE for geohash not working

Question

I am trying to use the MD.HHENCODE (or MDSYS.HHENCODE) spatial function in Oracle Spatial 12C. I can't get a function to compile based on the only docs I could find. Here is my function code:

CREATE OR REPLACE FUNCTION linear_key (  p_shape   in mdsys.sdo_geometry,
                                         p_diminfo in mdsys.sdo_dim_array ,  v_lvl  number)
RETURN RAW DETERMINISTIC
IS
  v_ctr   MDSYS.SDO_GEOMETRY;  
  rval   RAW(200);
BEGIN
    v_ctr := MDSYS.SDO_GEOM.SDO_CENTROID(p_shape,p_diminfo);
    select MDSYS.HHENCODE( v_ctr.sdo_point.x, p_diminfo(1).sdo_lb, p_diminfo(1).sdo_ub, v_lvl, 
                          v_ctr.sdo_point.y, p_diminfo(2).sdo_lb, p_diminfo(2).sdo_ub, v_lvl) 
                          into rval from dual;
  return rval;
 END;

I am getting compile errors - ORA 00904, MDSYS.HHENCODE invalid identifier. Some examples I saw use MD.HHENCODE and I get the same error regardless. I also read that I should enable the package, but I cannot figure out how to do that either. I have tinkered for an entire day, please help. Thanks

Do you actually have the spatial option installed? Can you see the MDSYS.MD package, and/or the MDSYS.HHENCODE function, in the data dictionary? If so have you granted privileges to the user you're trying to compile this as - and granted directly, not through a role? (Why do the title and first line refer to CCENCODE?) — Alex Poole
thanks @AlexPoole spatial is definitely installed. As for the granting, I don't know but thanks for bringing it up. CCENCODE is referenced because that was the entry point into this problem (which may be a bad label at this point) — markgiaconia

markgiaconia markgiaconia · Accepted Answer · 2016-11-14T19:18:12

Thanks for the input, but I think i discovered through an obscure link how to achieve what I want via SDO_UTIL.linear_key(args), which apparently replaces HHENCODE of old in 12C. My table has a col called geometry and it is an 8307 projected geometry, so my bbox is what it is below for that reason. It appears that this accomplishes a geohash like function, and I am currently looking at the DECODE functionality for the string.

Here is the code

select substr(SDO_UTIL.linear_key(A.geometry,-180,-90,180,90, 10),0,3)  as encoded
from mytable A

I am still trying to figure out what the last arg in the function actually does for me, but I am getting what I want out of this.

Oracle Spatial 12C MD.HHENCODE for geohash not working

1 Answers