[Geowanking] Proposal: Improving on the ESRI Personal Geodatabase?

[Frank Warmerdam]

On 11/10/05, Alex Willmer <alex at moreati.org.uk> wrote:
> In my day job I support an ArcSDE/ArcIMS/ArcGIS infrastructure. The GIS
> specialists frequently use Personal Geodatabases for offline editing,
> temporary storage & project archival.
>
> For those unfamiliar a Personal Geodatabase (PGDB) is an MS Access
> database (.mdb) file with some predefined tables and table structures. A
> PGDB can store multiple vector and raster layers with attributes, upto a
> total size of 2 GiB. An overview can be found here:
>
> http://www.esri.com/software/arcgis/geodatabase/about/personal.html

Eric,

Currently OGR has the ability to read ESRI Personal Geodatabases.
I also know of someone who has has created them from scratch
without ESRI software.

> Recently I've been pondering that a similar concept would be of use to
> opensource GIS systems. It could provide a format featuring:
> - a single file able to hold multiple datatypes, and layers with indexed
> access
> - a zero-admin, portable implementation of Simple Features for SQL
> - a common transport for moving geodata between disparate databases
> - an alternative to shapefiles with fewer limitations on attributes,
> types, identifiers etc
>
> I don't suggest reverse engineering the PGDB format (although this would
> also be of great use). I think something based on SQLite would fit the
> bill. A
>
> The SQLite library is already popular as an embedded database in
> opensource software. It smashes the PGDB limit of 2GiB (SQLite 3 can
> potentially address files of 2 TiB). Finally, unlike mdb, SQLite has no
> scripting built in - so virus scanners are less likely to interfere with
> any file format based upon it (ie mail filters should let it through and
> on-access scanners should ignore the potentially large files it would
> support.)

OGR also supports reading and writing geospatial data in SQLite
format.  Generally speaking I love SQLite for the reasons you mention.
The thing I don't like about it is that the type support is so weak which
can cause problems when you want it to preserve type semantics.

The OGR SQLite driver is pretty simplistic currently.  It only supports
geometries if they are in a column named WKT_GEOMETRY and
they have to be in WKT format.  It also does not currently support
any spatial indexing.

The OGR SQLite driver also does not currently preserve spatial
reference information.

If you were interested in pursuing your idea, I would like to put OGR
forward as at least one based on which to implement it.  We could
extend the driver to support OGC style GEOMETRY_COLUMNS
tables, spatial reference tables and indexing by bounding box.  As
well we likely ought to switch to WKB (at least optionally) for the
geometry column.

Then we could look at adding some of the other supporting
tables you would find in a personal geodatabase as is seen
appropriate.  Stuff describing relationships and so forth.  I
have mostly just ignored those taking a very "OGC Simple Features"
sort of approach in OGR.

Best regards,
--
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent