3D Operations in DBMS

Although it is possible to maintain objects with 3D coordinates, the functions implemented still omit the z-value.Some exceptions are PostGIS and MapInfo Spatialware Datablade (based on Informix) that do support geometry calculation such as 3D length and 3D perimeter in 3D. Existing spatial functions are developed only on the basis of a geometry model (with exception Oracle Spatial 10g), but they still can perform topological operations such as overlap, touch, etc.

The problem with 3D operations is twofold: first, the existing operations have to extend to consider the z-coordinate, and second, new operations have to be developed on the basis of topological models.

Furthermore, since the two models (geometry and topology) are to be maintained in DBMS, operations for conversion between the two has to be ensured, i.e.operators for building topology and operators for realization of geometry.

3D Topology in Geo-DBMS

At present, no DBMS supports 3D topology.The problems with topology are much more complex compared to the geometry.Usually many application-related issues used to be taken into consideration, e.g. the space partitioning (full, embedding), the object components (volumes, faces), the construction rules (planarity, intersection constraints, etc.).The 3D topological data structures reported currently in the literature can be subdivided in two large groups: structures maintaining objects and those maintaining relationships.While in the first group (object oriented), most of the relationships between the objects are to be derived, in the second group (topology oriented), the representation of the objects has to be derived.Many structures, which are a typical example of explicit storage of objects, also maintain explicit storage of relationships, i.e.singularities.

Clearly, advantages of a topological representation in one of the aspects occur as disadvantages in another aspect.For example, the arbitrary number of nodes per face can be seen as an advantage and disadvantage for different applications.It is very convenient for modelling complex 3D objects (e.g.buildings) since an inappropriate partitioning (into triangles) is not required, but the operators for consistency check become very complex.

Since no agreement on a 3D topological representation exists, alternative approaches are investigated.Instead of looking for an appropriate 3D topological representation, it is suggested extending the DBMS kernel with meta information describing different topological structures in the DBMS.Such topological meta information can then be used both within the DBMS and outside the DBMS. In general, meta information (or system catalogs) of a DBMS contains descriptions of the data stored in the database: tables, attributes and types, and also contains descriptions of the available types and operators.

Ten Common Database Design Mistakes

No list of mistakes is ever going to be exhaustive. People (myself included) do a lot of really stupid things, at times, in the name of "getting it done." This list simply reflects the database design mistakes that are currently on my mind, or in some cases, constantly on my mind.

NOTE:
I have done this topic two times before. If you're interested in hearing the podcast version, visit Greg Low's super-excellent SQL Down Under. I also presented a boiled down, ten-minute version at PASS for the Simple-Talk booth. Originally there were ten, then six, and today back to ten. And these aren't exactly the same ten that I started with; these are ten that stand out to me as of today.

Before I start with the list, let me be honest for a minute. I used to have a preacher who made sure to tell us before some sermons that he was preaching to himself as much as he was to the congregation. When I speak, or when I write an article, I have to listen to that tiny little voice in my head that helps filter out my own bad habits, to make sure that I am teaching only the best practices. Hopefully, after reading this article, the little voice in your head will talk to you when you start to stray from what is right in terms of database design practices.

So, the list:

1. Poor design/planning
2. Ignoring normalization
3. Poor naming standards
4. Lack of documentation
5. One table to hold all domain values
6. Using identity/guid columns as your only key
7. Not using SQL facilities to protect data integrity
8. Not using stored procedures to access data
9. Trying to build generic objects
10. Lack of testing

Geo-DBMS Development

Although designed for non-spatial data, DBMS has been linked with spatial data.Initially GIS vendors used DBMS mainly to store thematic, i.e.non-spatial data.Few GIS vendors (e.g.ESRI) went the way to store both non-spatial and spatial data into DBMS.Such solutions can be referred to as 'top-down' approaches, because from an architectural point of view, the DBMS functionality has been constructed 'under' the GIS application and the GIS application accesses 'top-down' to the geodata stored in the DBMS.

The second way and more interesting development within DBMS was the support of spatial data types.This solution is called a 'bottom-up approach,' because it extends 'low level' DBMS data types and indexes to use them in the upper level of GIS applications.Data analysis on the spatial and non-spatial parts of objects can be executed.Since the nineties more and more commercial DBMSs provide such spatial extensions to offer support of spatial objects.

Geo-DBMSs provide spatial data types and spatial functions (and operations) on them that define the spatial functionality of a Geo-DBMS.A Geo-DBMS knows primitive and composed geometric data types in the same way as its primitive standard data types such as character, string, integer, real etc. A Geo-DBMS is a 3D Geo-DBMS if it:
Supports 3D data types, i.e.point, line, surface and volume in 3D Euclidean space based on 3D (topological or geometrical) models.
Offers 3D spatial functionality, i.e.spatial operations and functions embedded in its query language that can operate with the 3D data types.
Geometry and topology are often used in Geo-DBMS terminology (in contrast to OpenGIS specifications) as synonyms to denote the two ways of describing spatial data types.Geometry data types are described by the x,y,z coordinates of the points composing a data type.Topology data types have references to the unique identifiers of low-dimensional objects.

Although there is a belief that a topological model might be sufficient (geometry can be derived from topology), in the same way one could argue that the same is true for a geometry model, because topology can be derived from geometry.In this respect, one can choose between three different options.
Storing the topological model in the 3D Geo-DBMS (and deriving geometry from it)
Storing the geometric model in the 3D Geo-DBMS (and deriving topology from it)
Storing both models in the 3D Geo-DBMS
In the first approach the topological relationships between object parts, such as 'all surfaces belonging to the boundary of a 3D volume object' are stored in a topology model.The location of objects, i.e. the x,y,z coordinates of its defining points, are not part of the topology model.This approach can quickly identify topological properties, e.g. 'find neighboring walls,' but it is inefficient in queries needing coordinates, e.g. 'visualize the object'.

The second approach prevents the disadvantage of the first approach.However, topological queries have higher computational complexity.

The third approach seems to be the best solution: it allows flexible topological and geometric database queries executed efficiently by accessing the topology and geometry model in the Geo-DBMS.With these two models all relevant types of spatial database queries can be executed:
topological database queries (neighborhoods etc.),
metric database queries (distances),
geometric database queries (e.g.spatial search inside a 3D box).