New project - Geospatial features

Setting up the Geospatial capabilities took longer than extected.

Database

There are many geospatial databases available. I’ve considered:

• Postgresql + PostGIS
• MySQL
• Elasticsearch

Postgresql + PostGIS

PG’s a powerful database and many people love it. There are a few things I don’t like about it though:

• the administration tools are ugly, both GUI and command line. I could never stand the bunch of createuser, createdb, etc commands. PgAdmin and Navicat are ugly, too
• PostGIS setup: it’s really painful, especially on a mac. The installation is annoying and I don’t like having to create a db template

MySQL

Everyone’s favourite toy database. The admin tools are pretty good (Sequel Pro) and the geospatial features are built-in. We all know its limitations, especially the malign type castings and non-standard SQL statements, but I still think it’s a very decent solution. I will eventually migrate to MariaDB.

Elasticsearch

I’ve dealt with it at my last job. It’s very powerful and it has some geospatial capabilities. Unfortunately it often feels cumbersome and I need just a small subset of its features. Makes testing a lot more painful and I can’t say I like the Tire gem, which is pretty much the standard Elasticsearch interface for Ruby. I even made a pull request on it a while ago.

So the winner is… MySQL.

Application-side

I have terrible memories of the old Ruby’s geospatial_adapter gem. I really don’t want to use it. I’ll give RGeo a try. It seems well coded and it’s far more recent. There are still some issues with Rails 4 though. I couldn’t manage to run db migrations with it and had to create the geospatial fields semi-manually:

add_column :places, :geometry, :geometry

The documented methods don’t work unfortunately:

create_table :places, :options => 'ENGINE=MyISAM' do |t|
  ...
  t.geometry :geometry
end

Maybe I did something wrong, who knows, but I’ve sure read all the available documentation. Using the latest version of the gem creates a circular dependency hell, too, so I had to fork activerecord-mysql2spatial-adapter and rgeo-activerecord, until I got bored with it.

Tools

Installing the GDAL tool suite turned out to be harder than expected. I’ve not been able to enable the MySQL support (for MySQL 5.6.13). The best I’ve got was

brew install gdal --HEAD

There must be some issues with mysql-connector-c. I’ll try again later.

Another useful tool is the TopoJson node module. Get it with

npm install -g topojson