Geospatial datastores in MySQL is totally possible, as I’ve recently learned, thanks to my involvement in the 2014 Sri Lanka design sprint for Google community managers. I was glad that the researchers in charge of analysing the dengue epidemic in the country was generous enough to include the MySQL shapefiles along with their documentation of their current geospatial analysis issues.
Interestingly, importing raw MySQL shapefile data (DBF, SHP, PRJ files in themselves) is actually slightly tricky than normal data files. Normally, I would use a MySQL admin console, create an empty database there, stop MySQL, copy the raw data files into the folder of the empty database, and start MySQL. That method works 99 percent of the time.
Not with MySQL SHP files, apparently.
It took me about half an hour Googling for the solution to the MySQL importing problem, reading through GDAL and even downloading MapServer for Windows (MS4W).
All it took, though, is for me to use the darned-simple PHP-based SHP to MySQL.
All that was needed it for me to change three variables at the most for it to connect to my database server, then run it as a script through my local server. Nice.
Next step is getting this format up into Google Cloud SQL and generate the GeoJSON files for use with Google Maps API. Crap. This design sprint is driving me insane. In a good way, I suppose, since I’ve never learned this much in such a short period of time. It’s crazy and exhilarating at the same time.
Wish me and my team the best!
Check out my other posts: « Design Sprint the Google[x] way / On blogging again and redefining web development »