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

[Peter Willis]

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.

OR!! maybe a single table called VECTOR_GEOGRAPHY that
has a geography column for each of LINE,MULTIPOLYGON,POLYGON, and POINT
plus a VECTOR_TYPE column to indicate which column the geography resides 
in. Having NULL as a default for these columns would make an easy check
for availability of the type for the current vector row.
This would also allow for trigger functions to automatically fill out
the geography of the other vector types, in the current row, by
extracting them from a higher order entry (ie: extract lines, points and 
centroids from polygons).

Attributes should also be associated to the vector geography
indirectly and placed in a series of tables something like:

VECTOR_ATTRIBUTES---NAME
		|---VALUE_TYPE
		|---VECTOR_ID
		|---VECTOR_ITEM_ID
		|---THIS_ATTRIBUTE_ID

VECTOR_ATTRIBUTE_VALUES---THIS_VALUE_ID
		      |---ATTRIBUTE_ID
                       |---VALUE_BASE_64_ENCODED

I realize that this causes some overhead but it would make
querying available vector coverages and attributes
a bit easier than having to change tables for each individual
vector.

There is also the added benefit pf being able to query for
vector entities and sub-entities that fall within a specific
viewing area. Thus you wouldn't need to read-out/redraw the
complete vector if you're not looking at a broad enough
scale to see it, improving rendering time for large composite
vectors.

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.

My opinions,

Peter