Re: [Qgis-user] Relational Databases and PostGIS formatting of Vector Data
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi 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... Small correction here - SPIT is not a frontend to shp2pgsql but rather an independently developed import tool. Regards Tim > > Carson > ___ > Qgis-user mailing list > Qgis-user@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/qgis-user > - -- Tim Sutton - QGIS Project Steering Committee Member (Release Manager) == Please do not email me off-list with technical support questions. Using the lists will gain more exposure for your issues and the knowledge surrounding your issue will be shared with all. Visit http://linfiniti.com to find out about: * QGIS programming and support services * Mapserver and PostGIS based hosting plans * FOSS Consulting Services Skype: timlinux Irc: timlinux on #qgis at freenode.net == -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAknjAxUACgkQqk07qZdiYjcxHwCfXAfF2eDmXzKfX6iyRoPh1vU/ yhUAn3D7vEzlhlSCeN/jUVveSRXM8uAI =099Z -END PGP SIGNATURE- ___ 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 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" -_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_- ___ 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
Carson Farmer wrote: 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. 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... Well, there should be some type of organizational standard so that applications, like QGIS, can query the database properly. Then again, that could be handled by a database function that simply provides a table/view of postgresql data in the proper organization. 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. No, I haven't suggested this on that list. My only real worry was that if I import 200 vectors, I will end up with 200 different tables in the database... 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
On Thu, Apr 9, 2009 at 7:53 PM, Peter Willis 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
Re: [Qgis-user] Relational Databases and PostGIS formatting of Vector Data
On Thu, Apr 9, 2009 at 7:53 PM, Peter Willis 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
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
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... Carson Yes, that is correct. Are there any other 'shp ingesters' for use with 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. 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
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
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
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
