Re: Oracle Spatial - querying user_sdo_geom_metadata

Steve G wrote:
> I'm struggling trying to learn some basics with Oracle Spatial.
>
> After doing SQL*Loader of the Tiger Data (generated by shp2sdo ) for
> Los Angeles County, for example, I then want to query the
> user_sdo_geom_metadata to get the bounding box coordinates.
>
> In SQL*Plus, I can do this:
>
> SQL> select diminfo from user_sdo_geom_metadata where table_name =
> 'TGR06037LKA';
>
> DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE)
> --------------------------------------------------------------------------------
> SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', -118.94474, -117.64938, .00000005),
> SDO_DIM_E
> LEMENT('Y', 32.806208, 34.82315, .00000005))
>
> But in my code I need a SQL query that accesses just the SDO_LB
> attribute, with the column returning a single numeric value.
>
> For example, if I want my query to return just the -118.94474 lower
> bound value for X I assume I need to access the dim_array for
> SDO_DIMNAME (X) and then get the SDO_LB attribute. But how that would
> be written in SQL is where I am stuck.
>
> Thanks for any help!
> - Stephen G.

You are struggling with the access to elements of object types. Recognize that Spatial (and Locator) use several composite data types that are defined in user MDSYS. General form to access a specific element of a composite type is 'composite_name.element_namer', so you likely want to 'select diminfo.sdo_lb ...'

Have you looked at the tutorials and sample code at http://otn.oracle.com? Also, the Online Library at http://education.oracle.com has several online classes, and the documentation at http://docs.oracle.com also helps

HTH
/Hans Received on Sun Mar 07 2004 - 02:48:58 CST