Re: Oracle Spatial - querying user_sdo_geom

From: Steve G <googlegroups_at_digicoast.com>
Date: 7 Mar 2004 23:17:56 -0800
Message-ID: <f0b66c2f.0403072317.3a7a5be2@posting.google.com>

Yup -- that was where I was stuck. I didn't know about the table function capability (new with 9i, apparently) for accessing objects.

So the SQL that I now to get the bounding box for the TIGER Data that I loaded is:

SQL>
  SELECT
    sdo_lb, sdo_ub
  FROM
    TABLE(

      SELECT diminfo 
      FROM user_sdo_geom_metadata 
      WHERE 
        table_name = 'TGR06037LKA' 
        AND 
        column_name = 'GEOM'

) WHERE sdo_dimname = 'X';

Here are some links that educated me on the table() Table Function

http://www.databasejournal.com/features/oracle/article.php/2222781
http://www.dbasupport.com/oracle/ora9i/returning_rows.shtml
http://www.oreillynet.com/pub/a/network/2003/01/22/feuerstein.html

Thanks so much!

Stephen G.

Hans Forbrich <hforbric_at_yahoo.net> wrote in message news:<_lB2c.152116$Hy3.2631_at_edtnps89>...
> 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 Mon Mar 08 2004 - 01:17:56 CST

Re: Oracle Spatial - querying user_sdo_geom_metadata