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

["Simon Greener" <sgreener@xxxxxxxxxxxxxxx>]

Jody,

> There is one remaining issue, a full table scan (!) is performed in  
> order to determine the extend of the data. As soon as we get this fixed  
> oracle will have caught up with Postgis support. And if enough people  
> are happy with it we can move over to be a supported plugin.

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:

  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.