[postgis-users] Question about SDE on top of Postgres . . .

[BladeOfLight16]

On Sun, Oct 27, 2013 at 5:20 PM, Paul Ramsey <pramsey at cleverelephant.ca>wrote:

> Wow, that page really goes out of its way to make sure you don't want
> to use PostGIS! All those notes and caveats in the PostGIS boxes!
>

Yeah. ESRI doesn't want you to find out how good PostGIS is. It might cut
into their sales. ;) I wouldn't be surprised if they dropped support for it.

On Mon, Oct 28, 2013 at 10:51 AM, Basques, Bob (CI-StPaul) <
bob.basques at ci.stpaul.mn.us> wrote:

> Paul,
>
> Ours is all linux based (SUSE), so the windows part wasn't bothering me.
>  There did seem to be a lot of steps to follow in order to make it work
> though.  Our SDE folks are still settling on what to have in place as a DB.
>  Oracle is the front runner, but I was hoping to be able to demonstrate
> PostGIS as an option.  Just looking at options right now.
>

If you are looking to go under the hood and bypass SDE to actually use the
spatial components of your database, I HIGHLY recommend against Oracle.
First, to preface this, most of my Oracle work has been with Oracle 10g, so
some of this may be kind of out of date. I know some still applies to
Oracle 11g. Anyhow, my experience with Oracle and it's spatial component
has been... very painful. SDO_CS.TRANSFORM (the reproject function) blows
up with a Java exception if you pass in NULL, and no, the Java exception
doesn't actually tell you what's wrong. (This is typical of Oracle as a
whole. Their error messages very rarely direct you toward the cause of the
problem; they usually just tell you about some low level detail that means
nothing to you.) NULL is the only representation of an empty geometry
Oracle has. They encourage you to use this SDO_RELATE function that's
supposed to use the spatial index and then filter, but my experience shows
its performance is absolutely pathetic. Their real filter (SDO_FILTER,
which I think is a bounding box check like PostGIS, but I can't find
documentation of that) is much faster, but because SDO_CS.TRANSFORM blow up
with NULLs, you're force to either create custom wrappers around TRANSFORM
or use the poorly performing SDO_RELATE. I'm not sure about the performance
impact of the extra wrapper function. My experience was that when I used
Data Pump to back up and restore, our indexes (spatial and non-spatial)
were corrupted at the end of the restore. We also couldn't just rebuild the
spatial indexes; we had to drop and recreate them. You have to manually
manage some per-table "metadata" for spatial indexes to work, and the
metadata is stored in a database global table. I've seen some kind of index
metadata randomly get left behind (after a failure, maybe?), forcing me to
go into a global metadata table (not the per-table one I just mentioned, a
second one devoted to indexes) and manually delete bad rows to be able to
create an index. (It was also a total guess whether I would break anything;
luckily, that was a development database.) In most environments, I've seen
Oracle automatically figure out to remove rows from the global metadata
tables when I drop its related schema and put them back when restoring
using Data Pump (which is good), but the production environment doesn't do
that. My best guess is that it's some kind of screwy permissions issue
(based on some other evidence I've seen). I've seen Oracle gobble up 40 GB
of disk space for "undo" when importing about 5 to 10 GB of spatial data,
and it doesn't let that space go when its done. Oh, and I've been having a
problem lately reimporting that Data Pump file back into the database it
originally came from, and no clue why not. It just freezes (for over a
day). I've seen Oracle produce WKTs that it could not parse because they
contained numbers in the #E# notation. (If you're thinking, "The heck?
Oracle spit out a WKT it couldn't reparse?", yes, you're correct.) I don't
think they provide nearly as much in terms of spatial processing/testing
functions as PostGIS, either; certainly, it's not as readily apparent if
they do. Oh, and it's difficult to find what you actually need in the
documentation. And all that is just directly related to spatial; I could
come up with a bunch more if I went beyond spatial. And it's stuff that I
can think of off-hand. This level of trouble is a daily experience when I
use Oracle.

By contrast, I got PostGIS processing intersections between 2 or 3 spatial
layers and a subset of a table with millions of points in only a couple
weeks working on several queries. (This is from zero; I knew almost nothing
about PostgreSQL or PostGIS when I started.) I did run into some nastiness
where ST_Intersection was exploding on some very, very bad geometries.
(They were valid according to ST_IsValid, but very strange nonetheless.)
That and the fact that the row estimates from spatial indexes are my only
real complaints with PostGIS.

I suppose you could argue that "we weren't doing it right" with Oracle.
(That seems to be a common response to many problems in the Oracle
community.) But if it's that hard to get right, what does that say about
your design? Oracle is, in my opinion, a product of the 80s when usability
wasn't a concern, and it seems to me that it's designed for people who can
throw gobs and gobs of disk space at it. I spend more time just trying to
make it work at all than I do actually dealing with good design and
improving performance. (Well, I guess if you include "getting something to
perform in even close to acceptable time" as improving performance, then I
do spend a fair amount of time dealing with performance, but I include that
in "making it work at all".)

...Sorry. I am not an Oracle fan. They have caused me much strife. Using
PostgreSQL/PostGIS has been a pleasure cruise in comparison to my
experience with Oracle.

On Mon, Oct 28, 2013 at 10:58 AM, George Silva <georger.silva at gmail.com>wrote:

> I don't know the actual status of ArcSDE in 10.1, but in 9.3.1 they had
> custom dlls (or .so) to do some sort of black magic.
>
> I had a case where I needed to install ArcSDE with PostGIS 2.0, but ArcSDE
> had to be 9.3.1 (32bits), without the support for that specific version.
> Instead of using the 9.3.1 provided .so we used the 10.1 .so, which worked
> just fine.
>
> The problem, Paul, is not the client software, but the custom software
> that needs to be inside the database machine.
>

I know SDE installs ESRI's own spatial types. Is that what you're referring
to? What if you don't use their spatial types? Is that still required?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20131103/1cf522ce/attachment.html>