Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are the in the process of building out a web application that has a spatial data component. In the beginning our spatial data comparisons will take a given point and return matched overlapping spatial polygons.

That being said, our database has many other components that include all the typical things you would find in your general relational database.

We are at the point in our project where we must choose which database solution to use.

All project members are more familiar with the implementation and administration of MySQL, yet all research suggests that PostgreSQL is the better solution - especially in regards to spatial data using postGIS.

We expect (hope) our application will experience a lot of action with a lot of concurrent users.

Does anyone with experience using MySQL as their RDBMS with a spatial data component have any long term advice / experiences?

Are there any disadvantages of using PostGIS with the exception of familiarity?

share|improve this question
    
In case you have not seen it, there is a similar question on slashdot that will probably get more attention. –  j.p. Jan 17 '12 at 10:29
add comment

2 Answers 2

I can't speak to advantages/disadvantages vis-a-vis MySQL, but the PostGIS code is pretty widely regarded as one of the best (in terms of speed/functionality) and most mature (in terms of testing/real-world exposure) available.

By way of example, there was a talk at PGEast 2010 by some folks from the FAA on their converting their airport database (used by AeroNav and others to compile charts) to Postgres/PostGIS from Oracle.
The avationDB site is also built on top of Postgres (8.0).

If GIS-related queries are at the heart of what you're doing my suggestion would be to go with Postgres. It can certainly handle everything else you would normally do in a relational database as well.


In terms of making the switch from MySQL, the documentation behind Postgres is first-rate, and there's also a section of the Oostgres Wiki about switching from MySQL to Postgres.
The initial learning curve may be a bit steep and you may need to tweak your database and any stored procedures (if you've written them for MySQL already), but it is not an insurmountable task.

You should be able to pick up enough to make the switch in a couple of weeks time, and if you set up a development database you can probably be well versed in routine tasks within a month, and confident you know where to look in the manual for the not-so-routine ones.

share|improve this answer
    
As an aside if anyone can dig up the slides from that PGEast talk I've been looking for them for about a month now and haven't been able to find them. Lousy USB drives wandering off with my data... –  voretaq7 Jan 28 '12 at 4:20
    
Did you mean this? postgresqlconference.org/2010/east/talks/… Need to register to view the slides though. –  R.K. Mar 29 '12 at 3:01
    
@R.K. YES, that's the link I've been looking for. AND I remember my username! PARTY! –  voretaq7 Mar 29 '12 at 3:44
    
I couldn't find the link for the slides though :( –  R.K. Mar 29 '12 at 3:52
add comment

I totally agree with all the statements of the first answer, but sharing my own experience -I’ve made this on my country's National Roads Administration: production critic, high traffic site. I suggest a web app be feed by both MySQL and PostgreSQL/PostGIS.

For all the "typical" stuff, the web app works flawlessly with a MySQL based CMS. For all the spatial tasks, the same web app works -also flawlessly ;) with PostgreSQL/PostGIS grounded custom development. The first component was developed and is maintained- effortlessly with normal MySQL skills. The second component involved a bit more research effort at the beginning.

You don't have to force a costly entire implementation of typical stuff in the not-so-familiar PostgreSQL/PostGIS and you don't have to force a suboptimal implementation of the geospatial stuff in the MySQL neither. Let every player play where it can hit.

share|improve this answer
    
I would normally avoid a dual-database implementation where one isn't absolutely required. Installing and maintaining two separate database engines commits you to a substantial amount of long-term work, and increases the testing burden. Learning the very minor differences between MySQL and Postgres in the "general utility" realm is a relatively small amount of one-time work and makes for a cleaner architecture when you're done... –  voretaq7 Jan 30 '12 at 22:46
add comment

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.