[Qgis-user] Relational Databases and PostGIS formatting of Vector Data

[Gary Sherman]

On Apr 8, 2009, at 3:25 PM, Peter Willis wrote:

> Hello,
>
> I just ingested a MULTIPOLYGON vector into a PostGIS enabled
> database and realized that each vector becomes a unique TABLE
> in the database.
>
> Is this really necessary?
>
> Why not use proper relational database techniques and have
> all vectors of a specific type go into a single table
> with a unique ID for rows that belong to a specific
> vector?
>
> Shouldn't I have tables named:
>
> LINES
> MULTIPOLYGONS
> POLYGONS
> POINTS
>
> that link to a table named VECTORS by a unique ID.
>

 From a data modeling perspective, you are viewing the lines,  
polygons, points, etc. as entities. When modeling features (lakes,  
towns, etc.) they *are* the entity and have attributes. A lake has a  
name, depth, area, and so forth and a spatial representation.

For GIS usage it makes sense to model the entity, not its spatial  
representation.

PostGIS (as well as Oracle Spatial, Informix DataBlade) use this  
scheme which is based on the OpenGIS "Simple Features Specification  
for SQL" (see http://www.opengeospatial.org/standards/sfs).

You end up with 200 tables (think layers) with attributes and a  
spatial representation. The scheme you suggest would add complexity  
not only in the database design, but also client implementation.

>
%<.............
> If we're going to use a database, we should make use of the
> facilities provided by a database and stop thinking in terms of
> flat files from the 1970s.

I think when viewed from the perspective that features==entities  
you'll find that the database is being used appropriately.

If you want more discussion on this issue, I suggest you post to the  
PostGIS users list where you'll get the attention of the developers  
among others. This isn't really a QGIS issue---we make use of PostGIS'  
OGC certified implementation.

Best...

-gary

-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-
Gary Sherman
Chair, QGIS Project Steering Committee
-Micro Resources: http://mrcc.com
   *Geospatial Hosting
   *Web Site Hosting
-Desktop GIS Book:
   *http://desktopgisbook.com
"We work virtually everywhere"
-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-