Re: [udig-devel] Apologies to the oracle enthusiasts.

[Jody Garnett <jgarnett@xxxxxxxxxxxxxxx>]

Simon Greener wrote:

> At my previous employer the extent of the Sdo_Geometry column in a table
> was always kept in the relevant entry in user_sdo_geom_metadata (or
> all_sdo_geom_metadata). This is standard practice for all oracle users
> as this extent is used when the spatial index is created.  Hence the extent
> can be gotten via the following piece of SQL ('scuse the VB6 code) without
> the need for a full-table scan against the actual table/column:
Thanks - I had already startedr down this road. Although I had only found about 
'X' and 'Y' - I will copy you sDimYDecodeSet and sDimaXDecodeSet over.

Question - should I provide a method to let people modify this metadata record? 
Or rather - it does not look like Oracle maintains this field with triggers or 
anything.

Do you know what we are supposed to do?
Jody

>   Dim sDimXDecodeSet As String
>   sDimYDecodeSet =  
> "'x','X','e','X','east','X','easting','X','lon','X','long','X','longitude','X'" 
>
>   Dim sDimYDecodeSet As String
>   sDimYDecodeSet =  
> "'y','Y','n','Y','north','Y','northing','Y','lat','Y','latitude','Y'"
>   Dim sDimNameSet As String
>   sDimNameSet =  
> "'x','e','east','easting','lon','long','longitude','y','Y','north','northing','lat','latitude'" 
>
>
>   sSQL = sprintf("SELECT DECODE(LOWER(SDO_DIMNAME),%s,%s,'UNKNOWN') AS  
> SDO_DIMNAME,",sDimXDecodeSet,sDimYDecodeSet)
>   sSQL = sSQL & "SDO_LB,SDO_UB FROM TABLE "
>   sSQL = sSQL & "(SELECT U.SDO_DIMINFO FROM 
> MDSYS.SDO_GEOM_METADATA_TABLE  u "
>   sSQL = sSQL & "WHERE UPPER(SDO_OWNER)='" & sSchema & "' AND "
>   sSQL = sSQL & "UPPER(SDO_TABLE_NAME)= '" & sObjectName & "' AND "
>   sSQL = sSQL & "UPPER(SDO_COLUMN_NAME)='" & sGeomColumnName & "') "
>   sSQL = sSQL & "WHERE LOWER(SDO_DIMNAME) IN (" & sDimNameSet & ")"
>
> For the SRID the following SQL is used:
>
> SELECT DECODE(U.SDO_SRID,NULL,-1,0,-1,U.SDO_SRID) AS SDO_SRID
>   FROM MDSYS.SDO_GEOM_METADATA_TABLE u
>  WHERE UPPER(SDO_OWNER)=UPPER(&sSchema)
>    AND UPPER(SDO_TABLE_NAME)=UPPER(&sObjectName)
>    AND UPPER(SDO_COLUMN_NAME)=UPPER(&sGeomColumnName)
>
> At 10g (Release 2, I think), an SRID->EPSG translation table is available
> inside Oracle that can map between Oracle's SRID and EPSG (I can't remember
> what the name of the table is, but will find out). I have this also as an
> Excel spreadsheet but would have to find out if I can make it publically
> available.
>
> regards
> S.
> _______________________________________________
> User-friendly Desktop Internet GIS (uDig)
> http://udig.refractions.net
> http://lists.refractions.net/mailman/listinfo/udig-devel