Behind Oracle Spatial

Oracle Spatial is installed under the MDSYS user account.

MDSYS is a high privilege account with rights similar to the system accounts. The user account is locked by default on more recent versions of Oracle. If your MDSYS user account is unlocked, lock it immediately, you should never need to login as MDSYS, even to create data.

MDSYS has rights with admin option so it can in turn grant rights to other users.
MDSYS is the owner of all the spatial objects, types, metadata, functions, packages and procedures.

If you have installed locator then the MDSYS schema will contain fewer objects than the full spatial option.

Remember, spatial is a licenceable option. Contact Oracle for pricing in your region.

What Are My X and Y Co-ordinates

In Oracle a geometry column hides the number of vertices and their values from you the casual coder.

Here is one method to extract (and display) the values of the geometry vertices.

You start by "loading" a working variable with the geometry in question.

Select geoloc into work_geom from dual;

Then you can get the number of vertices

Select sdo_util.getnumvertices (work_geom) into vert from dual;

For nv in 1..vert loop
Select work_geom.sdo_ordinates (    (nv - 1 ) * 2 + 1) as x,
       work_geom.sdo_ordinates (    (nv - 1 ) * 2 + 2) as y
  Into x_out , 
       y_out 
  from dual;
dbms_output.put_line( 'x = '||x_out||' and y = '||y_out );
End loop;

Obviously this is merely an example but it can be expanded to suit your needs.

Point Overview

Geometric Data (Points)

Geometric co-ordinate points in Oracle spatial can be 2d, 3d or even 4d. These can be representative of anything like a building or location.

Points can also be oriented to show direction and can also be 2d, 3d or 4d. They are usually used to show vector information and orientation. This is purely for display purposes, the point does not move.

A simple point is represented by (x,y) and an oriented point by (x,y,x1,y1).

If you track a car around a maze you can plot the position every five seconds and get a breadcrumb like trail of points. If you add on orientation you can see direction of travel from a single point.

Type of Spatial Data

There are many types of spatial data.

There is the obvious mapping connection of latitude and longitude. There is also a use for spatial data in CAD/CAM systems. This covers both the geographic and non-geographic uses for spatial data.

Geographic spatial data has to have an attribute set to help describe the data as a co-ordinate system i.e. latitude and longitude or BNG (British National Grid). Non geographical data in CAD/CAM systems would not have to set this attribute.

All the data is stored and manipulated in the same way. The only difference may be in the scale of data and permissible accuracy. The centre of a town is, well near enough is usually good enough (metres), whereas getting the size of a window wrong in architects drawings is only a matter of millimeters.

History of Oracle Spatial

We can trace the origins of Oracle spatial back to version 7.3 where more than just point data was stored. This was the Spatial Data Option giving us the initials SDO. You see the SDO initials on most spatially related objects and operators to this day.

Over the next couple of versions the product was renamed until settling upon Oracle spatial (in 10g). Spatial operations are performed in the database using SQL, a different syntax maybe, but in the database nonetheless. So it is not an external bolt-on solution.

Spatial is no longer a seperate part of the system and is built in to the system, from locator (spatial lite) in all versions to the full blown Oracle Spatial in Enterprise Edition. Locator allows you to store and index geomeries (points, lines, polygons, 2d shapes, 3d shapes etc...) and perform relationship operations and distance calculations.

Anything else like volume or area, or intersection and union operations require Spatial.