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

[Alex Mandel]

I'm a little lost here, in my experience a vector layer becomes a table,
not multiple tables and all the geometries are stored in a blob column
no matter what type it is.
Of course if you have multiple vector types in the same table this can
cause issues with various spatial operations, so you either need to
separate them out or subquery when you want to perform spatial
operations to make sure you only use compatible types.

What tool did you use to import the layer into POSTGIS?

Alex

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.
> 
> 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
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-user