Re: [Qgis-user] Relational Databases and PostGIS formatting of Vector Data
Alex Mandel wrote: 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. That's what I'm curious about. Each vector is becoming a table unto itself. That's not proper normal form for a relational database. 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. Yes, this is exactly what you want when using a relational database in the proper manner. What tool did you use to import the layer into POSTGIS? Quantum GIS. ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Relational Databases and PostGIS formatting of Vector Data
Peter, What tool did you use to import the layer into POSTGIS? Quantum GIS. My guess is you actually used the SPIT plugin, which is a C++ plugin included with QGIS, and is a gui frontend to shp2pgsql... Carson ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Relational Databases and PostGIS formatting of Vector Data
Yes, that is correct. Are there any other 'shp ingesters' for use with QGIS? AFAIK the PostGIS manager plugin is also able to load shapefiles into PostGIS, and this is a nice simple way to manage your database directly from within QGIS. I am thinking that my complaint regarding proper normal form for vector data ingested into postgresql should also be tempered with the idea that any database user could reorganize the ingested data into any format they desire. Therefore, it's not as though anything is actually irreparably disorganized or broken. Technically one could just add a trigger to the system catalogs to check if a new table has been added to the database and then go from there. Yes, I think you're probably right here. It would be difficult to create a tool that would import data into pgsql the way that everyone or anyone would like... so KISS is the best solution (keep it simple s...) from here it's up to the database maintainer to keep things the way they want it... Th postgis user lists would probably benefit from this discussion, have you suggested similar things there? Most of us on the QGIS lists aren't database experts, so a better discussion would probably come out of the postgis lists. Carson -- Carson J. Q. Farmer ISSP Doctoral Fellow National Centre for Geocomputation (NCG), National University of Ireland, Maynooth, Email: carson.far...@gmail.com Web: http://www.carsonfarmer.com/ http://www.ftools.ca/ ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Relational Databases and PostGIS formatting of Vector Data
On Thu, Apr 9, 2009 at 7:53 PM, Peter Willis pet...@borstad.com wrote: Carson Farmer wrote: Peter, What tool did you use to import the layer into POSTGIS? Quantum GIS. My guess is you actually used the SPIT plugin, which is a C++ plugin included with QGIS, and is a gui frontend to shp2pgsql... Just to clarify: SPIT plugin is not a frontend to shp2pgsql, it has its own logic. In addition to SPIT you can try to shp2pgsql command line tool or PostGIS manager plugin for qgis which actually is a simple shp2pgsql GUI :) Martin ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Relational Databases and PostGIS formatting of Vector Data
On Thu, Apr 9, 2009 at 7:53 PM, Peter Willis pet...@borstad.com wrote: Carson Farmer wrote: Peter, What tool did you use to import the layer into POSTGIS? Quantum GIS. My guess is you actually used the SPIT plugin, which is a C++ plugin included with QGIS, and is a gui frontend to shp2pgsql... Just to clarify: SPIT plugin is not a frontend to shp2pgsql, it has its own logic. I stand correctly ;-) C ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] Relational Databases and PostGIS formatting of Vector Data
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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Relational Databases and PostGIS formatting of Vector Data
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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
