[postgis-users] Re: PostgreSQL/PostGIS and ArcGIS Server 9.3

[Simon Greener]

A few comments from someone with a little bit of experience with ArcSDE.

Note: I have no up to date knowledge of licensing issues so any comments I make about things like Direct Connect are conditioned by my statement that my understanding may be wrong or out of date.

A few issues have been raised.

1. Geometry Models

ESRI's ArcSDE existed before ESRI did and also before the OGC SFS standard existed. In the original SDBE polygons were allowed to have invesions. An inversion is where the interior boundary touch a vertex of the outer boundary. An inversion touching a vertex of the outer shellis considered poart of the outer shell.

I would expect that these polygon types will be supported by ESRI's ArcSDE on PostgreSQL.

Do they cause problems? Well, an ESRI client on PostGIS would, via ArcSDE, be able to store polygons with inversions as they can with Oracle. With Oracle this is not a problem as Oracle does not 'auto-validate' the data stored by any GIS client. It will only report errors if one runs sdo_geom.validate_geometry(). The ESRI client will be able to read them (as it does nothing more than an sdo_filter when querying data from the database, doing all its own topological comparisons and buffering in the middle-tier) but what other clients do with them depends on the software. However, if one is running a mixed client environment a good set of "editing" standards to control operators should be able to have avoid the use of inverted polygons.

There is one thing to note in a mixed-client environment and that is this: if a non-ESRI client writes an invalid geometry to a the database then when ArcSDE constructs a query over an area that includes this feature ArcSDE will discover the error (as it passed all queried features into its own shape checking routines) and stop the query. I discovered this behaviour many years ago and so constructed Oracle Jobs that checked the day's edits at the end of the day, tried to autofix those it found problems and send an email to the person who had constructed the geometry to check it the next day. As at ArcSDE 8.3 there was no configuration parameter to disable this (rather anal-retentive) behaviour. It might have changed with later versions (but I doubt it).

2. SDE -> Many Databases

An ArcSDE instance can only talk to a single Oracle database instance. If you have data in multiple instances then you either need multiple ArcSDE instances (and licenses $$$) or you can use database links and views. Though, ArcSDE up till 9.1 still did not support the registration of views in Oracle (one had to trick it by creating a table, registering it, dropping it and replacing it with a view). I would think it likely that this architecture will continue with ArcSDE 9.3 on PostgreSQL.

3. Direct Connect

Direct Connect was free in ESRI clients up until about 8.3 or 9.0. With the rise of Gigabit ethernet, many customers discovered that the performance drop for Direct Connect on 10/100Mbit networks was no longer an issue so they dropped maintenance on ArcSDE. This must have hurt ESRI's pocket because they changed the rules and started charging for Direct Connect. Whether this is still the case given their bundling of ArcSDE inside ArcGIS Server etc I don't know. (If you think about the way ArcSDE executes queries then you will see that DC is slow - I would guess much slower than a native JDBC driver against PostGIS, Oracle Locator etc.)

4. Storage Models

Martin Davis said all that needs to be said. My only comment will be that, for PostgreSQL it is likely that ESRI's ArcSDE will offer support for PostGIS (in a similar way to Oracle), PostGIS's storage format + methods (but what methods?) and even, possibly, the old SDEBINARY approach.  What will be interesting is whether ESRI is offering a direct client to their own spatial type so that the client/server architecture is the same as using JDBC against PostGIS (ie no need for ArcSDE). Does anyone know if they are providing such access methods or will all ESRI clients still have to go via ArcSDE?

5. Versioning

I am very ambivalent about the need for versioning in an edit environment! But putting that to one side, I would hope that any ESRI support for PostgreSQL via ArcSDE or some native client would make versioning optional. When versioning was first released ArcGIS editing had to be versioned: there was no switch to turn it off. I was once asked to help a large Govt Dept here in Tasmania work out why the performance of ArcSDE with versions was so bad against Oracle with the SDO_Geometry storage format. Turning on sqltracing showed some of the most awful SQL I have seen. Performance did improve but certainly here in Oz ESRI AU pushed everyone to use SDEBINARY as the storage model because everyone knew "Oracle was slow"!!!

Just a few observations and ramblings.

regards
S
-- 
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, Radius Topology and Studio Specialist.
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.
Website: www.spatialdbadvisor.com
   Email: simon at spatialdbadvisor.com
   Voice: +613 9016 3910
Mobile: +61 418 396391
Skype: sggreener
Longitude: 147.20515 (147° 12' 18" E)
Latitude: -43.01530 (43° 00' 55" S)
NAC:W80CK 7SWP3