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?

  • 1
    Are you planning dynamic "season" maps or static ones? – underdark Nov 11 '10 at 22:31
  • If I understand what you're asking, dynamic. Example: the growing season for Apples near Grand Rapids, MI is August to October. – Jason Swett Nov 12 '10 at 13:37

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.


| improve this answer | |

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.

| improve this answer | |
  • FME supports MySQL as well as PostGIS. – Raven Jul 27 '11 at 19:44

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.


| improve this answer | |

The MySQL vs Postgis battle rises once again:


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

links too


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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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