I would like to get opinions and experience about GIS databases. In this case ones that are used for large topography, cartography and related dataset creation and publishing.
So lets imagine that we have the following databases to choose from (you can freely add others, that have native GIS types and ESRI support):
Oracle (PL/SQL) - native GIS or ESRI option - wide applications, very expensive
SQL Server (T/SQL) - native GIS or ESRI option - easy administration, expensive
PostgreSQL (PL/pgSQL) - native GIS or ESRI option - hard configuration, cheap (support)
In those we can build GIS datasets without ESRI price tag and with it if choose to buy ESRI licenses. All three of them have database side language for making custom procedures, functions that help organize things.
A lot of people in my country have invested in Oracle database knowledge and certification. So it is easy (there is always lack of people with programming, database and administration skills for certain amount of salary) to find corresponding people. But that all comes with the price tag (Oracle is not AIO, for every additional feature that it alternatives is already included you have to pay). Lets work under assumption that there are equal number of professionals for every database. There are two other big names: PostgreSQL and SQL Server (and probably DB2). From the pricing perspective both are at least times cheaper.
As I understand than there are specific aspects to each of them. How do they compare (why should I choose one over the another):
- Could you name the most important aspects?
- How common are geometry errors? What other problems have you experienced (like ESRI ST_Intersects errors or other)?
- What is the best tooling for administrating and developing in these databases (price, ease of use, functionality - Oracle SQL Developer Studio, MS SQL Server Management Studio, pgAdmin, TOAD)?
- What about security - how easy is configuration for novices?
- How hard is it to properly configure and administrate specific database?
- What can I do with data if I use ArcGIS, and what if I use QGIS?
- What about ArcGIS Geodatabases vs native GIS data types - availability of tooling (ArcGIS cant edit PostGIS)?
- Would migration be worth it and if you have experience what was the hardest thing, given pricing (migrating code)?
I do look towards PostgreSQL because of pricing. What would I miss?
How future would look like in databases?
Ideally those that have experience with at least two of the mentioned databases (one preferably being PostgreSQL) would respond.