Slashdot Mirror

I have had a fair amount of success using open source GIS tools for one of my side project to create a hunting map book. I am not too sure of how well either one would work for what you are trying to do but I would be surprised if they didn't. The 2 tools I have had the most success with are uDig GIS and GRASS GIS. Both of them will run on Window, Mac OS, or Linux just fine, they all can process shape files, and DB input as well as geo tiff files as well as other file formats. Another popular OS GIS program is Quantum GIS. When I started I found OSGeo4w which had a bunch of open source GIS programs compiled for windows with everything you need and tried out a few of them and found 2 that best suited my needs. At the time I know it included GRASS, uDig, QGIS, and a couple of other ones, you might want to check out OSGeo as well as there may have been other projects that have started or better meet your needs.

Here is an example of some of what I have done. It is a map of the MN deer areas showing the antlerless deer harvest per square mile for each area in relation to all the others during the archery season. This map is a 10 year snapshot where the darker area indicated more deer were taken per square mile.

http://postgis.refractions.net/ - pretty good spatial functions based on top of PostgreSQL, and not tied to Oracle.

There are many, many open source GIS packages that you might find useful rather than implementing everything yourself. http://maptools.org/ is one place to get some pointers from.

Run, do not walk, to research the Open Source Geospatial Foundation's offerings. And be aware, neo-geos devote their efforts primarily to PostGIS, which builds on the very capable PostreSQL database, adding in geospatial capabilities that by many accounts rival the best that that Oracle Spatial and MS SQL provide.

As always, there are significant tradeoffs to evaluate in your situation. Be ready to study the many evaluations and comparisons of the various solutions.

I would recommend checking out PostGIS, which works with PostgreSQL. PostGIS adds functions, data types, and projection definitions to PostgreSQL that turn PostgreSQL into a powerful spatially-enabled database server. I'ved used this combination for a number of projects, and they work great. Both are fully open sourced.

So...how does PostgreSQL compete with Oracle nowadays as far as features go (specifically, spatial, and data-guard-like replication)?

I can't speak for Oracle, but if you're interested in spatial stuff you should have a look at PostGIS. We've recently been using it to store tons (magnitude of several million) of points and polygons, and we're very happy with it. We've got about hundred simultaneous users connecting to the WFS in peak hours, and it bears the load pretty well if you properly index your tables. I can't speak much for updates, since our database updates in bursts (we import new data every X weeks). I can't go too much into detail about the type of data other than that it's polygons, points, and mostly distance calculations and intersections.

We briefly looked at Oracle Spatial for a while, looked at the pricetag and the project budget and made the decision to try the PostgreSQL+PostGIS combination and see how far it'd get us. We were pleasantly surprised. I had some experience with PostgreSQL before in the 7.X releases in a previous lifetime but in the end wasn't all that pleased with it, especially on busy servers. Nowadays, I'm running 9.0 and I'm pretty much content about it. Replication wise we've got a PITR setup up and running which is more than enough for our purposes. It's pretty well documented, but be sure to test everything, etc etc etc... It doesn't quite hold your hand when you're setting it up, so double check everything.

I'm sure that there will be people on here that have more extensive experience with PostgreSQL (and Oracle) to fill you in on the juicy details, but in general I'm pretty pleased with it so far. It scratches my particular itch, and does so without all too much headaches.

As is UMN's MapServer, with a fully open source (GPL-like) license. http://mapserver.org/

For a nice and robust GPL'd mapping client that can pull data from MapServer, as well as many other sources, see QGis. http://www.qgis.org/

For a full-featured GIS agent with GPL'd license that can also pull date from MapServer see GRASS. http://grass.osgeo.org/

Another cool open source geographical application is PostGIS, a spatial data server for PostgreSQL that interfaces with all of the above. http://postgis.refractions.net/

Oracle streams - a form of SQL-level replication.

Sounds like a subset of functionality is coming in PostgreSQL 9.0, which supports streaming replication in addition to transactional and log shipping.

High-performance compression that in many cases is faster than non-compression. You can encrypt it, too.

Here you can see Greenplum's commercial PostgreSQL offering which is 10x-20x faster than stock PostgreSQL. A large portion of its performance boost comes from its support of high performance and effective compression as well as parallelism. I strongly suspect its faster than Oracle in many use cases.

It's great you have B-tree indexes - Oracle also offers bitmap and there are cases where they are really useful. It's nice that you offer hash partitioning (if you do), but Oracle can partition on a half-dozen different things. Etc.

PostgreSQL has had bitmap indexes for a while now. Not to mention you can actually create your own index types too. PostgreSQL is very extensible. That's one of the reasons why PostGIS is so capable. And please note they just announced a major new release.

Let's also not forget PostgreSQL, like Oracle, supports function indexes, which are in of themselves extremely powerful.

Online redefinition (change your tables, views, etc. and have Oracle store everything up until you snap everything over at once - great for reducing downtimes).

PostgreSQL can do this too for most everything. There are some exceptions but by in large, PostgreSQL has this covered.

PostgreSQL is one of the few databases which supports transactional DDL and has done so for a very long time. So for example, you can create types populate and even create indexes within a single transactional boundary. Which means you can actually do all this within the confines of a TPC transaction, which can wait a long time (logging implications and caveats here). Then when ready you can commit the TPC transaction and *BLAM*, you new table, fully populated, with deferred index creation, is now online. That's just one example of what can be done with PostgreSQL.

Query analysis is enormously better than open software (explain plans, etc.)

PostgreSQL has very good query analysis features. Its query plans are also excellent and typically does so without the many hints Oracle often requires. Having said that, IMO, PostgreSQL query plans are only exceeded by that of Oracle's and even then PostgeSQL genetic planner offers capabilities to niche projects unavailable in even Oracle.

Virtual Private Databases

Hotly debated on PostgreSQL mailing lists. PostgreSQL offers this capability today via its schema and security models. They just don't call it VPDs.

PL/SQL, Java, etc. native to the DB

PostgreSQL blows Oracle and every other database out of the water when it comes to native PL language support. What's you're flavor? PL/pgSQL? Perl? Python? Tcl? Java? C? Lua? And I think I many be forgetting a couple.

No bones about it, Oracle is more feature rich. It is true Oracle still addresses many high end solutions where stock PostgreSQL does not yet compete. Just the same, many commercial PostgreSQL offerings are starting to compete in arenas which were previously Oracle only domains. Furthermore, stock PostgreSQL continues to egress further and further into extremely large databases and warehousing solutions. Additionally, once you step outside of high end databases, for the vast majority of people, PostgreSQL is a very competitive solution to Oracle and in many cases, unofficially faster.

It sounds like you need to take a hard second look at PostgreSQL because based on my of your comments, it sounds like you're somewhat out of touch with the current capabilities and features provided by PostgreSQL.

Oracle streams - a form of SQL-level replication.

Sounds like a subset of functionality is coming in PostgreSQL 9.0, which supports streaming replication in addition to transactional and log shipping.

High-performance compression that in many cases is faster than non-compression. You can encrypt it, too.

Here you can see Greenplum's commercial PostgreSQL offering which is 10x-20x faster than stock PostgreSQL. A large portion of its performance boost comes from its support of high performance and effective compression as well as parallelism. I strongly suspect its faster than Oracle in many use cases.

It's great you have B-tree indexes - Oracle also offers bitmap and there are cases where they are really useful. It's nice that you offer hash partitioning (if you do), but Oracle can partition on a half-dozen different things. Etc.

PostgreSQL has had bitmap indexes for a while now. Not to mention you can actually create your own index types too. PostgreSQL is very extensible. That's one of the reasons why PostGIS is so capable. And please note they just announced a major new release.

Let's also not forget PostgreSQL, like Oracle, supports function indexes, which are in of themselves extremely powerful.

Online redefinition (change your tables, views, etc. and have Oracle store everything up until you snap everything over at once - great for reducing downtimes).

PostgreSQL can do this too for most everything. There are some exceptions but by in large, PostgreSQL has this covered.

PostgreSQL is one of the few databases which supports transactional DDL and has done so for a very long time. So for example, you can create types populate and even create indexes within a single transactional boundary. Which means you can actually do all this within the confines of a TPC transaction, which can wait a long time (logging implications and caveats here). Then when ready you can commit the TPC transaction and *BLAM*, you new table, fully populated, with deferred index creation, is now online. That's just one example of what can be done with PostgreSQL.

Query analysis is enormously better than open software (explain plans, etc.)

PostgreSQL has very good query analysis features. Its query plans are also excellent and typically does so without the many hints Oracle often requires. Having said that, IMO, PostgreSQL query plans are only exceeded by that of Oracle's and even then PostgeSQL genetic planner offers capabilities to niche projects unavailable in even Oracle.

Virtual Private Databases

Hotly debated on PostgreSQL mailing lists. PostgreSQL offers this capability today via its schema and security models. They just don't call it VPDs.

PL/SQL, Java, etc. native to the DB

PostgreSQL blows Oracle and every other database out of the water when it comes to native PL language support. What's you're flavor? PL/pgSQL? Perl? Python? Tcl? Java? C? Lua? And I think I many be forgetting a couple.

No bones about it, Oracle is more feature rich. It is true Oracle still addresses many high end solutions where stock PostgreSQL does not yet compete. Just the same, many commercial PostgreSQL offerings are starting to compete in arenas which were previously Oracle only domains. Furthermore, stock PostgreSQL continues to egress further and further into extremely large databases and warehousing solutions. Additionally, once you step outside of high end databases, for the vast majority of people, PostgreSQL is a very competitive solution to Oracle and in many cases, unofficially faster.

It sounds like you need to take a hard second look at PostgreSQL because based on my of your comments, it sounds like you're somewhat out of touch with the current capabilities and features provided by PostgreSQL.

You either don't understand GIS or you don't understand relational DBs.

http://postgis.refractions.net/

Last week PGCon had a session introducing this project and how to use OpenStreetMap with PostgreSQL. One Postgres add-on that's very popular in the mapping space is PostGIS, which lets you do all sorts of spatial bits it used to take expensive propriety tools to handle.

There need not be a binary distinction between "winners" and "losers".

PostgreSQL, MySQL, SQLite and others are just alternative choices, and they compete for different database niches. If I'm a project manager ledaing five developers who are supposed to build a standard Web app, I would be using MySQL since all five are likely to know how to use it already. If my job is to build a spatial store for a research project, my choice may be PostgreSQL (because of PostGIS). If I need to manage 70k of personal addresses, SQLite's simple API, in-process ability and small memory footprint may make it the component of choice.

What I don't like about /. is that too many people here are religious fundamentalists rather that rational, professional, informed decision-makers.
I urge you to find out your requirements and match them against available products, pick what suits you, and if the cross-product is empty, report to your manager that you need resources to extend the nearest match with additionally needed capability or even build your own from scratch.

That's like you build everything else, so that's how you should build software, too.

One thing I know that's missing from Postgres is the relative lack of advertisement or PR. I realize that it's an open source project, but it's something to consider. In my field (landscape ecology), ESRI gets most of everyones business, including spatial databases. It's unfortunate that so many people shell out a truckload of money for ArcSDE when they could be using PostGIS, a free extension of Postgres to allow for the storage, querying, and manipulation of spatial data. Plus, it easily imports the industry standard shapefile.

PostGIS is gaining momentum in my field, but it, along with Postgres, needs more advertisement. When I started learning Postgres, I was a little leery. I had thought it was going to be incredibly complex and arcane, but I was pleasantly surprised. PostgreSQL just needs to get the word out.

PostgreSQL has much better support for spatial features than MySQL, via PostGIS, which allows the PostgreSQL server to be used as a backend spatial database for geographic information systems (GIS). The question of which one to use is pretty much a no-brainer if you're working with GIS and spatial databases.

I don't have any thoughts about the more general question, but PostgreSQL is much better at storing spatial data than MySQL. MySQL has spatial functions built in, but it only supports a subset of the OpenGIS functions (basically anything that can be done entirely with bounding boxes). PostgreSQL uses an external modulem PostGIS, which supports the full OpenGIS specification and a bunch of other extension functions besides. I've used MySQL by default simply because it is more familiar to me, but I've switched to PostgreSQL for my current project simply because of the spatial data module.

GRASS GIS (Geographic Resources Analysis Support System) is an open source, Free Software Geographical Information System (GIS) with raster, topological vector, image processing, and graphics production functionality that operates on various platforms through a graphical user interface and shell in X-Windows. It is released under GNU General Public License ( GPL ).

Mapserver is a CGI-based program for making maps. It has an interface (mapscrpt) in Perl, Python, PHP. PostGIS spatiall-enables Postgres. Great software.

Of Refractions Research for PostGIS and bringing geospatial processing out of the domain of the $X0000 'oracle spatial' and 'ESRI SDE' crowd.

Visual DBA Suite

Spatial Object Library

B1 Security

OpenROAD

Enterprise Access

EDBC

Will these be freely available (but not open), also what process did you go through when deciding what to contribute and what not to? I can imagine that these are areas where you feel you offer more than compeeting Open Source solutions and so you are holding back for now.

This is a shame, especialy with regards the Spatial library as PostgreSQL (via PostGIS) and MySQL (4.1 onwards) already have support for spatial objects and indexing.

If you really like geometric data types checkout PostGIS - a full, power implementation of spatial data types. Load some points, buffer them, intersect them with lines. Then serve up a web map with MapServer. The power is yours!

It supports much larger polygons than PostgreSQL's native geometry types, and far more operations on those types.