Where o where should I store my geospatial data?

December 12, 2009

In a previous attempt at creating a proof of concept google maps related application, I naively used a plain vanilla mySQL database. Using separate float columns to store the latitude and longitude of points of interest, I was very easily and quickly able to conjure some simple maths and SQL to return all points of interest within a bounding box.  For more complex operations, such as searching for markers within a defined radius, I had to implement an algorithm in Java that would further restrict this (bounding box) subset.

It has to be said that this approach worked rather well for my prototype but after investing some valuable time on research, I quickly realised that there are better and more scalable ways to achieve this.  For example, what if I want to find markers within a complex polygon or distances between points etc?

A better approach is to use a GIS (geographical information system) enabled database which has a fundamental understanding of the geospatial data that I am looking to store and manipulate. Such a class of database provides a rich collection of spatial functions that enable you to perform sophisticated queries on the data.

An analysis of the two main open source databades, MySQL and Postgres, reveals that both can be made spatially aware.  However it was immediately obvious that mySQL had some significant shortcomings over its cousin PostGIS.  For example, performance issues regarding the creation of indexes that contain both numeric and spacial data as reported in http://blog.redfin.com/devblog/2007/11/elephant_versus_dolphin_which_is_faster_which_is_smarter.html.  Furthermore, unlike PostGis, many of the OpenGIS functions remain unimplemented (see below).

PostGIS adds support for geographic objects to the Postgres object-relational database. In effect, PostGIS “spatially enables” the PostgreSQL server, allowing it to be used as a backend spatial database for geographic information systems (GIS), much like ESRI’s SDE or Oracle’s Spatial extension. PostGIS follows the OpenGISSimple Features Specification for SQLand has been certified as compliant with the “Types and Functions” profile.

There is no doubt that the battle for supremacy between these two databases is very closely fought and ever-changing with each release but at the time of writing PostGIS is a clear winner given my requirements.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: