| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Spatial Attributes
FinnE wrote:
>
> I've tried searching both 9iR2 and 10g documentation - it looks like
> an oracle table can only have one SDO_GEOMETRY type column. I
> couldn't find anywhere where more than one SDO_GEOMETRY
> column/attribute was allowed for a record.
The ability to use a 'user defined type' multiple times in a table is not a characteristic of the Spatial Option (or the subset of Spatial: Intermedia Locator, or any other aspect of Intermedia) - it's inherent in 'object types'. I would expect to be able to create multiple columns of type 'Address', with name 'Shipping', 'Billing', and so on, if needed (no matter how much that goes against the 3NF grain.)
Therefore I would not expect that to be in the Spatial documentation.
<sigh/> So ... why not try it?
Here I create a table CITIES with 2 geometry columns LocationA and LocationB, then data fill them both with points and select the point data. They do not both have to be points - I just don't want to hand-fill a whole geometry.
(I used downloaded 9iR2EE on Win2000Pro for this.)
/Hans
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining Option
JServer Release 9.2.0.1.0 - Production
SQL> CREATE TABLE CITIES (
2 LOCATIONA MDSYS.SDO_GEOMETRY,
3 LOCATIONB MDSYS.SDO_GEOMETRY,
4 CITY VARCHAR2(42),
5 STATE_ABRV VARCHAR2(2),
6 POP90 NUMBER, 7 RANK90 NUMBER);
Table created.
SQL> INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME,
DIMINFO, SRID)
2 VALUES ('CITIES', 'LOCATIONA',
3 MDSYS.SDO_DIM_ARRAY
4 (MDSYS.SDO_DIM_ELEMENT('X', -180.0, 180.0, 0.5),
5 MDSYS.SDO_DIM_ELEMENT('Y', -90.0, 90.0, 0.5)
6 ),
7 8307);
1 row created.
SQL> INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME,
DIMINFO, SRID)
2 VALUES ('CITIES', 'LOCATIONB',
3 MDSYS.SDO_DIM_ARRAY
4 (MDSYS.SDO_DIM_ELEMENT('X', -180.0, 180.0, 0.5),
5 MDSYS.SDO_DIM_ELEMENT('Y', -90.0, 90.0, 0.5)
6 ),
7 8307);
1 row created.
SQL>
SQL> COMMIT;
Commit complete.
SQL> SQL> desc cities
Name Null? Type ----------------------------------------- -------- ---------------------------- LOCATIONA MDSYS.SDO_GEOMETRY LOCATIONB MDSYS.SDO_GEOMETRY CITY VARCHAR2(42) STATE_ABRV VARCHAR2(2) POP90 NUMBER RANK90 NUMBER
SQL> insert into cities (
2 locationa, locationb, city, state_abrv ) values ( 3 mdsys.sdo_geometry(3001, 8307, mdsys.sdo_point_type(-75.2, 43.7, 200), null, null),
4 mdsys.sdo_geometry(3001, 8307, mdsys.sdo_point_type(-55.6, 45, 120), null, null)
5* 'Regina', 'SK');
'Regina', 'SK')
*
ERROR at line 5:
ORA-00917: missing comma
SQL> 4
4* mdsys.sdo_geometry(3001, 8307, mdsys.sdo_point_type(-55.6, 45,
120), null, null)
SQL> ap ,
4* mdsys.sdo_geometry(3001, 8307, mdsys.sdo_point_type(-55.6, 45,
120), null, null),
SQL> l
1 insert into cities (
2 locationa, locationb, city, state_aSQL> select * from cities;
LOCATIONA(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
CITY ST POP90 RANK90 ------------------------------------------ -- ---------- ----------SDO_GEOMETRY(3001, 8307, SDO_POINT_TYPE(-75.2, 43.7, 200), NULL, NULL) SDO_GEOMETRY(3001, 8307, SDO_POINT_TYPE(-55.6, 45, 120), NULL, NULL)
Regina SK
SQL>
brv ) values (
3 mdsys.sdo_geometry(3001, 8307, mdsys.sdo_point_type(-75.2, 43.7, 200), null, null),
4 mdsys.sdo_geometry(3001, 8307, mdsys.sdo_point_type(-55.6, 45, 120), null, null),
5* 'Regina', 'SK')
SQL> /
1 row created.
Received on Fri Feb 20 2004 - 05:08:41 CST
 |
 |