What type of database schema should I use for storing and working with geolocation data?

Question

I'm creating an application that will store geolocation data for specific transactions. Should I store a simple lat/long or is there a more preferred standard? What datatypes should I use to allow for switching to NoSQL in the future?

Answer 1

• Forget about NoSQL - learn to use database or hire someone who can. Most NoSql "propositions" are by people mentally not getting around proper use of databases.

• Use special data types. Not sure about MySQL - but MS SQL Server added geometry / geolocation data with 2008 server. It is not as easy as having a data type, as the logic behind queries and how to build indices MUST be IN the database engine to be really efficient. Espeically the two-dimensional indices of a point and handling "forms" is not something easil and effectivel added in a post-step.

Answer 2

The World Geodetic Standard is widely used. The latest revision is WGS84. This is the coordinate system used by the Global Positioning System, among others.

Many databases have custom data types for dealing with spatial data. If this is the case with your database, you might consider using these for storing coordinates. Otherwise, coordinates may be stored as latitude/longitude. There are several open source libraries available for converting between various coordinate systems, so you should be able to convert your data without too much hassle if you need to switch later on.