SQLite

[dacust]

Originally Posted by sredna

OpenCPN currently stores ALL your marks, routes and track data in memory, so there is lots of room for improvements. I believe using sqlite for data storage would be very efficient, it will be much faster than the current data lookup for these datas and probably tide/current data as well, and possibly more. The memory footprint is not scary, it is a few hundred kb, depending on how it is loaded. It can run embedded in mobile phone apps, so it can also run within openCPN!

It may store the marks in memory, but a few builds ago when I did a long route, I noticed that it read and wrote a completly new ini file each tie you moved/added/removed a mark. That was what was agonizing slow when the number of marks got over 1000 marks.

FYI: I was doing a detailed inland route - Charleston to Chattanooga. And I don't need all the comments about how some of you might think that is overkill to the max. In trips I have done, this detail comes in handy at least once during a 2-3 week trip.

And as I say, maybe this has changed in recent builds. But that route was not brought over to my new machine, so no easy way to go back and check.

-dan

[sredna]

Hi Dan,
That is exactly an example of something that can easily be optimized
And I do not think making detailed routes is wrong, that should not be a problem.

And also, that example exactly supports using sqlite, that would be much more efficient...

Btw I stumbled over the excessive flushing the config file while implementing the route manager dialog as well, and route/mark editing is mostly happening at non-critical times, I think it is worth optimizing that too.

[Psyches]

Originally Posted by dacust

It may store the marks in memory, but a few builds ago when I did a long route, I noticed that it read and wrote a completly new ini file each tie you moved/added/removed a mark. That was what was agonizing slow when the number of marks got over 1000 marks.

FYI: I was doing a detailed inland route - Charleston to Chattanooga. And I don't need all the comments about how some of you might think that is overkill to the max. In trips I have done, this detail comes in handy at least once during a 2-3 week trip.

And as I say, maybe this has changed in recent builds. But that route was not brought over to my new machine, so no easy way to go back and check.

Thanks, Dan. I think there are some quick, easy ways we could address this - agonizingly slow definitely isn't a good thing. For example we've discussed separating out routes, waypoints, and tracks from the core config - something that really should be done soon whether or not we do a major infrastructure change.

Anders, if you want to prototype SQLLite, of course feel free. If I were doing it, as a first cut I would do the separation and fix the import/export abstraction as I mentioned on another post, and then I'd go from there.

I'd suggest a quick think about the use cases, before building a schema. This may be boring and obvious to you, I dunno. For example, we might plan for hundreds of routes, and tens to hundreds of thousands of waypoints (to support POI). But track points would be in the hundreds of thousands to millions. So perhaps they are best stored as arrays?

If we do this, there would have to be a cut-off scale that waypoints, tracks, and routes are no longer shown - it might be fixed or flexible based on rowcount; you wouldn't want to end up reading twenty thousand waypoints to display (or insert your horror number here).

Based on what we need, there may be a better alternative to SQLLite. For example, if we decide our primary need is just lat/lon indexing, bdb, miniDB, a dbm variant, pblIsam or pblKeyfile, etc. could be a better match than SQLLite. Lots of options, and as was said earlier..."Why not use the right tool for the job?"

Mark

[sredna]

Mark,

I'll definately do the separation of config and location data, and the import/export abstraction, since that is affecting a lot of things. That will probably be next, when the route manager has reached reasonable state. After that, I would like to review some of the existing dialogs, but I am open to discuss that

[hackoon]

Originally Posted by Psyches

whatever problems you're seeing are solved by your suggested solution. It would say a lot to have clearly identified a problem with quantifiable data, and then show a solution that solves that problem.

I think you do foresee a real potential problem, one we'll want to be careful to address one way or another in a timely manner - not too late, and not too early.
Mark

Sorry I worked with php and sql so it will take me a while to get into C++. sqlite was just the first thing to jump up but I am happy to check out miniDB. The general idea is to just load position relevant tide, waypoint, route and trackdata into memory from a file. Anything what would index location data would do fine. Data sharing is my primary aim improving the speed was just an additional argument. I really don't care about sqlite or any other solution as long it permits data sharing.

[Netsurfer]

Hi all,

I'm by no means a programmer and I do not know anything about the actual data handling in OCPN. But I have some slight experiences of developing web projects. So these are more or less questions rather than suggestions.

I do believe that an efficient data handling is important for OCPN (in the future). Not only for the sake of being usable on low-performance hardware, also for usability reasons (routes, waypoints, tracks, marks, overlays, etc.).

Mark wrote that it would need major changes, which means a lot of work, to the core of OCPN.

But as I saw in just the last few days, developement is steadily going on.
So it won't be less sophisticated to do such changes in the future.

That leads me to think that it can't be too early to set up some kind of data handling structure (which is more appropriate than XML) as basis for all further/ future developement.

Which 'system' would be the most suitable/ practical, is in the means of the developers.

My conclusion is that there is no question if OCPN needs a (new) data handling system, but rather the question which one. The answer to this question depends on several factors and of course is not everthing foreseeable. But as actual discussions show Overlays become more and more a point of special interest beside the basic functions of route planning and tracking.

And as I already wrote: Don't see it only from a developers point of view, but also from that of a user (by usability).

Maybe it would be a good start to list here some of the requirements that should be fulfilled by a data handling system?

The newly developed and actually being tested Route Manager is also a good point to start off.

Here are some (very roughly) questions that came up to my mind:

• What kind of data should be handled (input - output/ import export)?
• How many data needs be handled?

And now you.

Gunther

[hackoon]

Had a quick look at miniDB. Still would go for sqlite because it isn't that much heavier but is widely used in many important applications which gives some assurance that this project keeps going. Apart that it runs on every major OS.

[HamishB]

Originally Posted by Psyches

I'd be surprised if it's smaller and faster than the XML parser.

Check it out, you will be surprised. SQLite is simply amazing, tiny, no server, and self contained in single .db files. It pretty much gives you the ease of DBF but with access to a modern format + power. "SQLitebrower" is fine, as is the firefox plugin (firefox uses sqlite internally).


for the very little that is it worth, for the Debian/etch packages I have installed
libexpat1: 62934 (used by wx)
libsqlite0: 180544

so both less than 200kb, and both bloody fast. The tech is there for both, so that shouldn't be a concern & you are free to let the needs of the software decide..


Hamish


ps - in GpsDrive we now use SQLite in addition to the old flat ascii text files for storing waypoints. GPX tracks and routes are supported but GPX waypoints remain TODO (but gpsbabel can convert those if needed). We were keeping the local OpenStreetMap POI db in MySQL but dropped that in favour of the much simpler SQLite. (Original OSM is stored in PostGIS [PostGres] AFAIK)

[HamishB]

ok, and sorry to harp on, but you might check out Spatialite too, you don't have to go very far out on a limb to say that it is no less than the future of geodata storage.
(SQLite's answer to PostGIS). Well, even if not used for OpenCPN it will be useful in some other project

[idpnd]

Originally Posted by HamishB

We were keeping the local OpenStreetMap POI db in MySQL but dropped that in favour of the much simpler SQLite. (Original OSM is stored in PostGIS [PostGres] AFAIK)

So you've already got the local db copy? Is it editable? Does it auto-sync upon internet becoming available? Portable?

[Psyches]

Hamish, thanks for the pointer to SpatialLite...interesting. Also thanks for the info re: SQLite use in GpsDrive.

[hackoon]

SpatialLite seems to be to good to be true. What arguments are left against sqlite integration? I would try to help with it where I can but my internet access is to sporadic to implement sqlite support at this moment.

[Psyches]

Originally Posted by hackoon

SpatialLite seems to be to good to be true. What arguments are left against sqlite integration? I would try to help with it where I can but my internet access is to sporadic to implement sqlite support at this moment.

Yes, it looks pretty interesting. Using it would be a very different way to slice up the chartplotter problem - in a potentially good way. To really do it right would require more than just "integration" - my guess is it'd require rewriting maybe 75% of the code base. And my guess is the chartplotter-specific code would then be quite a bit smaller. But maybe you could stage it somehow - exactly how left as an exercise to the reader. And this assumes there are no surprise performance or reliability problems. And for best advantage, all charts would need to go into the db. There are pros and cons to that.

Or maybe this really is too good to be true - we wouldn't know unless someone did some prototyping; if OpenCPN were started from scratch now, it'd make complete sense to look at doing it this way. And if this were my main gig, I'd be up for it. So to the person or folks that have the time - I'd say..."go for it".

[hackoon]

Originally Posted by Psyches

So to the person or folks that have the time - I'd say..."go for it".

Who is out there willing to help w'mith a sqlite integration/rewrite of opencpn? I can do a part of it but next few months I sailing into some wild rivers (iridium conection only!).

[idpnd]

Not much hope on the sql at this end

Where you off to?