Of course the wording might signify that it might take until 9.3/10.0 for this to happen, but I’ll keep my hopes up. 9.2 should be out in October so maybe next spring around the Dev Summit we’ll be on PostgreSQL? Dominic Stubbins actually read the UC Q&A ESRI posted about the pre-conference survey (I admit it, I didn’t feel like reading it) and found something very interesting.
I discovered this little gem in the Q and A section that seems to have been overlooked in all the Server discussion. It seems that ESRI will be supporting an open source DBMS in the form of PostGreSQL, this support is currently in testing and due for release sometime after the initial 9.2 release. You can read about it at the Q&A section under the Geodatabase and ArcSDE section (Q11).
Yep, there it is. I usually ask this question every year, but this year I didn’t for some reason and maybe that is all it took. Back at the Dev Summit, I asked this question to the ArcSDE devs and they said there were no plans (I assume they lied through their teeth since other folks at ESRI had already confirmed to me what the Q&A states back in Jan/Feb. Now that the cat is out of the bag, we can now start bugging the ArcSDE folks about PostgreSQL support at the UC. I’m very happy about this as the cost of implementing SDE has gone down exponentially. Any bets who will be the first person to ask where is MySQL support? (Hobu?)
21 Comments
I won’t be the person asking for MySQL support. It makes a fine storage bucket, but it is not what I would call a spatial database at this time. I will gladly take PostgreSQL support though. I really hope that ESRI walks the two extra steps required to support PostGIS geometries, however.
Also, Oracle owns most of the good backends for MySQL now. Supporting MySQL would mean supporting Oracle
“Supporting MySQL would mean supporting Oracle”
Hmm, great point. Can’t have that!
Nice! I hope ESRI go all the way and use Well Known Text (WKT) or Well Known Binary (WKB) as the storage format, thereby providing compatibility with PostGIS. That would be very cool.
I hope to find out more next week Andrew. I’ll post back with anything I figure out.
The SDE C API already supports WKT/WKB as a common transport format. By supporting PostGIS geometries, I meant that the geometry would be stored in native PostGIS format so that PostGIS’s r-tree indexing could be taken advantage of. Another piece of this puzzle is supporting the OGC Simple Features for SQL profile so that other applications can come in and directly read the data without much fuss. These were the two steps that I was referring to above.
According to the “What’s New in ArcGIS 9.2″ pdf, the Data Interoperability extension will have support for PostGIS. http://webhelp.esri.com/arcgisdesktop/9.2/pdf/Whats_New_In_ArcGIS_92.pdf Hopefully they will have SDE work with PostGIS in the future. Also, SDE already supports the Oracle Spatial geometry object, but it’s far from perfect.
Hold it…
The announcement said Postgresql it did NOT say PostGIS.
So, if ESRI supports PostgreSQL they will probably do what they have just done to Oracle – ignore the existing ISO/OGC compliant implementation in Refractions PostGIS – and write their own for $$$$.
PostGIS implements “extended WKB” which is what ESRI does as well. “Extended WKB” is not a part of the standard.
Also, WKT/WKB does not support 3D and 4D data. This is why AutoDesk were forced to “extend” WKT in their Feature Data Objects (FDO) library to include these extra dimensions. But this is very “standards-like”:
POINT XY (10 11) // equivalent to POINT (10 11) POINT XYZ (10 11 12) POINT XYM (10 11 1.2) POINT XYZM (10 11 12 1.2)
CURVESTRING XYZ (0 0 0 (LINESTRINGSEGMENT (10 10 1, 20 20 1, 30 40 1)))
Ever wondered why the very vendors on the standards committees and boards of management allow for the production of standards that don’t really impact their business and sales?
regards Simon Greener
Simon, if you are referring to my comment then I was speaking about the pdf I mentioned, not the announcement in the post. The pdf (page 202) is referencing the Data Interop extension and is essentially saying that it will import/export the PostGIS geometries. “they will probably do what they have just done to Oracle – ignore the existing … implementation…and write their own for $$$$” Why do I keep hearing this? They do in fact support Oracle’s sdo_geometry type, meaning SDE not only reads it but writes it as well. They screw up the SRID and the sdo_geometry’s metadata, but that can be fixed. Regarding SDE supporting PostGIS geometry, once the support of PostgreSQL is there I’m not sure why they wouldn’t in the future support PostGIS like they do Oracle’s sdo_geometry.
I would add that PostGIS’s “extended WKB” totally busts good ‘ole regular WKB by stuffing the SRID in the same spot as the bit that tells you how may bytes to read for the geometry
It is an abomination that hopefully is going away soon (or at least put the SRID at the end and out of the way).
I’m not sure, but I think there is a rework of WKT/WKB in the works within OGC. Anyone know for sure?
Hobu is NOT right when he says Oracle has bought most of the backends for MySQL.
They have bought InnoBase a Finnish company who created InnoDB which is ONE of the pluggable storage engines underneath MySQL. But Oracle has not bought any of the others that I know of eg MyISAM, MaxDB etc.
Yes, they have bought BerkeleyDB and TimesTen’s In-Memory database but neither of these are MySQL storage engines.
So, having bought ONE of the MySQL pluggable engines does not justify a claim that Oracle “owns most of the good backends for MySQL”.
So, Hobu, I can’t see how you can say:
“Supporting MySQL would mean supporting Oracle”.
Let’s be a little fairer and more generous in our criticisms.
Anyway, what’s wrong with Oracle buying database companies?
It is “core business” afterall.
ESRI Inc, which obviously James loves, has bought up other GIS companies and products in the past (ArcSDE was one of them). Why can’t Oracle buy database companies?
So, James, when you say:
“Hmm, great point. Can’t have that!”
I have to say why do you say this?
Oracle Locator/Spatial is supported by ESRI Inc. Oracle Locator/Spatial is standards compliant (though it uses names for its spatial types that aren’t part of the standard cf Sdo_Geoemtry rather than ST_Geometry – I DON’T think they should have done this). But it is a single, point solution. Why does a SINGLE database company implementing an standards compliant ADT for one of the many possible data types that its DBMS could manage cause the dominant GIS vendor and its associated partners and professionals such cause for concern?
Sounds to me like someone feels threatened – and it ain’t Oracle.
Oracle Corp have bent over backwards to be nice to ESRI Inc when Oracle don’t have a reputation for being nice to vendors who “compete” against them.
In fact, I would say that ESRI Inc has, unfathomably, some sort of Oracle “most favoured gis company” status!
Oracle has not bought any GIS companies. They have not gone into direct competition with its GIS vendor partners in any way that can be counted as being serious (MapViewer is NOT a serious GIS technology).
But when I hear the sorts of things I hear against Oracle within the GIS industry (and on this page) I can’t help but hope that it does start buying GIS companies (as it buys application companies like JD Edwards etc) and starts to really compete with ESRI whose code base is based on the largest legacy COM library in the world.
Let’s not let facts stand in the way of good marketing spin shall we
ESRI Inc is not some sort of Oracle “victim” – far from it…
The last thing I want is to have to BUY my spatial extensions for PostgreSQL, MySQL etc from a single gis company not known for cheap software.
regards Simon
On Oracle’s recent acquisitions.
http://www.tdan.com/dbreport_issue33.htm
Yes, July last year, but I have not heard of any other database additions since:
As the report says:
“The unquestioned leader in open source DBMS is MySQL”…
Ahh, maybe ESRI should take the lead from MySQL and make its code open source….
S
Simon, I said it as a joke.
Matt,
….if you are referring to my comment then I was speaking about the pdf I mentioned, not the announcement in the post. The pdf (page 202) is referencing the Data Interop extension a
No Iwasn’t referring to the Data Interop extension… but the fact that the post started with a claim about ESRI announcing support for PostgresSQL and NOT PostGIS.
PostgreSQL is NOT PostGIS.
Not unless ESRI Inc has bought Paul Ramsey’s Refractions Inc.
It was only your initial post that referred to the Data Interop extension!!
regards S
Hobu,
We can argue about how companies have implemented their “extended WKB” stuff till the cows come home. ESRI has done it, Spatialware has done it, AutoDesk has done it… (though my postings were not about this).
You know more about WKB etc in PostGIS and MySQL than I do and I defer to your superior knowledge.
All my point was that WKT/WKB is not, of itself, any remedy and that ESRI Inc themselves have “extended” it (non-standard).
… everyone does it because the OGC committed didn’t make the standard useful…
S
James,
Use a smiley next time!
I was sitting in a restaurant having dinner with 2 ESRI Ireland staff on Tuesday in Dublin and they wanted to debate me on ESRI’s new spatial type in Oracle even bringing up the old chestnuts of “proprietary” and “slow”.
A pointless exercise.
S
Simon,
BDB is one of the possible transactional backends for MySQL . By “good,” I meant transactional backends. InnoDB and BDB are/were/will continue to be two of the most popular transactional backends. PostgreSQL is transactional out of the box, but MySQL with MyISAM out of the box isn’t. Up until recent MySQL releases, the spatial indexing was only supported on the MyISAM backend anyway. As Paul Ramsey once said to me, “MySQL: what’s a little data loss between friends?”
As pointed out by my smiley, I was joking about ESRI’s ArcSDE support for Oracle as well. ESRI and Oracle’s relationship has been … rocky, and ESRI’s support in ArcSDE for Oracle has been somewhat uneven — causing much frustration for us poor saps who are given both and told to make them work together.
ESRI fully supporting (if they do so) PostgreSQL/PostGIS probably antogonizes their relationship with Oracle some more. I was snarking about ESRI with respect to this instead of Oracle.
The OGC WKT/WKB standards are out of date, and hopefully there is movement in the works to bring it up to speed. I agree that everyone tweaking them is a result of their inadequacies more than malice of the developers.
And there’s nothing wrong with Oracle buying databases (or entire market segments, like I think it did with BDB). But if you’re a commercial outfit using dual-licensed code like BDB or InnoDB, expect the possibility of getting toasted when the company does finally get sold. BDB getting sold to Oracle was unexpected, especially by those using BDB because they couldn’t afford Oracle or didn’t need its copious bells and whistles. Not all open source software is free.
PostGIS does implement an “extended WKB” but you have to work hard to get it. If you use the OGC standard AsBinary() function (implemented as AsLarryRocksTheWorldBinary() in Oracle Spatial) you will get OGC standard WKB, exactly as you would expect. Ditto for AsText(). Nothing to see here, move along. (PS, look for a snazzy “OGC Compliant” button badge on the PostGIS web site, coming soon! (Unlike Oracle, we really deserve it!))
Hobu and Paul,
I for one would welcome ESRI FULLY supporting existant spatial blades or types within EXISTING vendors.
I just don’t see the need to write their own when decent, standards compliant, ones already exist. Until I see the evidence I doubt that ESRI will support Paul’s good work within PostGIS regardless as to how many shiny chrome badges he has for the product!
ESRI had 9 years to get it right with Oracle Spatial and to respond to the constant offers of Oracle to work with them not against them, but haven’t. (In management “speak” Oracle was after a win-win: that does not appear to be what has happened!) Now they release their own which gives a pretty good signal to the market what they might also do with PostgresSQL. ‘Cos we know it AIN’T about Standards support!
Currently, the state of play appears to be:
I don’t think the track record looks good for ESRI to support PostGIS. If they do, they are just as likely to write their own as well.
I guess we will just have to sit back and watch what happens! At least all of us who have been saying Spatial SQL is great and valuable for years in the face of opposition from other geospatial professionals can finally feel vindicated. But I am sure, like most things, a certain company will put out the spin that this spatial sql stuff was their idea all along.
But perhaps I am just annoyed because, now that ESRI is going to be THE spatial type for Oracle (and I won’t be able to afford the license fees), I will no longer be that “highly paid, specialist consultant [who has] to come in and tweak obscure SQL statements” that a respondant to one of my articles on Directions Magazine claimed was needed for spatial SQL! Damn
Penury ahead! Like the Hobbits, I had better “tighten my belt another notch”!
Then again, if ESRI’s is faster than Oracle’s, it might finally make Oracle do some work on their geometry engine: ‘cos it ain’t real quick.
BTW PostGIS’s AsText() and AsBinary() do provide OGC compliant responses. I know this is true because I helped a company build an interface to PostGIS in which the TatukGIS DK was the client and AsText()/AsBinary() provided the content. Tatuk’s DK happily swallowed the PostGIS outputs without complaint – well done Paul! I personally don’t care if the physical storage format is not standards compliant or even, shock horror, “proprietary”, because the interface is logical, standards compliant and client agnostic and, in database theory, that is all that matters! So, Paul, I agree, let’s “move on”.
regards S
I think ESRI will just support PosgreSQL as a SDE but will NOT support PostGIS. They will just port the SQL Server code and store a new geom type that is deliberatly not the PostGIS code. This way the only way you can use the database is with an ESRI product ($$$$ license fees). This is increadibly short-sighted on their part but that is their business model for the last 8 years or so. When they delivered ArcView 3 in 1994(?) they completely changed GIS from a niche group (ArcInfo and AML) to a dominant desktop market (Proof – where is the UNIX version of ArcView these days?) because they focused on the customer and built market share. Lately the revenue comes from licence fees and maintenance cost. Even ESRI admits the quality of the software is not what it used to be. If ESRI embraced PostGIS and said “Use SDE for your development and maintenance platform and use anything you want for other applications” (Mapserver or ArcIMS, PostGIS/PHP for SVG, WFS, etc.) they would bury Oracle Spatial. I don’t think they are that smart yet. Please prove me wrong. Bruce Rindahl
Hey, this is a very good discussion.
I totally agree with Hobu, Paul and Simon.
Spatial SQL is really cool but IMHO Esri will never make it available for several reasons:
1) if they would, then any GIS client could easily edit SDE simple features. At the moment the only way you can perform that is with $$$ ArcEditor and SDE Api (Java, C, I don’t know if they have plans for .NET in the next releases) 2) with Spatial SQL Esri should heavily modify the geodatabase schema, that is actually heavily implemented only by ArcObjects + stored procedures (ie: with PostGIS you could build with triggers and sp data intelligence or even topology by SQL, with ArcSDE you can do it only by creating topologies, custom feature or feature class extension by ArcObjects and the UML stuff) 3) Esri Geodatabase schema in SDE is fully maintained by ArcObjects clients like Editor, if you use an editor developed with SDE API you could only edit simple feature class
If Esri is going to support PostgreSQL this does not mean that they will use PostGIS OGC simple feature compliant model. They will introduce also in PostgreSQL, like they did for Oracle, their sde geometry field.
This is Just My 2 Cents
Another key ingredient here is whether Postgresql will be supported under “Personal SDE” and/or “Workgroup SDE”.
The plans to use Microsoft SQL2005 Express as the back end to personal SDE are disappointing.
We have 75 sites where we would like to do feature level data replication (one way) from our central SDE repository.
Because of constant issues with MS Server, we are moving to a linux NAS solution at each of these sites. Guess what, we are not going to be running Microsoft SQL2005 Express on these linux boxes.
Our spatial database is roughly 70gig. The 4 gig limit on Microsoft SQL2005 Express geodatabases (among other things) is a deal breaker.
Also, we can’t really afford to buy SDE licenses for all of these sites, and we don’t want to. We don’t need multi-user editing, we just want to replicate all of our central SDE repository data to these sites, where 5-25 users can get at the data locally.
If only the new file-based geodatabase could participate in one-way feature level data replication (central push), we would be golden… but no dice. SDE has to be running on both ends… ugh.
I think ESRI really missed the boat by relying only on SDE for the replication solution.