Database Software

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

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');

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.

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