[postgis-users] PostGIS vs Oracle Spatial/MS SQL2008

Thu Nov 26 07:10:06 PST 2009

Good points Paul and Simon,

>> uDIG, QGIS allow direct connectivity to PostGIS, hope they allow 
>> direct file editing too.
>
> Yes, they do. As does gvSIG, and I think MapWindow.

For the record MapWindow does not work.  Tried it and have been trying to
get them to fix their PostGIS driver, but it is of low priority for them. 
It’s a shame too because I really wanted to use their activex control in MS
Access.  So if some  C# programmers are willing to give it a go it might not
be that far off.

>From last try a month ago, it can listthe tables, but then closes out with
an error when you select one.

Also I would suggest Simon's talk -- if you haven't seen it.  I think it
gives a fair view of the pros and cons of PostGIS vs. Oracle Locator vs.
Oracle Spatial.

http://www.spatialdbadvisor.com/blog/147/foss4g-2009-sydney-presentation/

As far as SQL Server goes -- yes only windows -- but of course your clients
can be anything if you are using for web and it ties in with their Reporting
Services which is a biggie for hard-core SQL Server and general Windows BI
users. I personally don't care for Reporting Services but a lot of people
(especially non-programmers do) because its got things like subscription,
quick report wizards to help you along, tied in security with active
directory etc.all out of the box.
  So if you are using windows servers anyway and don't plan to save money on
cloud Linux -- its probably not a major concern and it works with other
Microsoft networking stuff out of the box. 

Now topology and versioning stuff -- I think people think ESRI when they
hear versioning and I believe ESRI's yucky brand of versioning will work 
fine as long as you have an SDE license for the aforementioned Oracle,
PostgreSQL, or SQL Server -- all starting at about Ithnk $5000 per core.  So
as Paul says it depends the kinds of clients you are talking about and what
exactly you mean by versioning.  I prefer my versioning with triggers and
rules because you can control it to the granularity you need.

So in short PostGIS gives you a lot of flexibility of decision (all the cool
stuff you can get for it is not packaged with it aside from the raw power of
its SQL and function offering, but is just a download away or reading away),
but choice is overrated for some and sometimes you don't want to have to
make choices - Microsoft makes a lot of choices for you -- which is kind of
nice.  Just like Simon says Oracle is very configurable -- but if databases
scare you -- lots of knobs is the last thing you want to look at. PostgreSQL
has knobs in other places. MySQL lack of schema and knobs is a feature :)
because you don't have to deal with someone saying "Oh my god what are these
things should I be using them - they look scary."  Yes someone has said that
to me "I don't want to know about schemas, functions, or casts.  I just want
a database."

Thanks,
Regina

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Simon
Greener
Sent: Thursday, November 26, 2009 12:48 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] PostGIS vs Oracle Spatial/MS SQL2008

Paul,

(This is NOT a flame!)

> Oracle's wonderfully featureful, but if you're not already an Oracle 
> shop, you'd be foolish to bring it in. It's difficult to learn and 
> administer, which is why "Oracle DBA" is such a great job title to 
> hold (job security!)

Still peddling this old canard, Paul, that Oracle is "difficult to learn and
administer"?!
It has never been difficult to learn (I've been teaching basic SQL since
1988 to non-technical people):
no more than any other database. It might have been difficult to administer
in the past but it is far easier nowadays. Anyway, its instrumentation and
configurability is one of the reasons it is a great and very fast
database....

> SQL Server is more lightweight, but it naturally ties you into the 
> Windows platform, if you want to deploy on some other operating 
> system... tough. Many administrators love it, some hate it. The 
> spatial support is also pretty new, which means support for it is 
> still building. However, it is Microsoft, so it'll only get stronger 
> over time.

A reasonable summary.

> PostGIS is easy to install and easy to use, it has a clear simple 
> syntax, it's been around a long time, is stable and very fast.

Now you go and compare apples with oranges. Above you have a go at Oracle
being "difficult"
(like ESRItes say Spatial is slow - but forget to tell you the slowest
client against Oracle Spatial is ArcSDE. Compare ArcSDE+ArcGIS render time
against GeoServer or MapServer and you'll see what I mean), but you don't
talk about Oracle Spatial you talk about Oracle.

You reference Spatial when talking about SQL Server (a fraction of the
functionality of the database) but your criticisms are mainly about it (the
host database) being on one platform etc etc.

Yes PostGIS is "easy to install" but Oracle Spatial is even easier: you
don't have to as it comes with the database! There are no issues about
putting the Oracle Spatial code in another schema (cf the emails on this
forum) - it just "works". Doesn't get easier than that!

With regards spatial the functionality of the basic type in PostGIS is far
superior to anything else on the market.
Critical analytical functionality like ST_Union cannot be matched and are
excellent. Oracle has other SPatial APIs other than just the basic type but
the number of people who use them is low because of licensing issues.
Yes, PostGIS has related projects that compare to these but most of these
projects are not yet in full production (ie verion 1.0 or above).

But if we talk about PostgreSQL the database, yes it is easy to install, but
I would argue that it more difficult to learn than Oracle because of its
quirky post-Relational extensions which (for simple people like me) is
exposed in weird SQL quirks that I find frankly, crazy. Something trivial in
Oracle can be bloody hard in PostgreSQL. Anyway, Oracle has better, more
mature functionality (ie existed before PostgreSQL 8.4!) in many areas that
I take for granted in crafting solutions eg hierarchical queries,
materialized views, full analytic SQL etc.

I am still learning how to get the best out of PostgreSQL. I like it a lot
but that learning process will last a long time.
And that learning (same with SQL Server) is not about the spatial stuff - it
is trivial - it is about learning and mastering the whole database (eg
explain plan and performance analysis).  If you are not interested in doing
this then just grab any database your GIS can connect to and treat it like a
bit-bucket: PostgreSQL is probably  best for this.

Just my 2c worth.

regards
Simon

> On Wed, Nov 25, 2009 at 4:15 PM, Bruce Foster <gis.foster at gmail.com>
wrote:
>> a. Read somewhere on Topology. Hope someone throw more light on this.
>
> Some basic topology support, but nothing to write home about, and more 
> importantly there are no client tools that support it. Topology is a 
> sexy slow dance between the underlying data model and the user-facing 
> application that exposes the model, and the difficult part is on the 
> user-facing side. This is why ESRI's topology stuff remains 
> more-or-less the only stuff in use -- because they nailed the 
> user-facing side.
>
>> b. Versioning, which is not available in Postgres
>
> You can build versioned tables easily enough with some simple rules 
> and triggers. Again, the question is what user-facing application you 
> are planning to use and what your use case is going to be.
>
>> On a related note, can we edit directly on PostGIS using MapInfo,
>
> Yes
>
>> ArcGIS Desktop,
>
> Yes, with zigGIS. Yes also with ArcGIS Server underneath, but ... ouch.
>
>> AutoCad Map3D etc.
>
> Yes'ish, the FDO support for PostGIS is still limited and apparently 
> this is finicky at best.
>
>> uDIG, QGIS allow direct connectivity to PostGIS, hope they allow 
>> direct file editing too.
>
> Yes, they do. As does gvSIG, and I think MapWindow.
>
> Also web-based tricks, like WFS editing through openlayers and 
> geoserver. Or through openlayers and featureserver. Or through geoext 
> and mapfish.
>
> Best,
>
> Paul
>
>
>
>>
>> --
>> Thanks
>>
>> Bruce
>> NSW Australia
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

--
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: simon at 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 at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users