Hi Josh et al,
Sorry for the tardy reply, and thanks for your comments. Any
suggestions or pointers on robust database design will be greatly
appreciated.
Josh Berkus wrote:
> Jim,
>
>
>>> I'm trying to figure out what support PostgreSQL
>>
>> offers for SQL99
>>
>>> abstract data types.
>>
> I'm a little curious why what you're attempting couldn't be
> done with two columns rather than inventing your own data
> type. As somebody who often rescues databases gone bad,
> composite data types have not earned a warm place in my
> heart ...
>
> -Josh Berkus
What we are attempting is the storage of vector data for a geographical
(or spatial) information system in a database. We hope to base the
implementation on the upcoming standard from the ISO TC/211 committee.
More information can be found at http://FMaps.sourceforge.net/ - the
webpages need a major revamp so the best place to look for current
developments is in the mailing list archive. A good source of info can
be found at http://gdal.velocet.ca/projects/osvecdb/index.html,
especially relevant is the comparison of the SQL/MM, OGC, and ISO TC/211
standards (http://gdal.velocet.ca/projects/osvecdb/comp-mm-ogc-tc211.pdf ).
To answer your question, it is a bit hard to say at the moment as the
design schema for our project has only just been started. The draft
versions of the ISO standard that I have seen use an object oriented
data model, so to me it makes sense to try and keep the database schema
as close as possible to this (minimise data impedance).
Briefly, at its' simplest the schema will probably use a two tier approach.
Tier 0ne
------
The original data stored in the most flexible way that we can think of,
with associated metadata tables.
Tier Two
-------
These will effectively be persistent views on the T1 tables structured
for efficient access by client applications. OK, as far I know no such
beast as a persistent view exists in the SQL standards, but that is
probably the best way to describe what I have in mind. Using views as
currently implemented in PostgreSQL would probably not be viable as it
is likely that, if multiple spatial reference systems are defined on a
area of interest, reprojection of the geometry objects would be a
performance killer.
cheers,
John
