Re: [udig-users] Oracle: sdo_aggr_mbr vs user_sdo_geom_metadata

["Bergenroth, Brandon" <bbergenroth@xxxxxxx>]

Simon, 

This intrigued me a bit, and I found this:

For geodetic data, the index is not created in the long/lat space. We create the index
in a 3D space since long/lat essentially defines a 3D space on the earth's surface.
So what you see in the sdo_root_mbr is a 3D box from this 3D space.

This is not easy to convert back to an MBR in long/lat space from the values we store
as there are special cases to deal with when the MBR crosses the poles and
the prime meridian.

From: http://kr.forums.oracle.com/forums/thread.jspa?threadID=523315



> -----Original Message-----
> From: udig-users-bounces@xxxxxxxxxxxxxxxxxxxxx [mailto:udig-users-
> bounces@xxxxxxxxxxxxxxxxxxxxx] On Behalf Of Simon Greener
> Sent: Friday, August 20, 2010 3:26 AM
> To: udig-users@xxxxxxxxxxxxxxxxxxxxx
> Subject: Re: [udig-users] Oracle: sdo_aggr_mbr vs
> user_sdo_geom_metadata
> 
> PS the ordinates in sdo_root_mbr are the two corners of an optimized
> rectangle - ie bottom left/top right of mbr.
> Simon
> On Fri, 20 Aug 2010 17:12:47 +1000, Simon Greener
> <simon@xxxxxxxxxxxxxxxxxxxx> wrote:
> 
> > Just to clarify.
> > My suggestion actually gives you access to the actual, dynamic,
> extent
> > of every indexed geometry in a "layer".
> > All other methods based on whether some load tool filled out
> > the user-sdo-geom-metadata view, the the ArcSde LAYERS table
> > is immaterial: it is the extent at load time. Accessing the
> sdo_root_mbr of the rtree index is correct, dyanmic and instantaneous.
> > No need to do anything else.
> > Jody, my point abkht the sdo ordinates is that when the data is
> geodetic, oracle puts in 4 ordinates in the root mbr that i caNot work
> out what they are. they are not lat/long, but sone sort of
> confabulztion of the numbers.
> > Simon
> > sorry about the spelling: sent from my iPad in NZ.
> > PS I am hapy to program the oracle side of GeoTools etc if the money
> is there.
> > On Fri, 20 Aug 2010 16:26:53 +1000, Jody Garnett
> <jody.garnett@xxxxxxxxx> wrote:
> >
> >> So long and short of it is ... the bug was closed shortly after I
> reported it. The issue has been fixed in GeoTools 2.7.x and we will get
> the fix when uDig updates.
> >>
> >> Jody
> >>
> >> On 19/08/2010, at 10:34 PM, Bergenroth, Brandon wrote:
> >>
> >>> I'm not so worried about uDig not using user_sdo_geom_metadata as
> much as it doing the full table scans to determine the mbr.  And then,
> doing the query on all tables even before I've picked one to actually
> display.
> >>>
> >>> I'm a little surprised about the negative feedback about
> user_sdo_geom_metadata, it seems to me the users with problems having
> correct metadata would be the same users with problems having a correct
> crs!
> >>>
> >>> In fact, in my experience when loading data with a 3rd party tool
> like ESRI ArcCatalog, the metadata is inserted correctly with the
> proper bounds, but it's the crs (srid) that always shows up null.
> >>>
> >>> And Simon's solution suffers a bit as well, an index is not
> required, I might only want to display a particular layer at full
> extent and an index is just a waste in that case, but that is probably
> a rare occurrence compared to everything else.
> >>>
> >>> I guess I've just always seen user_sdo_geom_metadata as a necessary
> part of having tables with sdo_geometry, so I make sure it is always
> populated correctly (same with geometry_columns in postgis).
> >>>
> >>> Thanks,
> >>> Brandon
> >>>
> >>>
> >>>
> >>>> -----Original Message-----
> >>>> From: udig-users-bounces@xxxxxxxxxxxxxxxxxxxxx [mailto:udig-users-
> >>>> bounces@xxxxxxxxxxxxxxxxxxxxx] On Behalf Of Simon Greener
> >>>> Sent: Thursday, August 19, 2010 5:16 AM
> >>>> To: udig-users@xxxxxxxxxxxxxxxxxxxxx
> >>>> Subject: Re: [udig-users] Oracle: sdo_aggr_mbr vs
> >>>> user_sdo_geom_metadata
> >>>>
> >>>> Greetings,
> >>>> (Jodie, I'm on holidays in NZ. Back Sept 3rd.)
> >>>> I learned  when programming GeoRaptor that you
> >>>> can access the root mbr of the Oracle RTree
> >>>> in user_sdo_index_metadata (column sdo_root_mbr).
> >>>> This is an sdo_geometry object. However,
> >>>> when it contains the mbr of a gedetix layer,
> >>>> we haven't worked out what the
> >>>> sdoordinates mean, as yet.
> >>>> But is is blindingly faat: better tahn aggr union.
> >>>> Code is in metedatatool.java in sourceforge repository.
> >>>> best i can do.
> >>>> simon
> >>>> On Thu, 19 Aug 2010 14:23:32 +1000, Jody Garnett
> >>>> <jody.garnett@xxxxxxxxx> wrote:
> >>>>
> >>>>> Hi Brandon.
> >>>>>
> >>>>> We had the previous version of our oracle support use the geom
> >>>>> metadadata table; and I after treading the oracle manuals I
> though
> >>>>> this was the correct approach to determine bounds.
> >>>>>
> >>>>> I got lots of negative feedback; apparently this table is not
> often
> >>>>> filled in with the correct information and cannot be trusted.
> With
> >>>>> that in mind, when geotools created their "jdbc-ng" rewrite for
> >>>> oracle
> >>>>> support they did not even considering taking the geom metadata
> table
> >>>>> code used previous.
> >>>>>
> >>>>> If you like we can make the feature request for GeoTools and see
> what
> >>>> they say.
> >>>>>
> >>>>> With respect to SELECT SDO_AGGR_MBR ... I had thought that we
> avoided
> >>>>> doing this for uDig 1.2.0 - instead assuming a default max extent
> >>>>> based on the coordinate reference system.
> >>>>>
> >>>>> When Maurcio returns from vacation we can ask him, he performed
> the
> >>>>> oracle testing for 1.2.0.
> >>>>>
> >>>>>
> >>>>> Jody
> >>>>>
> >>>>> On Thu, Aug 19, 2010 at 4:37 AM, Bergenroth, Brandon
> >>>>> <bbergenroth@xxxxxxx> wrote:
> >>>>>> I have yet to see uDig (including 1.2.0) use
> user_sdo_geom_metadata,
> >>>>>> instead, it seems to always insist on running
> >>>>>>
> >>>>>> SELECT SDO_AGGR_MBR(GEOMETRY) FROM BIG_SPATIAL_TABLE
> >>>>>>
> >>>>>> for every table in the schema.
> >>>>>>
> >>>>>> Will uDig ever use user_sdo_geom_metadata?  Running a full table
> >>>> scan on
> >>>>>> every spatial table makes it pretty much useless for anything
> but a
> >>>>>> trivial amount of data.
> >>>>>>
> >>>>>> Thanks,
> >>>>>> Brandon
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>> _______________________________________________
> >>>>>> udig-users mailing list
> >>>>>> udig-users@xxxxxxxxxxxxxxxxxxxxx
> >>>>>> http://lists.refractions.net/mailman/listinfo/udig-users
> >>>>>>
> >>>>> _______________________________________________
> >>>>> udig-users mailing list
> >>>>> udig-users@xxxxxxxxxxxxxxxxxxxxx
> >>>>> http://lists.refractions.net/mailman/listinfo/udig-users
> >>>>>
> >>>>
> >>>>
> >>>> --
> >>>> SpatialDB Advice and Design, Solutions Architecture and
> Programming,
> >>>> Oracle Database 10g Administrator Certified Associate; Oracle
> Database
> >>>> 10g SQL Certified Professional
> >>>> Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS,
> FME,
> >>>> Radius Topology and Studio Specialist.
> >>>> 39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.
> >>>> Website: www.spatialdbadvisor.com
> >>>>   Email: simon@xxxxxxxxxxxxxxxxxxxx
> >>>>   Voice: +61 362 396397
> >>>> Mobile: +61 418 396391
> >>>> Skype: sggreener
> >>>> Longitude: 147.20515 (147° 12' 18" E)
> >>>> Latitude: -43.01530 (43° 00' 55" S)
> >>>> GeoHash: r22em9r98wg
> >>>> NAC:W80CK 7SWP3
> >>>> _______________________________________________
> >>>> udig-users mailing list
> >>>> udig-users@xxxxxxxxxxxxxxxxxxxxx
> >>>> http://lists.refractions.net/mailman/listinfo/udig-users
> >>> _______________________________________________
> >>> udig-users mailing list
> >>> udig-users@xxxxxxxxxxxxxxxxxxxxx
> >>> http://lists.refractions.net/mailman/listinfo/udig-users
> >>
> >> _______________________________________________
> >> udig-users mailing list
> >> udig-users@xxxxxxxxxxxxxxxxxxxxx
> >> http://lists.refractions.net/mailman/listinfo/udig-users
> >>
> >
> >
> 
> 
> --
> SpatialDB Advice and Design, Solutions Architecture and Programming,
> Oracle Database 10g Administrator Certified Associate; Oracle Database
> 10g SQL Certified Professional
> Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME,
> Radius Topology and Studio Specialist.
> 39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.
> Website: www.spatialdbadvisor.com
>    Email: simon@xxxxxxxxxxxxxxxxxxxx
>    Voice: +61 362 396397
> Mobile: +61 418 396391
> Skype: sggreener
> Longitude: 147.20515 (147° 12' 18" E)
> Latitude: -43.01530 (43° 00' 55" S)
> GeoHash: r22em9r98wg
> NAC:W80CK 7SWP3
> _______________________________________________
> udig-users mailing list
> udig-users@xxxxxxxxxxxxxxxxxxxxx
> http://lists.refractions.net/mailman/listinfo/udig-users