[postgis-users] Re: I have an ArcGIS-PostGIS connector.

[Ken Lord]

There was a discussion on the MapServer's user list in early July
regarding an ArcGIS-PostGIS connector.

Some fundamental issues were brought up by others who have made the
attempt, where ArcGIS appeared to have blocks in place to prevent
getting full use out of third party spatial database engines ... to
protect ArcSDE from the competition.

Hopefully the ideas you all are bringing up are along a different line
that will have more success, we at BGC Engineering in Vancouver are
eager to see an ArcGIS - PostGIS connector and may be willing to
contribute to funding a portion of the project at some point.

I am copying the contents of the MapServer posting on the subject
below in hopes that it may be of some help to you.

Cheers,
Ken Lord
Vancouver BC

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
MapServer Posting - ArcGIS - PostGIS Connector
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Mike Davis  to MAPSERVER-USERS

  Jul 7
Since we seem to be building some momentum on the ESRI personal
geodatabase thing, it seems like a good time to bring up the other
database related item on our wishlist.

What would be required to allow read/write to a PostGIS database from
ArcGIS?  I would love the ability to maintain our spatial data in
multiple datbase formats (must me my irrational fear of vendor
lock-in), but unless we can manage the data from within ArcGIS it
isn't really a practical option for us.

~~~~~~~~~~~~~~~~~~~~~

Paul Ramsey  to MAPSERVER-USERS 
   Jul 7 

Guys, I am happy to spec this out again, but frankly I fear that most
solutions will remain in the "moderately ugly hack" domain, ala
PgArc. I have investigated more elegant things (oledb) but the signs
are not promising (http://arcgisdeveloperonline.esri.com/
ArcGISDeveloper/ExtendingArcObjects/Ch07/AboutOLEDBProviders.htm).

Principally, the continuing lack of an Oracle Spatial third party
read/write extension on the market says to me that such a thing is
just technically not possible to do attractively. If it were, someone
would have written it by now and be making money on it. If anyone
knows of such a thing, please do tell, it would be a good positive
sign of possible success.

~~~~~~~~~~~~~~~~~~~~~~

Alex Smith  to MAPSERVER-USERS 
  Jul 7 

I'd like to preface this by acknowledging that my technical ineptitude
may have been the cause of my unsuccessful attempts to write tabular
data to external OLEDB databases from ArcGIS.  And I'd also like to
point out that it's been a while since I tried doing so - things may
have changed.  But my experiences have lead me to believe that ESRI
has (or had) carefully implemented ArcGIS in a manner that prevents
users from writing to external DBMS without  using ArcSDE.  We do alot
of spatial processing that output and affects a considerable amount of
tabular data that many other rely on and edit where necessary. 
Because we're already pushing the limits of Access, I looked into
other options when we started some new projects....and an affordable
option, such as Postgres or MySQL.  Here's what I found
 
In the original "Exploring ArcObjects" book, published near the
release of ArcGIS 8.0, states that OLEDB connections to ArcGIS are
read only.  Hence, we couldn't directly output data to an external
table joined to a Access based featureclass.  Sure, we can later use
ADO to update the 'enterprise' database from the Access geodatabase,
but that starts to get messy or becomes, to use Paul's words, a
"moderately ugly hack" (why have the middle-man Geodatabase tables -
like pgArc had the middle-man shapefiles).  So I tried linking in a
MySQL table to via ODBC in a geodatabase .mdb file and attempted to
edit that table (no spatial data) from ArcGIS - I got a message
stating that linked tables cannot be written to.  DAO tables (and I'm
fairly certain that ArcGIS interacts with Access databases using DAO),
have a property that returns true if they are linked tables.   So it
seems that the ArcGIS has code that tests this property and does not
allow users to edit these tables.  I find it very hard to believe that
this was accidental.  And I later felt naive for thinking I could
somehow 'outsmart' them - ESRI's software engineers are not dumb. 
Expect some resistance in attempting to circumnavigate their systems.
 
I understand their need to protect their investment - the ArcGIS
spatial editting tools (including topology and networks) and ArcSDE
must have cost a lot of money to develop.  And it's good software -
ESRI has earned a lot of its success.  It just sucks for those of us
on a limited budget - SDE licenses on top of commercial RDBMS fees is
simply unaffordable in our case.
 
On the other hand, ESRI seems to be opening up.  But they must
continue to recover their development costs somehow.  And I'd be
surprised if they'd make it easy for other develop tools to compete
with their systems.  Yes, being able to directly edit PostGIS data
using ArcGIS-like editing tools would be huge....but I don't think
that it would be easy and would probably be cost-prohibitive.  As Paul
mentioned, if these things were easily and cost-effectively
attainable, then it probably would have been done by now.  But then
again....look how successful mapserver has become.  Just my
thoughts......
 
~~~~~~~~~~~~~~~~~~~~~~

uli mueller  to MAPSERVER-USERS 
   Jul 8 

Paul, why exactly are the signs not promising? Is
it "only" because OLEDB provides read-only access for ArcGIS? I did not
really delve into the article you cited, but what I read sounded like
you can use PostGIS WKBs 1:1 with OLEDB for ArcGIS?? Has anybody
successfully implemented read-access from ArcGIS 9 to PG? Would this be
only an ugly or also a never-ending hack?

Background: We also have a project where we are constrained to use a
personal geodatabase and would be happy to switch to PostGIS. Read-only
would be sufficient in this case.

~~~~~~~~~~~~~~~~~~~~~~~~~~~

Paul Ramsey  to MAPSERVER-USERS 
  Jul 8 

Read-only OleDB would probably work fine. It would still be some
effort to implement. Most people really consider read/write the gold
standard they need for ArcMap, which is why I was focussing on it.

~~~~~~~~~~~~~~~~~~~~~~~~~

Paul Ramsey  to MAPSERVER-USERS 
   Jul 8 

The plot thickens:

http://support.esri.com/
index.cfm?fa=knowledgebase.techarticles.articleShow&d=16643

~~~~~~~~~~~~~~~~~~~~~~~~~~

Ken Lord  to MAPSERVER-USERS 
   Jul 27 

I found more information that seems to confirm ESRI's intent to block
direct writing (for example saving edits) of data from ArcGIS to
PostGIS and many other formats.
 
FME Suite by Safe Software can read and write directly to PostGIS and
many other formats. It uses libpq to interact with PostGIS.

However, ESRI's Data Interoperability Extention ... which is Safe's
FME Suite beaten into submission (into ArcToolBox) ... gives you
direct read and import capabilities for PostGIS, but no ability to
save edits back to PostGIS or the other formats. Instead you must
place your data into a geodatabase which you can then export back to
PostGIS using their Export Tools.  I assume you must then go back and
delete your original PostGIS tables manually.
 
In otherwords it offers almost no advantage over pgsql2shp /
shp2pgsql, it is just a streamlined way to get your PostGIS tables
into ArcGIS.
 
Here's the link to ESRI's specs and FAQ on the extention:
http://www.esri.com/software/arcgis/extensions/datainteroperability/about/faqs.html
 
Has anyone tried using the Data Interoperability Extention with PostGIS?

~~~~~~~~~~~~~~~~~~~~~~

Frank Warmerdam  MAPSERVER-USERS 
   Jul 27 

Ken,

Generally speaking FME's format drivers are oriented to bulk reading, or
bulk writing rather than update-in-place, which may explain why the
Data Interoperability Extension using FMEObjects is setup this way.

I don't know if it is ESRI policy to only do editing on ESRI databases or
not, but there are certainly technical reasons why editing on external
databases is hard for a big system with alot of expectations of data
providers.

~~~~~~~~~~~~~~~~~~~~~~~

End.