[OSGeo-Discuss] designing databases, organizing data formats to work with open source and proprietary GIS

[Simon Greener]

Karsten,

Interoperability with ESRI GeoDatabases (SQL, not file) have their difficulties but they aren't insurmountable.

To address such interop one must make clear distinctions at differing tiers.

The first is that the chosen "storage format" is the "native" one for the chosen database product: That is Oracle's SDO_Geometry,
Microsoft's geomery/geography, or PostgreSQL/PostGIS's geometry/geography. Interop with ESRI's ST_Geometry is possible but
widespread support across different proprietary and open source product is problematic.

The second is the database data model. One should not use ESRI's GeoDatabase Design Tool as the ONLY tool for defining the data model.
Use it, yes, for the ESRI side of the data model/access question, but additionally, one should use a horizontal market data modelling tool
(like Enterprise Architect which supports ArcGIS data model xml) to QA the model produced by the ESRI tool, to specify more open data
types, to add additional documentation (that most ESRI customer shops do in Word documents by hand), and to forward engineer those
components of the model that ESRI rather likes to keep to itself and not share with others (eg constraints, lookup tables/coded domains, foreign keys etc).

The final aspect is ensuring that ESRI software can do what it believes it needs to be able to do if you are using topological rules,
versioning etc.

In essence the activities are:

1. Define the model so that it can serve an intelligent data to multiple clients independent of vendor. This is best practice data management
    fully in line with database theory.
2. Then "configure" the ESRI interface (treat ArcSDE as a JDBC interface for ESRI clients) so that it can use the model as if it created it itself.

I have had success with this in the past. It isn't rocket science though the latter can require a bit of clever thinking.

regards
Simon
-------- Original Message -------- Subject: 	[OSGeo-Discuss] designing databases, organizing data formats to work with open source and proprietary GIS
Date: 	Wed, 10 Aug 2011 10:42:55 -0700
From: 	karsten vennemann <karsten at terragis.net>
Reply-To: 	OSGeo Discussions <discuss at lists.osgeo.org>
To: 	<discuss at lists.osgeo.org>



Hi all,

in the near future I will have the opportunity to help design databases, decide on data formats (files data) for an international organization that wishes to be able to use both proprietary and open source based systems, mostly in web mapping solution but also possibly on the desktop. The task will be to design and organize the data stores in a way that both types of systems - open source (e.g. MapServer, OpenLayers) and proprietary systems (ESRI Arc Server) can use them well, and along the way to try to avoid too much data duplication (having to store data in multiple formats just to make them accessible) .

This sounds to like a exiting & useful, fun task, but given the limitations of both systems (regarding input data that might not work out of the box- namely file Geodatabases in open source solutions, and PostGIS data in ESRI products) might be not totally trivial ;)

I was wondering if anybody has done work on this, has implemented systems facing the same issues or knows of projects or reports that have been dealing with similar issues. Also I anybody has comments about what data storage solution you would recommend and comments about the pro and cons of certain storage designs please send it to the list.
Looking forward to hear what other have come up with.
Thanks a lot

Cheers
Karsten

-- 
Holder of "2011 Oracle Spatial Excellence Award for Education and Research."
SpatialDB Advice and Design, Solutions Architecture and Programming,
Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional
Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist.
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.
Website: www.spatialdbadvisor.com
Email: simon at spatialdbadvisor.com
Voice: +61 362 396397
Mobile: +61 418 396391
Skype: sggreener
Longitude: 147.20515 (147° 12' 18" E)
Latitude: -43.01530 (43° 00' 55" S)
GeoHash: r22em9r98wg
NAC:W80CK 7SWP3
-------------- next part --------------
Skipped content of type multipart/related