4 Replies Latest reply: May 11, 2012 7:50 PM by Simon Greener RSS

    select sdo_geometry

    cptkirkh
      Is it possible to query just the first object in a SDO-geometry data type? for example of if i have the following

      (2001, 8307, (-117.47196, 33.758424, ), , )

      How do i query that 2001? Thanks
        • 1. Re: select sdo_geometry
          John O'Toole
          Hi user8602786,
          select t.<geometry column>.sdo_gtype
          from <table name> t
          The trick is to make sure you use a table alias.

          Similarly to get the SRID, you can use
          select t.<geometry column>.sdo_srid
          from <table name> t
          Regards,
          John
          • 2. Re: select sdo_geometry
            cptkirkh
            ok so what if i want to add more data to that type. For example..

            I use the following update:

            update clients_test set CLI_MAIN_GEO_LOCATION = SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE (SUBSTR(Centlong,0,10),SUBSTR(Centlat,0,10),NULL), NULL, NULL) where cli_rid = recs.cli_rid;
            Say i want to put more items in the Null spaces outside of the SDO_point_type().

            For example in the tutorial found here:
            http://st-curriculum.oracle.com/obe/db/10g/r2/prod/datamgmt/spatial/spatial_otn.htm

            You can notice the piece below allows for gender, birthdate and marital status. Does one have to insert that only in the beginning or can One go back and edit and add there and if so how? Thanks.

            '01-JAN-60','married', 'M',

            The tutorial inserts into his table customers using the following command. It looks like to me that a part of the mdsys.sdo_geometry data type has information other than coordinate systems and long and lat.


            INSERT INTO customers VALUES
            (1001,'Dennis','Green',
            cust_address_typ('1 Oracle Drive','03062','Nashua','NH','US'),
            PHONE_LIST_TYP('+1 603 897 4104'),
            'us','AMERICA','100','Dennis.Green@Oracle.com',
            149,
            MDSYS.SDO_GEOMETRY(2001, 8307,
            MDSYS.SDO_POINT_TYPE (-63.13631, 52.485424,NULL),NULL,NULL),
            '01-JAN-60','married', 'M', '110,000 - 129,999');
            • 3. Re: select sdo_geometry
              Barbara Boehmer
              You are mistaking the separate columns date_of_birth, marital_status, gender, and income_level for parts of the cust_geo_location column. Please see the demonstration below.
              OE@orcl_11gR2> DESCRIBE customers
               Name                                      Null?    Type
               ----------------------------------------- -------- ----------------------------
               CUSTOMER_ID                               NOT NULL NUMBER(6)
               CUST_FIRST_NAME                           NOT NULL VARCHAR2(20)
               CUST_LAST_NAME                            NOT NULL VARCHAR2(20)
               CUST_ADDRESS                                       CUST_ADDRESS_TYP
               PHONE_NUMBERS                                      PHONE_LIST_TYP
               NLS_LANGUAGE                                       VARCHAR2(3)
               NLS_TERRITORY                                      VARCHAR2(30)
               CREDIT_LIMIT                                       NUMBER(9,2)
               CUST_EMAIL                                         VARCHAR2(30)
               ACCOUNT_MGR_ID                                     NUMBER(6)
               CUST_GEO_LOCATION                                  MDSYS.SDO_GEOMETRY
               DATE_OF_BIRTH                                      DATE
               MARITAL_STATUS                                     VARCHAR2(20)
               GENDER                                             VARCHAR2(1)
               INCOME_LEVEL                                       VARCHAR2(20)
              
              OE@orcl_11gR2> INSERT INTO customers VALUES
                2    (1001,
                3       'Dennis',
                4       'Green',
                5       cust_address_typ ('1 Oracle Drive','03062','Nashua','NH','US'),
                6       PHONE_LIST_TYP ('+1 603 897 4104'),
                7       'us',
                8       'AMERICA',
                9       '100',
               10       'Dennis.Green@Oracle.com',
               11       149,
               12       -- beginning of cust_geo_location columns
               13       MDSYS.SDO_GEOMETRY
               14         (2001,
               15          8307,
               16          MDSYS.SDO_POINT_TYPE (-63.13631, 52.485424,NULL),
               17          NULL,
               18          NULL),
               19       -- end of cust_geo_location column
               20       '01-JAN-60',
               21       'married',
               22       'M',
               23       '110,000 - 129,999');
              
              1 row created.
              
              OE@orcl_11gR2> SELECT cust_geo_location FROM customers WHERE customer_id = 1001
                2  /
              
              CUST_GEO_LOCATION(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_OR
              --------------------------------------------------------------------------------
              SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-63.13631, 52.485424, NULL), NULL, NULL)
              
              1 row selected.
              
              OE@orcl_11gR2> SELECT date_of_birth, marital_status, gender, income_level
                2  FROM   customers WHERE customer_id = 1001
                3  /
              
              DATE_OF_B MARITAL_STATUS       G INCOME_LEVEL
              --------- -------------------- - --------------------
              01-JAN-60 married              M 110,000 - 129,999
              
              1 row selected.
              
              OE@orcl_11gR2> SELECT * FROM customers WHERE customer_id = 1001
                2  /
              
              CUSTOMER_ID CUST_FIRST_NAME      CUST_LAST_NAME
              ----------- -------------------- --------------------
              CUST_ADDRESS(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
              --------------------------------------------------------------------------------
              PHONE_NUMBERS
              --------------------------------------------------------------------------------
              NLS NLS_TERRITORY                  CREDIT_LIMIT CUST_EMAIL
              --- ------------------------------ ------------ ------------------------------
              ACCOUNT_MGR_ID
              --------------
              CUST_GEO_LOCATION(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_OR
              --------------------------------------------------------------------------------
              DATE_OF_B MARITAL_STATUS       G INCOME_LEVEL
              --------- -------------------- - --------------------
                     1001 Dennis               Green
              CUST_ADDRESS_TYP('1 Oracle Drive', '03062', 'Nashua', 'NH', 'US')
              PHONE_LIST_TYP('+1 603 897 4104')
              us  AMERICA                                 100 Dennis.Green@Oracle.com
                         149
              SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-63.13631, 52.485424, NULL), NULL, NULL)
              01-JAN-60 married              M 110,000 - 129,999
              
              
              1 row selected.
              • 4. Re: select sdo_geometry
                Simon Greener
                user8602786,
                update clients_test set CLI_MAIN_GEO_LOCATION = SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE (SUBSTR(Centlong,0,10),SUBSTR(Centlat,0,10),NULL), NULL, NULL) where cli_rid = recs.cli_rid;
                Say i want to put more items in the Null spaces outside of the SDO_point_type().
                Well, you can but put data into those NULL fields but it can only be specific spatial data.

                What data?

                Try reading up on the SDO_GEOMETRY Object Type here: http://docs.oracle.com/cd/E16338_01/appdev.112/e11830/sdo_objrelschema.htm#i1004087

                Example

                The only time you can put non-spatial data into any field of the SDO_GEOMETRY type is in situations where you may wish to record user data in the Z ordinate of an SDO_POINT_TYPE or SDO_ORDINATE_ARRAY field.

                For example, say you were a hydrologist who, in the field, sampled soil PH values at random points recorded by a GPS. The hydrologist stored these observations as follows:
                CREATE TABLE observed_ph (
                   observation_id integer,
                   observation_Date data,
                   longitude number(15,9),
                   longitude number(15,9),
                   soil_ph number(3,1)
                );
                The hydrologist comes to you and requests help to interpolate between these observed points to assign the PH to a grid containing 10mX10m grid cells.

                Firstly you have to turn the above data into an sdo_geometry data.
                ALTER TABLE observed_ph ADD (geom sdo_geometry);
                Now, our spatial interpolation (done via Delaunay Triangulation - see below) requires us to set the Z ordinate of the SDO_POINT_TYPE field of each sample point to the Ph as follows:
                UPDATE observed_ph a SET a.geom = sdo_geometry(2001,4283,sdo_point_type(a.longitude,a.latitude,a.ph), null,null);
                Then we create spatial metadata and a spatial index in the normal manner.

                NOTE: This can be done for any quantitative numeric eg temperature, height, depth....

                The question asked is really one from someone learning about SDO_GEOMETRY data. But I have mentioned Delaunay Triangulation. With my free Spatial Companion 4 Oracle package (downloadable from my website), the triangulation of the above point+Ph data can be done as follows:
                select codesys.SC4O.ST_DelaunayTriangles(CAST(COLLECT(a.geom)  mdsys.sdo_geometry_array),0.000005,9) as dTriangles
                  FROM observed_ph a;
                If anyone wishes to discuss this, please contact me directly simon at spatialdbadvisor dot com

                S.