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

Geometry to csv string

This function will change a geometry into a comma delimited string.

CREATE OR REPLACE FUNCTION geom_to_string (p_geometry IN MDSYS.SDO_GEOMETRY)
   RETURN VARCHAR2
AS
   str      VARCHAR2 (4000)    := '';
   vgeo     MDSYS.SDO_GEOMETRY := p_geometry;
   x        NUMBER             := 0;
   y        NUMBER             := 0;
   isi      NUMBER             := 0;
   i        NUMBER             := 0;
   v_dims   NUMBER (2)         := 0;
BEGIN
   v_dims := vgeo.get_dims ();
   isi := vgeo.sdo_ordinates.COUNT / v_dims;

   FOR i IN 0 .. isi - 1
   LOOP
      x := vgeo.sdo_ordinates (i * v_dims + 1);
      y := vgeo.sdo_ordinates (i * v_dims + 2);
      str := str || ',' || FLOOR (x) || ',' || FLOOR (y);
   END LOOP;

   str := SUBSTR (str, 2, LENGTH (str));
   RETURN str;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN '';
END;

I needed this function to return the x and y co-ordinates as a comma delimited string to a plotting package for display.

SRID

You can view the SRID information in existing SDO_GEOMETRY columns with the following

SELECT g.GEOMETRY.SDO_SRID FROM MY_TABLE g;

assuming that MY_TABLE has a sdo_geometry column called geometry.

Well, what is the srid?

SRID stands for Spatial Reference Identifier.

Without setting a SRID all values in Oracle spatial are treated as Cartesian.
Using the UNIT parameter will never be supported (for SDO_NN, SDO_WITHIN_DISTANCE, BUFFER, LENGTH, DISTANCE, AREA, etc).
Coordinate system transformations will never be supported, so you won't be able to use a query window or compare to a geometry that has an SRID set.
Most/all tools support spatial data with a NULL SRID.

The SRID allows the correct projection, transformation and relational operations between different co-ordinate systems. The most commonly used SRID in Oracle Spatial is 8307 which is the whole earth projection WGS84 using latitude and longitude.

The complete list of Oracle SRIDs can be found in the MDSYS.CS_SRS table.

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.

Geospatial Viewer

ESRI's ArcGIS Explorer is a lightweight desktop client for ArcGIS Server that yields a common operating picture, in two and three dimensions, while performing queries and analysis on the underlying data. The application accesses the full GIS capabilities of ArcGIS Server, including spatial analysis and 3D services, and can also use data layers and services from ArcIMS and ArcWeb Services. Local data such as shapefiles, file geodatabases, KML, JPEG2000, GeoTIFF, and IMG are supported.

Spatial Structure

The thinking behind spatial.

In an oracle database, spatial data is stored in a layer, which in simple terms can be considered to be a column in a table where the datatype is SDO_GEOMETRY. This is a simplified view of the model underlying Oracle Spatial.

A layer contains one or more geometries (at least one row in the table).
Geometries consist of one or more elements (at least one entry in the geometry object type).

To take a simple example: A point is represented in the layer as a geometry with one element of type point. Points are usually something indicating a specific position like a point of interest.

More complex shapes and are built up by combining more than one element into a geometry. These may, in the real world, be roads, coastlines, borders, rivers or even continents. These are built up by the combination of many elements, each element being a simple type, giving the geometry. In most cases the elements will follow on from each other to build up a complex geometry, but there is no restriction on a geometry having more than one 'shape', so a geometry could contain the 'shapes' for all the countries in Europe or all the States in the USA.

This means that your GIS application can show maps consisting of points of interest and routes.

To recap: The Spatial Model

Layer --> Geometry --> Element --> (point, line, polygon, compound line, compound polygon).

It is usual practice to keep related geometries in the same layer (column), so the 'roads' (one layer) are not mixed with the 'county borders' (separate layer).

Polygons

Different Shapes

A geometry datatype in Oracle can be used to hold any imaginable real world shape.

The shapes are as you would think points, lines, compound lines, polygons and mixtures of all the items in the list. So a single geometry object can "contain" a polygon and lines and points. Though it is not a good idea to (mis)use the sdo_geometry datatype in this way. All of the shape primitives can be further extended from 2D to their 3D equivalents.

I have previously looked at points and will now look at polygons.

• A polygon encloses an area. In other words, its start and end points are the same.
• A polygon can contain other polygons which can be thought of as islands or lakes which in turn can contain polygons ad infinitum.
• A polygon can be optimised by two points (rectangle) or three points (circle).
• The "sides"of a polygon cannot cross. Self crossing lines are valid. It is allowable to split such a geometry into self contained polygons that when viewed together look like a self crossing polygon.
• A polygon can be made up of any combination of compound linestrings.

There are many more rules that cover what is and isn't a valid polygon in the oracle documentation.

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.

Applied Imagery

3D Data Modeling




QTModeler from Applied Imagery builds and manipulates three-dimensional (3D) models of as many as 200 million vertices or 100 million points and builds both point clouds and gridded surface models. Users of version 5.1 can align multiple scans from Optech ILRIS 3D laser scanners with digital elevation model editing tools and manually remove spikes or other terrain features with area-smoothing or flattening tools. QTModeler is optimized for 3D LIDAR (light detection and ranging) and SAR (synthetic aperture radar) survey data.

Applied Imagery. www.appliedimagery.com/

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.

GPS and Maps

Every GPS unit out there that supports mapping only supports "vector" maps. These vector maps are not scanned images. Think AutoCAD drawing and it should help. These type of maps take up less memory than raster(or scanned) maps like the ones Maptech sells. Now you can buy software that provides vector maps for GPS units. However, there's no standard format. So each GPS manufacturer provides their own format. So if you buy a Garmin unit that supports maps, then you would need to purchase their map software called MapSource. If you buy a Magellan unit, then you would need to buy their map software called MapSend.

It is possible in a round-about way of getting raster map support for GPS units. How it works is you buy a PDA(Palm or PocketPC) and then run a mapping program on it that supports GPS units. You copy the maps that are supported by the mapping program to the PDA and then connect a GPS to the PDA via a cable, Bluetooth GPS or compact flash GPS. The mapping software just communicates with the connected GPS unit and displays your position on the map, along with routes, tracks, etc. Most GPS mapping software give you the same functionality that the GPS unit does.

Worth a thought.

Take Me Home

Show me the way to go home

One of the pleasures of moving to a new city is learning your way around – but what if technology means we never get lost again?

London has some strange and twisted corners, and its incomprehensible traffic regulations and confusing maze of one-way streets are pleasantly intimidating to some of our foreign visitors, especially some Americans. Once you've got used to the nice wide American streets, in a neat grid of alternating streets and avenues, clearly numbered to simplify your navigation requirements, some of London's tangled medieval streets, in many places designed for a couple of horses to pass comfortably, can seem completely bonkers.

A powerful emblem of London's pride in its own byzantine complexity is The Knowledge, that ritualised memory game which ensures not everyone has what it takes to drive a black cab. Yet most minicabs I take now are driven by people with knowledge provide by Tom Tom, or Garmin, or Mio. It's become convenient to know the exact post code of the people you're going to visit, so that the driver can get a lock on your target destination more quickly. When you're returning home after a heavy night, the sat-nav's blinking interface is now as much a part of the drunken ride home as your sleepy companion or the bittersweet pop music on Heart or Magic radio, tuned too high to block out but too quietly for you to be able to reasonably ask that it be lowered.

I started my driving life in South Africa and have only just started driving in London, and I'm torn: the gadget-head in me knows that it's a perfect opportunity to stick a sat-nav on the dash and see what this technology is all about. It will stop me getting lost as I concentrate on driving. There's also a marriage to preserve. When a sat-nav gets it wrong, no one has to stop the car "to cool off," and no one else has to explain that they meant the other East Finchley turn off, which some people would have realised if they weren't so bloody stubborn.

And yet – surely it's cheating? Surely the knowledge of London's grimy fly-blown streets should be hard won: turn left at the Hoover Factory and hum the Elvis Costello song to yourself; surf the horrors of the Holloway Road every grim day of your commute; go into orbit around London on the M25 and discover for yourself its Bermuda-Triangle like ability to make vehicles mysteriously vanish from where they belong and make them reappear somewhere quite other. Knowledge, built up like coral, through trial and error.

The end-game for sat-nav systems is a world in which GPS chips get integrated into our phones. Eventually affluent people in the developed world will start to forget what it was like to be lost - just as we are all beginning to forget what research was like before Google. I suspect I shall crack and get one of these little information devils: it's probably safer than arguing over the torn pages of a coffee-stained atlas at 50 mph. It's just that I'd quite like it to be my London, not London according to TomTom, or Garmin, or Mio. What do you think? Does sat-nav make your life better or worse?

Let me know.

A New Voice

I know that some car GPS navigation systems offer customised celebrity or funny voices for their products.

Just imagine choosing something a bit more adult in tone than the standard offering and then having to explain to your mom/kids/better half why the GPS is letting rip at you.

Definitely one of those moments to be avoided.

Driving the Wrong Way

I have always relied on a map to get me to somewhere I have never been to before. You know, a different town buried more than three or four turns off a main road.

With the advent of in car GPS systems this has led to more than one case of people not understanding the route to their destination. They know where they are starting from and where they will end up, but the actual route, well that they haven't a clue about. Call me old fashioned, but I like to know about the middle bits, just in case I need to 'divert' for some reason or other in an emergency.

We have all seen or heard the stories of someone who was following the in car GPS so carefully and exactly that they got into trouble, driving into streams, buildings and roadworks. Is it their fault - most assuredly. The box on the dashboard, hasn't a clue about the roadworks which just sprung up overnight and that burst watermain or even the accident causing the tailback.

So eyes on the road drivers and let your tomtom or garmin help you get to your destination, not lead you astray.

Frustration

Mapping in the UK is hamstrung. All the data is owned by the Ordnance Survey who will happily licence you their data at an astronomical cost. This excludes all but the most well funded organisations or individuals from using the data.

For the rest of us, well there are a couple of groups whose aim is to collect and collate open source data and then make this data freely available to all who want a copy. While not perfect for those dedicated and hardcore mapping enthusiasts it is certainly adequate for developers dipping their toes into the new world of online mapping.

The openstreetmap project aims to take all of the uploaded GPS tracks and build a UK street map.

Freethepostcode on the other hand is attemting to get people to submit GPS co-ordinates of their postcode.

Both are admirable projects and I wish them all the best for the future.