Oracle: sdo_aggr_mbr vs user_sdo_geom_metadata

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Oracle: sdo_aggr_mbr vs user_sdo_geom_metadata

Bergenroth, Brandon
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
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users
Reply | Threaded
Open this post in threaded view
|

Re: Oracle: sdo_aggr_mbr vs user_sdo_geom_metadata

Jody Garnett-2
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
<[hidden email]> 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
> [hidden email]
> http://lists.refractions.net/mailman/listinfo/udig-users
>
_______________________________________________
udig-users mailing list
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users
Reply | Threaded
Open this post in threaded view
|

Re: Oracle: sdo_aggr_mbr vs user_sdo_geom_metadata

Simon Greener
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 <[hidden email]> 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
> <[hidden email]> 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
>> [hidden email]
>> http://lists.refractions.net/mailman/listinfo/udig-users
>>
> _______________________________________________
> udig-users mailing list
> [hidden email]
> 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: [hidden email]
   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
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users
Reply | Threaded
Open this post in threaded view
|

Re: Oracle: sdo_aggr_mbr vs user_sdo_geom_metadata

Bergenroth, Brandon
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: [hidden email] [mailto:udig-users-
> [hidden email]] On Behalf Of Simon Greener
> Sent: Thursday, August 19, 2010 5:16 AM
> To: [hidden email]
> 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
> <[hidden email]> 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
> > <[hidden email]> 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
> >> [hidden email]
> >> http://lists.refractions.net/mailman/listinfo/udig-users
> >>
> > _______________________________________________
> > udig-users mailing list
> > [hidden email]
> > 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: [hidden email]
>    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
> [hidden email]
> http://lists.refractions.net/mailman/listinfo/udig-users
_______________________________________________
udig-users mailing list
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users
Reply | Threaded
Open this post in threaded view
|

Re: Oracle: sdo_aggr_mbr vs user_sdo_geom_metadata

Jody Garnett-2
In reply to this post by Simon Greener
Thanks Simon; I better make a geotools bug report and gather up your suggestion:
- https://jira.codehaus.org/browse/GEOT-3251

I actually do know what sdo ordinates mean - the data structure is used as part of SDO_GEOMETRY and there is some code in GeoTools to parse such things which I wrote a while back.

Jody

On 19/08/2010, at 7:16 PM, Simon Greener wrote:

> 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 <[hidden email]> 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
>> <[hidden email]> 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
>>> [hidden email]
>>> http://lists.refractions.net/mailman/listinfo/udig-users
>>>
>> _______________________________________________
>> udig-users mailing list
>> [hidden email]
>> 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: [hidden email]
>  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
> [hidden email]
> http://lists.refractions.net/mailman/listinfo/udig-users

_______________________________________________
udig-users mailing list
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users
Reply | Threaded
Open this post in threaded view
|

Re: Oracle: sdo_aggr_mbr vs user_sdo_geom_metadata

Jody Garnett-2
Found the original bug report here:
- https://jira.codehaus.org/browse/GEOT-704

One thing you can tell form the age of this bug report is that funding for development of Oracle support does not happen often compared with ArcSDE or PostGIS.

Jody

On 20/08/2010, at 1:23 PM, Jody Garnett wrote:

> Thanks Simon; I better make a geotools bug report and gather up your suggestion:
> - https://jira.codehaus.org/browse/GEOT-3251
>
> I actually do know what sdo ordinates mean - the data structure is used as part of SDO_GEOMETRY and there is some code in GeoTools to parse such things which I wrote a while back.
>
> Jody
>
> On 19/08/2010, at 7:16 PM, Simon Greener wrote:
>
>> 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 <[hidden email]> 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
>>> <[hidden email]> 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
>>>> [hidden email]
>>>> http://lists.refractions.net/mailman/listinfo/udig-users
>>>>
>>> _______________________________________________
>>> udig-users mailing list
>>> [hidden email]
>>> 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: [hidden email]
>> 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
>> [hidden email]
>> http://lists.refractions.net/mailman/listinfo/udig-users
>

_______________________________________________
udig-users mailing list
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users
Reply | Threaded
Open this post in threaded view
|

Re: Oracle: sdo_aggr_mbr vs user_sdo_geom_metadata

Jody Garnett-2
In reply to this post by Bergenroth, Brandon
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.

As indicated I think I can fix that; we will just have to go with a different assumption (consider bounds based on crs extent if available).

> 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!  

I was not trying to be negative - just reporting what I remember of the discussion. Since that time I have found https://jira.codehaus.org/browse/GEOT-704 so you can read for yourself.

> 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.  

Oh cool! Do you have access to a log? Perhaps we could sort out what ESRI catalog is using to determine the bounds?

> 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).

That is my understanding as well; as you go through the oracle docs they make it very clear that proper set up is required for the index to be efficient.

Thanks Brandon your conversation has already turned up a few good ideas,
Jody

_______________________________________________
udig-users mailing list
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users
Reply | Threaded
Open this post in threaded view
|

Re: Oracle: sdo_aggr_mbr vs user_sdo_geom_metadata

Jody Garnett-2
In reply to this post by Bergenroth, Brandon
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: [hidden email] [mailto:udig-users-
>> [hidden email]] On Behalf Of Simon Greener
>> Sent: Thursday, August 19, 2010 5:16 AM
>> To: [hidden email]
>> 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
>> <[hidden email]> 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
>>> <[hidden email]> 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
>>>> [hidden email]
>>>> http://lists.refractions.net/mailman/listinfo/udig-users
>>>>
>>> _______________________________________________
>>> udig-users mailing list
>>> [hidden email]
>>> 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: [hidden email]
>>   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
>> [hidden email]
>> http://lists.refractions.net/mailman/listinfo/udig-users
> _______________________________________________
> udig-users mailing list
> [hidden email]
> http://lists.refractions.net/mailman/listinfo/udig-users

_______________________________________________
udig-users mailing list
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users
Reply | Threaded
Open this post in threaded view
|

Re: Oracle: sdo_aggr_mbr vs user_sdo_geom_metadata

Simon Greener
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 <[hidden email]> 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: [hidden email] [mailto:udig-users-
>>> [hidden email]] On Behalf Of Simon Greener
>>> Sent: Thursday, August 19, 2010 5:16 AM
>>> To: [hidden email]
>>> 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
>>> <[hidden email]> 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
>>>> <[hidden email]> 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
>>>>> [hidden email]
>>>>> http://lists.refractions.net/mailman/listinfo/udig-users
>>>>>
>>>> _______________________________________________
>>>> udig-users mailing list
>>>> [hidden email]
>>>> 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: [hidden email]
>>>   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
>>> [hidden email]
>>> http://lists.refractions.net/mailman/listinfo/udig-users
>> _______________________________________________
>> udig-users mailing list
>> [hidden email]
>> http://lists.refractions.net/mailman/listinfo/udig-users
>
> _______________________________________________
> udig-users mailing list
> [hidden email]
> 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: [hidden email]
   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
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users
Reply | Threaded
Open this post in threaded view
|

Re: Oracle: sdo_aggr_mbr vs user_sdo_geom_metadata

Simon Greener
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 <[hidden email]> 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 <[hidden email]> 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: [hidden email] [mailto:udig-users-
>>>> [hidden email]] On Behalf Of Simon Greener
>>>> Sent: Thursday, August 19, 2010 5:16 AM
>>>> To: [hidden email]
>>>> 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
>>>> <[hidden email]> 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
>>>>> <[hidden email]> 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
>>>>>> [hidden email]
>>>>>> http://lists.refractions.net/mailman/listinfo/udig-users
>>>>>>
>>>>> _______________________________________________
>>>>> udig-users mailing list
>>>>> [hidden email]
>>>>> 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: [hidden email]
>>>>   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
>>>> [hidden email]
>>>> http://lists.refractions.net/mailman/listinfo/udig-users
>>> _______________________________________________
>>> udig-users mailing list
>>> [hidden email]
>>> http://lists.refractions.net/mailman/listinfo/udig-users
>>
>> _______________________________________________
>> udig-users mailing list
>> [hidden email]
>> 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: [hidden email]
   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
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users
Reply | Threaded
Open this post in threaded view
|

Re: Oracle: sdo_aggr_mbr vs user_sdo_geom_metadata

Bergenroth, Brandon
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: [hidden email] [mailto:udig-users-
> [hidden email]] On Behalf Of Simon Greener
> Sent: Friday, August 20, 2010 3:26 AM
> To: [hidden email]
> 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
> <[hidden email]> 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
> <[hidden email]> 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: [hidden email] [mailto:udig-users-
> >>>> [hidden email]] On Behalf Of Simon Greener
> >>>> Sent: Thursday, August 19, 2010 5:16 AM
> >>>> To: [hidden email]
> >>>> 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
> >>>> <[hidden email]> 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
> >>>>> <[hidden email]> 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
> >>>>>> [hidden email]
> >>>>>> http://lists.refractions.net/mailman/listinfo/udig-users
> >>>>>>
> >>>>> _______________________________________________
> >>>>> udig-users mailing list
> >>>>> [hidden email]
> >>>>> 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: [hidden email]
> >>>>   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
> >>>> [hidden email]
> >>>> http://lists.refractions.net/mailman/listinfo/udig-users
> >>> _______________________________________________
> >>> udig-users mailing list
> >>> [hidden email]
> >>> http://lists.refractions.net/mailman/listinfo/udig-users
> >>
> >> _______________________________________________
> >> udig-users mailing list
> >> [hidden email]
> >> 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: [hidden email]
>    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
> [hidden email]
> http://lists.refractions.net/mailman/listinfo/udig-users
_______________________________________________
udig-users mailing list
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users
Reply | Threaded
Open this post in threaded view
|

Re: Oracle: sdo_aggr_mbr vs user_sdo_geom_metadata

Bergenroth, Brandon
In reply to this post by Jody Garnett-2
Great news Jody, thanks.



> -----Original Message-----
> From: Jody Garnett [mailto:[hidden email]]
> Sent: Friday, August 20, 2010 2:27 AM
> To: Bergenroth, Brandon
> Cc: [hidden email]
> Subject: Re: [udig-users] Oracle: sdo_aggr_mbr vs
> user_sdo_geom_metadata
>
> 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: [hidden email] [mailto:udig-users-
> >> [hidden email]] On Behalf Of Simon Greener
> >> Sent: Thursday, August 19, 2010 5:16 AM
> >> To: [hidden email]
> >> 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
> >> <[hidden email]> 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
> >>> <[hidden email]> 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
> >>>> [hidden email]
> >>>> http://lists.refractions.net/mailman/listinfo/udig-users
> >>>>
> >>> _______________________________________________
> >>> udig-users mailing list
> >>> [hidden email]
> >>> 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: [hidden email]
> >>   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
> >> [hidden email]
> >> http://lists.refractions.net/mailman/listinfo/udig-users
> > _______________________________________________
> > udig-users mailing list
> > [hidden email]
> > http://lists.refractions.net/mailman/listinfo/udig-users

_______________________________________________
udig-users mailing list
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users