storing NMEA (AIS/GPS) history in database

[Martin van Es]

Hi All,

We use a couple of RPi as AIS receivers in a harbour. This works great.

We also have a script to store recent vessel GPS info in a mysql database so other applications can fetch there current location.

This is a buggy/old script and i would like to get rid of it and build something new.

It would be great if there is a way to store AIS data from kplex in a database.

I want one table with all current vessels in reach of AIS, so we can query the most recent info from this database.

And if this works good, i also want to store some historic gps movement, so we can request a gps track from his visit to the harbour.

Yes, i know there are already online services for this :-) but we like to store it ourself if possible.

This brings up two main questions.

1) how to get the NMEA data into a mysql database in readable format.

2) how to limit the amount of data send to the database.

to explain (2), one RPi receiver, on avarage receives between 20 - 30 messages a second.

We want 3 to 4 receivers to cover all the area. So updating the database on each message would be generating way to much updates.

does anybody have any ideas, tips, examples...

thanks in advance.

[Teppo Kurki]

Full disclosure: I am heavily involved with Signal K, an open source project for marine data interchange

http://signalk.org/

My solution to your problem would be to install Signal K server on your RPi, connect that to your kplex 0183 feed and create a plugin that writes incoming AIS data to a local db.

https://github.com/SignalK/signalk-server-node/blob/master/SERVERPLUGINS.md

There is already a plugin that writes all your own vessel's data (not just position but all data like depth, speed, wind data, engine data) to a local InfluxDb. That includes once per second throttling for position data, but does nothing with AIS data. So something to see for reference.

https://github.com/tkurki/signalk-to-influxdb/blob/master/index.js#L43

I am working on (= there is an existing branch that does this) writing own vessel's position data to a local SQLite as latlon & geohashes to allow bounding box searches to plot data-annotated tracks on a map in the browser.

Once the data is in the db it is fairly easy to create a GeoJSON speaking http api for it, allowing you to get the tracks in a universal format. My wip actually provides such a beast and now that I am writing this I realise I should make it work also without InfluxDb, as a plain track server.

https://github.com/vokkim/tuktuk-chart-plotter/pull/10#issuecomment-331733183

The advantage for Signal K in implementing something like this are
- data is already parsed to a nice, source agnostic format - no need to parse 0183 and in case you go N2K some day Things Will Just Work (TM)

- there is a framework for doing processing like this - you can write a plugin on a high level language (depends on your viewpoint, I know ;-) & share it

--
You received this message because you are subscribed to the Google Groups "kplex" group.
To unsubscribe from this group and stop receiving emails from it, send an email to kplex+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/kplex/536cf02e-3dbf-4f81-b341-b224352e8aa5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Keith Young]

1) how to get the NMEA data into a mysql database in readable format.

2) how to limit the amount of data send to the database.

For (1) the obvious statement is "with something to replace your buggy/old script" :-).  Write a program/script which decodes the data and enters it in the database as you desire.  How best to interface kplex with your script?  You can simply define a "file:" interface as standard output from kplex and pipe that into standard input of your script but that involves tightly coupling kplex and the script.  Decoupling them to allow easy restart of one might be better.  I'd probably do this as a little network server program which listens on a socket, takes in NMEA data, decodes it and stores it.  Using a kplex tcp interface with the "persist=fromstart" option means you can restart either process independently. Of course you can argue that if you wanted to do network programming you wouldn't be bothering with kplex. An alternative might be to use a named pipe (see mkfifo(1)) which kplex uses a file interface (with persist option) to write to and your program reads from.

For storing, if you're not familiar with it already, here's Eric Raymond's write-ups on NMEA-0183 which is my reference for decoding:

http://www.catb.org/gpsd/NMEA.html

And here's essential info for AIS decoding:

http://catb.org/gpsd/AIVDM.html

For (2), kplex does rate limiting but it does it rather simply. You give it a 5 character pattern to match against a sentence type of an incoming sentence.  If the pattern matches it will drop the sentence if it has seen another one which matches that pattern in the time you specified. So an output filter like this:

ofilter=~GPRMC/60

would pass at most one GPRMC sentence every 60 seconds

However this doesn't work the way you probably want with AIS.  An AIS message can be multi-part.  A rule like:

ofilter=~AIVDM/60

Might get you one part-message every 60 seconds so you never actually get any data.  If storing to a database the obvious answer is to do any rate limiting in your own program *after* decoding.  So have your program to use the second, 3rd and 4th fields of an AIVDM message to buffer and reassemble whole messages (and discard part messages), then decode a complete sentence. If you're rate limiting AIVDM messages you don't process any more until your blackout period has completed. However it might make more sense to rate limit on a per-vessel basis, so keep an in-memory hash table recording the last time you recorded a value for that vessel in the database (and to finesse, what that value was: you don't necessarily want to record "at anchor" positions that often)

If Teppo has code which will do the heavy lifting of decoding this might help you out.  If you're happy to do your own decoding I'm not convinced that converting to Signal K is the best way to go unless you specifically want to store data in document format and then you'd probably (I am prepared to be corrected / educated on this as I storing of signal k in a geo-searchable format an interesting topic) want to do some post processing to convert signal k position to GeoJSON format.  For just storing specific rate-limited AIS and GPS data in mysql in an easily searchable format I'd be tempted to bypass signal k unless there was a pre-written application which suited the need.  YMMV :-)

[Teppo Kurki]

If Teppo has code which will do the heavy lifting of decoding this might help you out.  If you're happy to do your own decoding I'm not convinced that converting to Signal K is the best way to go unless you specifically want to store data in document format and then you'd probably (I am prepared to be corrected / educated on this as I storing of signal k in a geo-searchable format an interesting topic) want to do some post processing to convert signal k position to GeoJSON format.  For just storing specific rate-limited AIS and GPS data in mysql in an easily searchable format I'd be tempted to bypass signal k unless there was a pre-written application which suited the need.  YMMV :-)

There is a pre written application that does pretty much everything in Keith’s list (rate limiting, black/whitelisting data items, decoding 0183, time series handling ) and I am working sporadically on a geo searchable http api for the whole shebang.

Keith: the data in the db is not in SK, numbers not JSON. Signal K’s role in this application is just providing the db schema names for the various data dimensions - something that you need to do anyway, but if it is just a few fields it is not an issue. And if you switch some of your data to N2K things will keep on working when you decode inputs to SK

All in all if you know exactly what you need and know the tools it is not complicated. However decoding 0183 data is a wheel I would skip reinventing, there are solutions for that for all major programming languages.

[Keith Young]

Keith: the data in the db is not in SK, numbers not JSON. Signal K’s role in this application is just providing the db schema names for the various data dimensions - something that you need to do anyway, but if it is just a few fields it is not an issue. And if you switch some of your data to N2K things will keep on working when you decode inputs to SK

I'll check out your links in the coming week.  Off topic for here but before all my interesting projects were put on hold while I re-stock the cruising fund I was looking into alternatives for storing arbitrary signal k.  I ended up back at postgress which I haven't played with for years but seems to conveniently had support for searchable json docs and geosearch added since last I looked, so apparently the hybrid sweet spot: Relational for  few fields I really care about and which aren't part of the signal k and support for a searchable json document.  I need to get that project back on track.  I'd ask what other folks are using for storing/searching signal k but probably a better question for the signal k forum...