ZeroTier Networking

[coupe]

If I had any energy left, I'd probably translate it to "golang" as Java is about as popular as wooden boats these days...

I did see the -9999 entries in altitude. I don't remember but I think that means NULL because I couldn't use "0" as that was a valid altitude. But I don't know why I just didn't enter the last good altitude received?? Too many brain cells lost since I wrote that.

[Oblivian]

In either case I applaud you for making it.

Has made a lot of locals and military watchers that know me rather chuffed

Not been able to find anything else that compares and allows you run queries and such so easily.

[coupe]

I looked at ADSBSQLite and it dawned on me that we shouldn't be seeing -9999 or -999.0 in the database. I fixed those things and updated the sql export in the github directory.
I also updated the SQLite database version.

I put an empty adsb.db database in there. It has the PRAGMA set to turn WAL on under journaling. This lets you view the database with sqlitebrowser while it is being written.

So now I see the NULL altitudes being put in target and targetecho.

I believe these NUL altitudes are from aircraft that are not ADS-B equipped, or they have not received an interrogation from a radar. I know in the US all planes need to have ADS-B but I'm not sure of the rest of the world. Why an airplane would squawk direction and speed and not altitude is probably some coding thing I've done.

I have seen one guy flying along and his altitude never updated in the database. But I might have missed it. He might have squawked altitude using ADS-B and then the database was overwritten with a NULL from the lack of a response to a ground site or AEW aircraft.

I'm not an expert though, ha, just a hacker...

Let me know if you try it and if it worked OK. You may know more about these things.

[coupe]

Thinking out loud it may be I need some more table items. Such as lowest and highest altitudes received, rather than just the last altutude.

[Oblivian]

It's beating me hard. I can't seem to find the perfect combo.

fr24 30003 output works. But misses some of the mode-c only military stuff. (still talking older 1.7 writer and dbase here)

I can switch from one 30003 output to the other near realtime with the stop/start of configured services, and while using the FR24 instance they're all populated.
And as soon as you switch to anything else like modesdeco or dump1090-fa they drop off writing mainly the Alt (squawk may too but I don't tend to keep those as 7700 etc isn't used here).

The writer still logs them as containing alt packets in the counts. But don't actually get dumped

So the only thing I can pin it on, is either the output formats differ when using classic beast and these alternate 30003 outputs. Or the beast-splitter output is auto-detecting the connection and changing options(that you can with DIP switches) for filtering like turning on DF17/18 only and turning off DF4,5. I've started to try force override those now to see if results differ.

[coupe]

There was a change in the way port 30003 was processed in the latest versions of adsbsqlite and adsbmysql. Before, the syntax errors weren't caught very well, and the new way seems to be a better algorithm. So you might try the latest adsbsqlite and see if it doesn't have these problems you are describing. All the port 30003 programs out there seem to take liberties with the protocol. It becomes a parsing problem for the missing values and series of unexpected comma's.

Do you have the source code to version 1.7? I might be able to change it to the new way. You can see the changes in SocketParse.java

if (data.startsWith("MSG")) {
token = data.split(",", -2); // Tokenize the data input line

The "-2" seemed to fix the syntax errors.

[Oblivian]

I do not, Infact it's a little older...

1.10

I should probably give up using beast splitter or anything else until such time I've worked out how to copy the data to a 1.9 table and convert between the different column types

Then worry about why it seems to filter some stuff and drop altitude

[coupe]

Give the SQLite version a go, and see what you think. I played with it a bit today, and uploaded the changes. Also built a SQLiteView program to display the tracks in the database. It uses an older design that mimics the old DEW Line Air Defense Command ha... https://github.com/srsampson

[coupe]

Since SQLite doesn't have all those different data types, maybe converting would be easier (TEXT, INTEGER, FLOAT mostly).

[Oblivian]

That was another option I had considered. But it may involve the need to dust off the dark mater to write a new php page or 2 to query using PDO instead of sql to see if I get the same results :P

Interestingly, even before any mods sqlite expert allowed me to open the .db while it was being written and refresh just fine

An example of what I have it doing...

Quite some smarts going on in the background. Queries SQL, for the primary HEX/live data. And at the same some smarts (but slow on the PC) cross referencing with PDO connection to my basestation.sqb file for the type, owner data and so on.

Frames (EWWWWWW). Top one being query of the 'tracked' table for live data. Even knowing the changes in altitude. Bottom being targethistory

And I have some search pages which work in reverse to lookup requests.

adsbout.JPG

[Oblivian]

Oh I see where there is a big design change limiting the data copy

varchar date in 1.10 isn't numeric *face slap*

yyyy-mm-dd hh:mm:ss.ss Vs unix time bigint

Wonder if I can massage the current column format pre copy to match.

[coupe]

I made some updates to ADSBSQLite and SQLiteView this morning. Caught a few more bugs. Also added in the Air to Air counter that was missing. This is the Altitudes most reported.

[Oblivian]

Made more of a mess somehow

Tried to update Mysql version, and before I realised the new one stamps timestamp with _6_ decimal places (which breaks the utcxx fields)
Roll back to the original and now somehow I've reset the counter of flight_id reset back to 0. Oops

Looking more and more like a start again with 1.9. and need to join run 2 archived lookups. 1 pre 2021. one not.

sigh

/edit. Lets try a sneaky inline flight_id change to reset the increment marker

[Oblivian]

I really should have just left it on old stuff :/

Pre changes it opened one OK.

But now... Unable to open database 1 jdbc:sqlite:adsb.db

And yep, tried full path too

Took away the mysql one, was that on purpose? (I have it before your mods I can revert to and start fresh)

[coupe]

ADSBMysql was updated a bit to include verticalTrend (-1,0,1). Are you running Windows? Might have to specify the C:\ (backslash) stuff on database location in ADSBSQLite. I've done that before with resetting the counters. Unfortunately it was on a web site people were registering for an event. Whoo-boy... I never liked databases ha.