Data storage advice needed: Best way to store location + time data?

Question

I have a project in mind that will require the majority of queries to be keyed off of lat/long as well as date + time.

Initially, I was thinking of a standard RDBMS where lat, long, and the datetime field are properly indexed. Then, I began thinking of a document based system where the document was essentially a timestamp and each document has lat/long with in it. Each document could have n objects associated with it.

I'm looking for advice on what would be the best type of storage engine for this sort of thing is - which of the above idea would be better or if there is something else completely that is the ideal solution.

Edit: Looking for an open source/free solution. Unfortunately price is an issue!

Thanks

Pharaoh

A document management system is not an RDBMS (in fact, it usually uses one to store the documents). Depending on how many data you expect to manage, query frequency and how many these return on average you could find out that a document system is not efficient at all. Please elaborate on why you think that the "timestamp" is best modeled as a document. – Virginia

I don't necessarily think that the timestamp is best modeled as a document. It's just something I was considering. As I stated below, in SQL-type language, I anticipate sets of queries between specific times and with locations. Those two pieces of data would be the most common fields keyed on. – Pharaoh

Answer 1

I have used PostGres (free open source db) with PostGIS extensions for working with location data. They are extremely good, even though I was working in an MS environment with all production databases using MSSQL 2005, I used PostGres w/GIS to manipulate and precalculate a lot of geographic data.

PostGis has utilities for import arcview .shp files which is a huge plus since that's how most geographic data is present. It also provides a host of location based sql functions like contains( ... ) and near( ... ); and it provides a mechanism for indexing spatial data.

It's been awhile since I used it, but I remember it being rock solid and very useful.

PostGIS: http://postgis.refractions.net/

Typography

Answer 2

SQL Server 2008 has new data types for storing and processing geographic information, in addition to the usual date and time types.

See "Working with Spatial Data (Database Engine)".

Hoof

My apologies - I should have been more clear, looking for an open source/free solution. – Pharaoh

SQL Server Express 2008 is free. – Hoof

But I believe there are restrictions on running it in a production environment? Last time I checked it did, I'll double check. Thanks! – Pharaoh

@sobedai: there are no restrictions about production. There are limitations on database size (4GB, I believe). – Hoof

Answer 3

SQL Express is a free database + has suppport to store lat/long & dateTime

just a tip - use UTC time for dateTime specially if your application needs to be aware of various locations...

hth.

Kain