Would PostGIS offer an advantage over MySQL for a produce farm application? [closed]

Question

I have a web app that stores the locations of farms in West Michigan. You can search for a product (e.g. "broccoli") and it will show you all the farms that grow that product.

Right now I'm using MySQL and using trigonometry to calculate the difference between the user's location and the location of each farm. It's not a bad way to go but it did take some doing.

Another thing I want to do soon is to map out growing seasons for different products for different regions. (For example, I want to show that avocados grow at a certain time of year in California but never Ohio.)

I realize this is an open-ended and possibly naive question, but might it be worth it for me to make the switch to PostgreSQL/PostGIS to take advantage of its spatial capabilities?

Answer 1

I am a great PostGIS fan and have no experience with MySQL so I mght be biased.

But from what you write I think of two reasons to switch.

first, it will most surely be much easier to implement new features like the season map you mentioned.

second, when you today do your trigonometry calculations I guess you are doing it outside the db. if you do all that in the db instead you are much more free in your development of the overlaying applications.

you will probably not have to do any calculating outside the db if you run postgis.

the season thing you mentioned might be doable in MySQL to since it sounds very basic but you will get mmore flexibility in PostGIS with access to all spatial functionality.

/Nicklas

Answer 2

If only because you will have a lot more choice in third-party applications for generating maps of your information (mapserver, geoserver, etc, etc) loading data (ogr2ogr, fme, etc) PostGIS would make a better choice. MySQL will only suit if your needs continue to be relatively limited.

Answer 3

MySQL has also a spatial extension but, as far as I know (I have never used it), is not as feature rich and stable as PostGIS.

If you are considering to use a spatial database, PostGIS is a good choice and the effort of switching will be worth.

While MySQL already provides some functionality to store and operate on geospatial data, the functionality leaves quite a lot to be desired and is far from providing full OpenGIS compatibility.

Most notably is that all functions that query spatial data only operate on MBRs (minimum bounding rectangles), to simplify the operations.

http://forge.mysql.com/wiki/GIS_Functions

Answer 4

The MySQL vs Postgis battle rises once again:

http://ambergis.wordpress.com/2008/02/19/mysql-vs-postgis/

Note the comment-ers most are from here (gis stack exchange.)

links too

http://www.spatiallyadjusted.com/2008/02/05/bringing-open-source-gis-into-an-esri-shop/#comment-32680

Have had more successful deployments with postgis than mysql. (depend on clients set ups and what they are trying to achieve)

My only suggestion to Paul Ramsey (and PostGIS team) is a nice GUI for postgis via PgAdmin (v4..?) with a visualiser (like safe software's FME) - not just attributes would be a major plus. Currently use QGIS for visualising postgis data.