Example statement 10

Insert a compound line string:

INSERT INTO roads values ('obj_2', mdsys.sdo_geometry(2002, null, null,

mdsys.sdo_elem_info_array(1,4,2,1,2,1,9,2,2),

mdsys.sdo_ordinate_array(15,10, 25,10, 30,5, 38,5, 38,10, 35,15, 25,20)));

Example statement 09

Select any objects within 1.35 distance units from the query window:

select a.feature_id

from target a

where sdo_within_distance(a.shape, :theWindow, 'distance=1.35') = 'TRUE';

Example statement 08

Performs a consistency check to validate a geometry:

select c.name, sdo_geom.validate_geometry(c.shape, m.diminfo)

from cola_markets c, user_sdo_geom_metadata m

where m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE'

and c.name = 'cola_c';

Example statement 07

Compute the difference between two geometries:

select sdo_geom.sdo_distance(c_b.shape, m.diminfo, c_d.shape, m.diminfo)

from cola_markets c_b, cola_markets c_d, user_sdo_geom_metadata m

where m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE'

AND c_b.name = 'cola_b' AND c_d.name = 'cola_d';

Example statement 06

Return the minimum bounding rectangle of all geometries in a column:

select sdo_tune.extent_of('cola_markets','shape')

from dual;

Example statement 05

Create Index:

create index states_geom on states (geom)

indextype is mdsys.spatial_index

parameters ('sdo_level = 7');

Example statement 04

Estimate tiling level:

select mdsys.sdo_tune.estimate_tiling_level (

'STATES', 'GEOM', 10000, 'LAYER_EXTENT') "ESTIMATE LAYER_EXTENT"

from dual;

Example statement 03

Select all the counties within a certain area:

select sum (mdsys.sdo_geom.sdo_area (c1.geom,

(SELECT diminfo

FROM sdo_geom_metadata

WHERE table_name = 'COUNTIES'

AND column_name = 'GEOM'))) area

from counties c1,

counties c2

where c2.state = 'New Jersey'

and c2.county = 'Passaic'

and mdsys.sdo_relate (c1.geom, c2.geom, 'mask=TOUCH querytype=JOIN') = 'TRUE';

Example statement 02

Select all the cities within a particular area:

select c.city, c.pop90

from cities c

where mdsys.sdo_relate (

c.location,

mdsys.sdo_geometry (3, null, null,

mdsys.sdo_elem_info_array (1,3,3),

mdsys.sdo_ordinate_array (-109, 37, -102, 40)),

'mask=ANYINTERACT querytype=WINDOW LAYER_GTYPE=POINT') = 'TRUE';

Example statement 01

Select all the counties that are around a particular state:

select county, c.state_abrv

from counties c, states s

where s.state = 'FLORIDA'

and mdsys.sdo_filter (c.geom, s.geom, 'querytype=JOIN') = 'TRUE';