3

I'm looking for suggestions on a database for large sets of spatial master data. The data itself is not relational, but could be formulated as such. The data as-is is structed as:

latitude, longitude, time-series ("array" of ints)

Currently, we are using MySQL which is not performing well with our large table setup. Indexing the spatial point on our largest table (420 million rows, 500GB in size) took a week before giving up. We are currently also facing frequent server crashes (working on why still).

Because of these problems, I'm looking for suggestions on other databases. Oracle would be an option, but I wonder if we'd just trade complex problem for complex problem. Anyone know of good spatial data experiences with other databases?

Izzy
  • 8,214
  • 2
  • 30
  • 35
Catamount
  • 31
  • 1

3 Answers3

8

PostgreSQL together with the PostGIS extension should be a very good match. PostGIS is widely used e. g. by the OpenStreetMap project, so it should be able to handle your data.

joschi
  • 20,747
  • 3
  • 46
  • 50
  • +1 - I would do the same. MySQL is great in some places, but in such cases as yours, it pales in comparison to Postgres. – vmfarms Aug 16 '10 at 13:00
  • 1
    PostgreSQL is definitely king of the hill for GIS solutions unless you want to spend serious cash, and even then I am yet to be convinced that what Oracle and Microsoft offer is so much better than what you get for free with PostgreSQL and PostGIS combined. – Cromulent Aug 27 '10 at 13:06
1

OpenStreetMap used to use MySQL with a custom-built quadtile based spatial extension. The code for that must be knocking around somewhere still. This worked OK actually. It wasn't so much the spatial querying which prompted a move as far as I know. It was more general poor performance with database operations (query optimising, and changing schemas without locking the table)

The project shifted to use Postgres for its main database in 2009. Not PostGIS in fact. OpenStreetMap doesn't even use postgres geometry types in it's main geodatabase. The OpenStreetMap tile rendering server (and anyone else who sets up a Mapnik tile rendering server with OpenStreetMap data) does use a PostGIS database, but that's a separate system.

Note that PostGIS is just Postgres with a bunch functions added into the database.

Harry Wood
  • 301
  • 3
  • 9
0

Just to add that all the 'heavy' Oracle GIS is an extra cost option to their Enterprise Edition, so that can get expensive.

A subset of functionality, called Locator, is part of Standard Edition.

You don't give details on what processing you actually do with the data, so its hard to determine what you need. I assume, at a minimum, you'd do some sort of 'distance' or 'closest' calculations. If not, then you may not even need to treat the data as 'spatial'.

It's also hard to say which of the offerings would be more of a problem than a problem solver. A lot of that depends on your group's skill set (or the skill-set you can hire in). There's probably a bigger pool of Oracle experience than the others you mention (which didn't include SQL Server, but may also be worth considering).

Gary
  • 1,839
  • 10
  • 14