Duarte, Your experience is pretty well identical to nearly 20 years of experience with Oracle and ArcSDE.
Many times, since SDE 2.0 (first port from Interbase to Oracle after ESRI purchase), I have had cause to turn on SQL Tracing and then blanch at the SQL the ESRI software generates. The ESRI claims of the poor performance of Oracle Sdo_Geometry with ArcSDE being due to Oracle are impossible to expose because of the ban Oracle has on comparing their database with any other database or associated product and publishing the results. Many practitioners like yourself and myself have dropped the SQL and discovered the probable cause of the performance issues. But it doesn't matter when the world's dominant GIS company says the problems are otherwise. The recent publication of a PostGIS vs Oracle performance benchmark on LinkedIn is a case in point. (The methodology was so bad that the comparison was worth nothing. Oracle, to their credit, then contacted the University in question and offered some advice.) I predicted that performance issues would occur when ESRI moved to support PostgreSQL/PostGIS. Looks like it has come true. As I have indicated in other emails. If you want clean data-level integration don't use the whole GeoDatabase framework - don't use ESRI's versioning. If you have to use it, find open products that use the right FDO driver to access the versions etc. But these drivers normally use the ArcSDE API so you will be forced back to where you started. Good luck. regards Smon On Tue, 03 Jul 2012 10:07:05 +1000, Duarte Carreira (gmail) <dncarre...@gmail.com> wrote:
Hi Olivier. We are an esri shop and have been slowly adopting postgis as our rdbms. We use arcsde direct connections (no arcsde middleware service, just sde's schema with tables, functions, triggrers, etc.). The other way, using native connections from arcmap is just too slow (from the File menu). Also, we obviously want to use postgis native geometry type. But what we found out was that arcmap is 400% slower in some of our use cases. We narrowed it down to the sql emitted by arcmap. ArcMap was taking 38s to draw a spatial table (full zoom), while qgis took 15s. When using esri's st_geometry type arcmap would drop to 8s. Please note this is a single use case, others were also consistently slower but not nearly as much. We opened a support ticket and the first answer was "it's just the way postgis works". Which led me to send the sql that I logged when arcmap draw the table and when qgis did it. The difference was in the sql sentence used by each. One query took 38s another took 15s, for the same data. Eventually I was told an enhancement request was opened, but it is not public and not searchable. At this point, we have some scripts that copy data from esri's st_geometry to postgis geometry type, keeping in fact 2 copies of the data: one for esri clients another for open source tools. This is not mandatory, just a choice given our scenario. I think others could have chosen just the postgis geometry type and live with arcmap performing badly with some tables. Another final note: it was not easy for us to copy from esri's st_geometry to postgis geometry using only sql. Since we can only use postgis 1.4 with esri, the wkt produced by esri's st_astext is not compatible with postgis geometryfromtext at 1.4 when there are z or m coords. Neither are binary forms compatible. We ended up using esri's tools and arcsde configuration keywords to make the conversion happen (which is a bit ironic I think). It seems postgis 2.0 would accept esri's st_astext output but i haven't tried it yet. So there you go. Just my 2 cents... all that being said, I enjoy working with arcmap+postgis. The thing that is more confusing to me is handling the server memory usage from arcmap sessions - they can go from 100mb to 1gb. Regards, Duarte -- View this message in context: http://postgis.17.n6.nabble.com/ArcGIS-and-PostGIS-feedback-tp4998679p4998715.html Sent from the PostGIS - User mailing list archive at Nabble.com. _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
-- 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: si...@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 _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
