Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 22 down vote favorite
8

EDIT: I have been using Postgres with PostGIS for a few months now, and I am satisfied.

I need to analyze a few million geocoded records, each of which will have latitude and longitude. These records include data of at least three different types, and I will be trying to see if each set influences the other.

What database is best for the underlying data store for all this data? Here's my desires:

  • I'm familiar with the DBMS. I'm weakest with PostgreSQL, but I am willing to learn if everything else checks out.
  • It does well with GIS queries. Google searches suggest that PostgreSQL + PostGIS may be the strongest? At least a lot of products seem to use it. MySql's Spatial Extensions seem comparatively minimal?
  • Low cost. Despite the 10GB DB limit in SQL Server Express 2008 R2, I'm not sure I want to live with this and other limitations of the free version.
  • Not antagonistic with Microsoft .NET Framework. Thanks to Connector/Net 6.3.4, MySql works well C# and .NET Framework 4 programs. It fully supports .NET 4's Entity Framework. I cannot find any noncommercial PostgreSQL equivalent, although I'm not opposed to paying $180 for Devart's dotConnect for PostgreSQL Professional Edition.
  • Compatible with R. It appears all 3 of these can talk with R using ODBC, so may not be an issue.

I've already done some development using MySql, but I can change if necessary.

share|improve this question
1  
PostGIS would be the most mature of the options. – OMG Ponies Sep 18 '10 at 21:51
2  
PostGIS is by far the most mature GIS solution. And if you're using R, you can even use PL/R to write stored procedures in R. The MySQL spatial extensions are pretty slim and imho not worth trying, the SQL Server GIS possibilities are fairly new and seem somewhat limited but I have no experience with it yet. – WoLpH Sep 18 '10 at 21:56

4 Answers

up vote 21 down vote accepted

If you are interested in a thorough comparison, I recommend "Cross Compare SQL Server 2008 Spatial, PostgreSQL/PostGIS 1.3-1.4, MySQL 5-6" and/or "Compare SQL Server 2008 R2, Oracle 11G R2, PostgreSQL/PostGIS 1.5 Spatial Features" by Boston GIS.

Considering your points:

  • I'm familiar with the DBMS: setting up a PostGIS database on Windows is easy, using PgAdmin3 management is straight-forward too
  • It does well with GIS queries: PostGIS is definitely strongest of the three, only Oracle Spatial would be comparable but is disqualified if you consider it's costs
  • Low cost: +1 for PostGIS for sure
  • Not antagonistic with Microsoft .NET Framework: You should at least be able to connect via ODBC (see Postgres wiki)
  • Compatible with R: shouldn't be a problem with any of the three
share|improve this answer
1  
Heh - Oracle Spatial was a $1 million dollar license, last I heard – OMG Ponies Sep 18 '10 at 22:57
Thank you. The 2nd comparo link is helpful. I only found the first one earlier because I had MySql in my search terms. So looks like it's PostgreSQL for me! – Aren Cambre Sep 19 '10 at 0:30
10  
Just want to say, almost 1.5 years later, that Postgres+PostGIS was absolutely the correct answer. – Aren Cambre Mar 8 '12 at 3:08
up vote 8 down vote

PostGis definitely. Here's why.

  1. Postgres is far superior to MySQL in performance. Server is more fault tolerant, has out of the box tools for load-balancing, caching and optimization.
  2. PostGIS is becoming a standard in GIS apps.
  3. It's free.
share|improve this answer
Thank you. And of those points, #2 is especially strong for me. – Aren Cambre Sep 19 '10 at 0:30
#2 is definitely true for open source GIS software and open source stacks, but I'm not so sure if it is true for commercial GIS applications though. – winwaed Jan 25 '11 at 23:18
up vote 1 down vote

Just an note that MySQL has finally added in proper GIS logic.

http://dev.mysql.com/doc/refman/5.6/en/functions-for-testing-spatial-relations-between-geometric-objects.html

But I can't comment on cost or performance at this stage

share|improve this answer
it looks like rather than using a spatial library, such as GEOS, all of the spatial logic is in sql/item_geofunc.cc – Mike Toews May 23 '12 at 5:33
up vote 0 down vote

PostGIS is best because it is becoming a standard in GIS applications these days and PostGIS is free. It is far superior to MySQL in performance

share|improve this answer
Any benchmark somewhere? – j0k Sep 22 '12 at 21:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.