Re: [Geowanking] Storing Simple Geometries in a RDBMS Model

[Peter Bayley]

Landon, You talked earlier of a point-line-area database (Frank responding that is slow).  This type of structure results in a topological database where every coordinate is stored only once and every linestring only once - a linestring becomes a set of interpolations (straight-line by default) between an ordered set of coordinates while a polygon becomes an ordered set of linestrings (rings). In my experience, this is the right way to store data  (it is closest to what we're actually trying to represent) but it is, as others have said, slow.  My proposed solution is to automatically store a display-ready version of the geometry elsewhere in the database and maintain integrity between the two representations with DB triggers.  99% of the time, people simply want to view data - to zoom and pan - so they just use the display-ready version (could be in SVG format for example, ready to go straight to a browser).  Whenever the display version is not up to the task (spatial analysis etc), then the database will automatically use the topological version.  This is also the case for data editing and building - the topology is used and the display-ready version rebuilt automatically when finished.  The same technique can be used to maintain network-analysis-ready versions of the same topological data. I wish I had the time to do something along these lines with PostgreSQL/PostGis which is what I used most of the time.  There is already some early topology in PostGIS Regards Peter Bayley OpenEarth.   Landon Blake wrote: Thanks Frank and Rich. You guys have been a lot of help. I'll talk this info over with my buddy and we will do some testing. Landon -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Frank Warmerdam Sent: Friday, November 30, 2007 1:17 PM To: geowanking@lists.burri.to Cc: Mark Dennehy Subject: Re: [Geowanking] Storing Simple Geometries in a RDBMS Model Landon Blake wrote: Thanks for that response Frank. I find this very interesting. It seems that you could model just about any data stored in a RDBMS as an object in an object-oriented programming language. For example: If you were writing a database program for used car lots you could represent cars as objects instead of data in individual table cells. But at some point you have to ask, is this worth the effort? I think that is one advantage of the RDBMS model, you can represent all types of data without custom programming. Landon, I did not in any way suggest that the solution to the fully normalized RDBMS model is an object oriented database. Only that for reasonable performance it is desirable to "closely associate" the whole geometry of a feature with the feature record, and have some sort of spatial extents information for features that allows reasonable fast spatial queries. Still, I wonder if the overhead of an additional engine on top of the database makes the speed gap small. Have there been any documented benchmarks that compare this model to others? I think I'll see if my friend is interested in running some tests. I would encourage him to do some comparisons. For instance, try loading a "counties worth" of TIGER data in fully normalized form, and in WKB+extents form and then do various operations. For instance, see how long it takes to fetch all the features and their geometries that match some non spatial criteria (perhaps a single zip code). And see how long it takes to fetch all the features and their geometries that meet a particular spatial criteria (perhaps intersecting a 1mile x 1mile rectangle). Here I'm not even suggesting any special spatial type or specially optimized spatial index ... that's just gravy on the cake. Best regards,

_______________________________________________
Geowanking mailing list
Geowanking@lists.burri.to
http://lists.burri.to/mailman/listinfo/geowanking