What is the best way to store geographic coordinate data in a database

Question

I'm building a mobile mapping application and I want to store GPS Track data, waypoints etc in a database (SQL CE 4.0). This means I only have access to basic datatypes (http://msdn.microsoft.com/en-us/library/ms172424(SQL.110).aspx)

1. What Coordinate system should I use (Lat/Long, WGS 84, ...)
2. What datatype would you suggest given the answer to 1.

Operations I'm likely to want to do

• Find all Points of Interest within x distance
• Calculate distance between points x and y
• Find nearest waypoint to point x

Answer 1

WGS84 is a Geodetic System (datum), not coordinate system - literally (W)orld (G)eodetic (S)ystem 19(84). So I am not 100% sure what you are asking.

If you are asking about deciding which datum to use, then it would depend on what you intend to do with the data and the locations you are dealing with. For example if you are solely dealing with UK based features and wish to display points on OS maps then OSGB36 would make the most sense. If you require a world-wide reference system and wish to use something such as Google Maps then WGS84 would be best. It is worth noting that WGS84 is the default standard datum for coordinates stored in recreational and commercial GPS units.

However all these datums define points based on a geographic coordinate system of latitude and longitude - so they all use the same 'coordinate system'. Converting between datums using a Helmert transformation matrix is also possible so you are never stuck even if some alternative system would be more applicable in a particular case.

All that aside, if you are using SQL CE I would suggest using a simple table to store the waypoints. Something like the following should work well in a lot of cases. You would need to decided on the level of precision you require for the coordinates. e.g.

CREATE TABLE 'waypoints' (
 'id' INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 'name' VARCHAR( 60 ) NOT NULL ,
 'type' VARCHAR( 30 ) NOT NULL ,
 'latitude' FLOAT( 10, 6 ) NOT NULL ,
 'longitude' FLOAT( 10, 6 ) NOT NULL
)'

Obviously you may not need fields such as 'name' and 'type', they are simply there to show how one might distinguish the waypoints in a human readable way.

--

Just to add - as for the various operations or calculations you may wish to perform; within x distance, distance between x and y, etc. You again would need to decide what level of precision you require in your application. For most cases using the simple spherical law of cosines will suffice, however you may wish to implement something more accurate such as the Haversine formula, or Vincenty's formulae, etc.