How to store lots of GPS data in database?

Hi,

it’s not particulary Rails related question, but I hope you guys can
give me some tips.

I’d like to store in the database routes build of lots of single
points (latitude, longitude, time and maybe altitude and speed if
available) and later display the route i.e. on Google Maps or
something similar.

Should I store each point as a single row in the db and give it some
route_id field to know which route it belongs to? If i.e. a single
route is built of 1000 points, having 1000 routes gives, well, a lots
of records.

Or would it be better idea to create a route model that has blob field
and simply put there all points as binary data and later parse it
every time I need to display the route?

Any suggestions? Thanks in advance

This sounds like a prototypical (love this word) oo solution:

Model points - stores points (id/lat/lon/time/etc.,route_id,seqno)
(since the order of the points is important the points for the route
should have a field to maintain their logical order).
Model routes - has many points (id, route name, etc.)
etc,

Justin

On 26 Kwi, 20:59, jmhirsch [email protected] wrote:

On Apr 26, 8:54 am, szimek [email protected] wrote:

available) and later display the route i.e. on Google Maps or

Any suggestions? Thanks in advance

Thanks for answering!

I know this solution is better from the “design” point of view, but I
was wondering whether i.e. storing all points related data in a huge
array of hashes and serialize it to blob field (or JSON and store it
in text field) in Route model wouldn’t be better solution.

It surely won’t be as flexible as storing all points in separate
table, but the points table could quite fast grow to milions of
records - I’m not sure about db performance in this case. Also Rails
treats each row as a separate AR object - if single route is built of
i.e. 3000 points I’d have to create 3000 ActiveRecord objects - not
sure about memory usage here vs one array of 3000 simple hashes.

Regard,
Szymon

Szymon N. wrote:

i.e. 3000 points I’d have to create 3000 ActiveRecord objects - not
sure about memory usage here vs one array of 3000 simple hashes.

I think it depends what you want to do with these 3000 waypoints. If you
only want to store and fetch hashes, and it won’t be a problem to
generate these hashes, then it should be fine to store them as hashes.
But if you often need to read/change any point info faster, then getting
the value for a row may be faster and easier from a hash.

Smizek,

I’d like to store in the database … (latitude, longitude, …
and … display the route i.e. on Google Maps

Have a look at the geokit plugin:

http://geokit.rubyforge.org/readme.html

They’ve made all the hardwork for you.

FYI, mySql offers some SPATIAL support :
http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html

Alain R.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs