IonicWind Software

Creative Basic => General Questions => Topic started by: Egil on July 07, 2016, 11:03:54 am

Title: Building databases whitout really knowing how...
Post by: Egil on July 07, 2016, 11:03:54 am

These questions applies to all the IW languages, but is posted here in the CB-section because I presently use CB to experiment with different routines for importing and arranging/converting data for my map project.

Have downloaded an "open source" XLS file from the Yahoo HFDL User Group.
This contains almost 5000 records of airplanes heard on the HFDL system, and all relevant information such as ICAO registration, international callsign (tail number), type and owner etc..
As with all such "open source" files, it has to be filtered for removal of misprints and duplicates. Just for fun, I removed the coloumns not needed for my application, deleted the duplicates found by browsing quickly through the file, and saved it as a text file (CSV). The size was reduced from 1.3MB to 213kB.

My first question is: What is the best way to save this "filtered" data. As a CSV file or one of the binary database formats?
I am going to use this file as a "look-up" table to find complete aircraft registration data based on one of three possible ID's used in the transmissions.


Also have to make a kind of "dynamic" database, where each record only will be stored for max 15 minutes. Older data is not needed and can be deleted. Each record consist of fields for time, flightnumber, latitude, longitude. The number of records will vary all the time, depending on radio propagation and on how many channels that are monitored at any given time. Last year I monitored four channels in paralell, and the maximum number of planes logged during any 15 minute intervall, was 293. But to be on the safe side I have to make room for 150 planes per channel.

Can anyone please explain how to make a FIFO buffer for this? Or will other methods be more convenient?
I know less than nothing about such use of memory, so, as they say in Sweden: "Speak slowly so I can perceive at my own rate..." ;) ;D ;D


The third one will be rather small. Containing ID, positions and frequency tables for each ground control station in the HFDL service (Presently 16 stations, but number can be changed any time). I plan to save this to a small CSV file, and load it whenever the program is loaded, and plot the stations on the map. If any of these stations is registerred by my receiver, it will be highlighted on the map, and stay highlighted for max 15 minutes after the last time it was heard (they all transmit ID  broadcasts every 33 seconds). This "database" is the easy one, and the only one I think I know how to do...


On top of the coding activities, we have Tour de France (cycling), European Athletics Championships in Amsterdam, and the European Football Championships going on in France, so this is a busy time for a sports addict like me.  ;D


Regards,
Egil
Title: Re: Building databases whitout really knowing how...
Post by: LarryMc on July 07, 2016, 12:01:47 pm
Glad to see you're back to working on "the" program.
Title: Re: Building databases whitout really knowing how...
Post by: Egil on July 07, 2016, 02:10:55 pm
Quote from: LarryMc on July 07, 2016, 12:01:47 pm
Glad to see you're back to working on "the" program.


So am I Larry. I expeprienced the hard way that coding and medication don't go together....
Now I have started in the end of this project where I should have started first. The data part, and then build the presentation part based on the data. Instead I started the other way round. Fortunately most of the data collection routines were done before my health condition became really bad last fall.
And when I was on the hospital I obtained another SDR receiver, so now I am able to monitor and decode data on eight channels in paralell. So hope the virtual audio cable software do what they promise. That's the only thing I havent tested yet.

But the main thing is that with most of the pain gone, I'm able to enjoy coding again. So you don't get rid of me yet....


All the best,
Egil.
Title: Re: Building databases whitout really knowing how...
Post by: billhsln on July 07, 2016, 02:54:32 pm
Just a thought, put the records into a database storing the timestamp.  Then have a timer that after 1 minute it deletes any records older than 15 minutes.  You should just need to store a small int value to keep track of which channel it came from.

Bill
Title: Re: Building databases whitout really knowing how...
Post by: LarryMc on July 07, 2016, 03:08:00 pm
Bill
How would you handle the fact that during any 15 minute interval he could recieve multiple signals from the same plane and he only wants to keep the latest during that 15 minute  interval.
Title: Re: Building databases whitout really knowing how...
Post by: billhsln on July 07, 2016, 04:48:56 pm
Make the plane ID the key and don't allow duplicates, so on insert it will tell you that it exists already, then delete the old one and insert the new.  Or just delete first and check for 'not found' result and then do insert.

Bill
Title: Re: Building databases whitout really knowing how...
Post by: LarryMc on July 07, 2016, 08:57:47 pm
Quote from: billhsln on July 07, 2016, 04:48:56 pm
Make the plane ID the key and don't allow duplicates, so on insert it will tell you that it exists already, then delete the old one and insert the new.

Knowing what I do from playing with it before I think this would be the way to go.
Title: Re: Building databases whitout really knowing how...
Post by: Egil on July 08, 2016, 03:20:23 am
I think using Flight Numbers will be the best way to index these records.

Every time the map is going to be updated, I first have to check for duplicates. If duplicate Flight Numbers are found, the positions must be checked, and if the latest reported position is different from the first, the planes course can be calculated before deleting the oldest report. This will then be indicated on the map, by drawing a short line to indicate the direction in which the plane is moving. The flight speed can also be calculated at this time, but is of no interrest to me as I shall certainly not intercept these planes. Then I have to step through the list again, to delete reports older than 15 minutes, and  finally update the map.

This is about as far as I had decided to do things before my health failed last year. So I know what to do, but not how to do it... yet.
Right now I found an old book in my shelf, decribing methods for programming FIFO buffers using C. So I have to study that text and try to understand how to do it in one of "our" languages.

By studying the logfiles produced by the decoding software, I have found that each plane uses four different ID's for each data communication session. ICAO registration number, International Callsign, Flight Number and finally a session ID assigned for each time they log on to the system. I have no idea why they do it so complicated, but think I have found a "pattern" for how the IDs are used.

But now it's lunch here, so have to leave.


Egil
Title: Re: Building databases whitout really knowing how...
Post by: Andy on July 08, 2016, 06:04:00 am
Egil,

I have some ideas for you but I don't know how long your program will be running for, i.e. 24/7, or a few days at a time, or just daily.

You could use Tick Count in conjunction with flight number, that will give you up to around 2 months before the count rolls over (or unless your machine is restarted).

You could use a Julian routine to convert the date, and use that with a routine that converts hours/mins/secs, together you could easily work out if a flight number is >= 15 mins and update accordingly.

Just ideas...

Andy.
:)
 
Title: Re: Building databases whitout really knowing how...
Post by: LarryMc on July 08, 2016, 06:09:00 am
Egil
I haven't gone back and read all our discussions from last year and my memory IS on the decline but the best I think I remember is
Quote from: Egil on July 08, 2016, 03:20:23 am
I think using Flight Numbers will be the best way to index these records.

Totally agree

Quote from: Egil on July 08, 2016, 03:20:23 am
Every time the map is going to be updated, I first have to check for duplicates. If duplicate Flight Numbers are found, the positions must be checked, and if the latest reported position is different from the first, the planes course can be calculated before deleting the oldest report. This will then be indicated on the map, by drawing a short line to indicate the direction in which the plane is moving. The flight speed can also be calculated at this time, but is of no interrest to me as I shall certainly not intercept these planes. Then I have to step through the list again, to delete reports older than 15 minutes, and  finally update the map.

I believe we worked out some pretty good IWBasic code to do all the above. I can't remember the exact file structure but it seems like it depended upon you supplying a data file every so often for it to pull its info from to update the the display file above.  I'm thinking that data file is the one you are think FIFO about.
If I'm correct in all the above then Bill has already solved your "FIFO" problem (maybe).
Change what Bill said to
"Make the Flight Numbers the key and don't allow duplicates, so on insert it will tell you that it exists already, then delete the old one and insert the new"

To be more specific
create a database file and index on the Flight Numbers
as you receive the flight info you write it to the database
since duplicates aren't allowed the db will never have more than one entry for a Flight
once every TBD delete obviously old entries to keep db from growing

Then once every TBD this db will be copied and used as the input db for the display portion of the program.

Let me know how close or how far off I am or am I totally missing why you are thinking FIFO.

LarryMc
Title: Re: Building databases whitout really knowing how...
Post by: LarryMc on July 08, 2016, 06:24:19 am
for those that are interested in the background of what Egil is talking about it all started with a simple question in March of last year

http://www.ionicwind.com/forums/index.php?topic=5611.msg41381#msg41381
Title: Re: Building databases whitout really knowing how...
Post by: Andy on July 08, 2016, 06:55:06 am
I might be completely off the mark here, but if you do need three things to check against, here is a little code that returns the date in Julian format, and time in seconds.

If you added the third equation - flight number this could give you all you need see attached.

Like I say, I might be completely off the mark, but it is an idea...

Andy.

Title: Re: Building databases whitout really knowing how...
Post by: Egil on July 08, 2016, 07:27:51 am
Quoteall started with a simple question in March of last year


Hehe, it turned out to be not so simple after all....

QuoteI believe we worked out some pretty good IWBasic code to do all the above. I can't remember the exact file structure but it seems like it depended upon you supplying a data file every so often for it to pull its info from to update the the display file above.  I'm thinking that data file is the one you are think FIFO about.


Your right! I almost fogot all about that code. I blaim it on the painkillers that converted my brain into sour milk...

To make the data compatible with other free tools for such monitoring, I had to change the data format slightly. The code below shows one of my experiments. Running the code first prints a line showing an original position report exactly how it came out of the decoder software.
Next line show the same report after it is converted into the new format. This is how each record in the FIFO will look like. At the same time the Greenwich Meridian and Equator will be used as reference lines in such a way that west longitudes and south latitudes are presented as negative values.
This should be clearly shown in the CB demo.

Just to avoid any misunderstandings:
1. All decoded transmissions go into the same file, regardless of what channel they were  transmitted on. The records are all plotted on the same map.
2. Exact time is not an important issue, only that plotted data is less than 15 minutes old. In the example below the timestamps are in STRING format, so they sould be fairly easy to compare. ( but if this is the fastest  way to do it, is quite another matter....)


Code Select
' FixPos.cba - by Egil-LA2PJ 2016
'
' Final log format (CSV):  Time, ID, Latitude, Longitude
' All extracted from Raw Position reports. Separate files for each date.
'----------------------------------------------------------------------------------------
AUTODEFINE "OFF"

TYPE POSITIONREPORTS
def rtime:string
def rid:string
def rlat:double
def rlon:double
ENDTYPE

def pt:POSITIONREPORTS

DECLARE PosFix(pos:string)
DECLARE PosConv(degrees:string,minutes:string,seconds:string,dval:string) as double

SETPRECISION = 8
def a$:string
a$=""

OPENCONSOLE

a$ = "14:53:58  UTC  Flight ID = SU1710  LAT 56 49 21  N  LON 38 18 15  E  "
PosFix(a$)
print a$
print pt.rtime + "," + pt.rid + ",", pt.rlat, "," ,pt.rlon
print

a$ = "14:53:58  UTC  Flight ID = CCA134  LAT 32 10 24  S  LON 114 36 41  E  "
PosFix(a$)
print a$
print pt.rtime + "," + pt.rid + ",", pt.rlat, "," ,pt.rlon
print

a$ = "14:54:52  UTC  Flight ID = CES787  LAT 53 58 45  N  LON 27 24 56  W"
PosFix(a$)
print a$
print pt.rtime + "," + pt.rid + ",", pt.rlat, "," ,pt.rlon
print

a$ = "14:55:10  UTC  Flight ID = CXA812  LAT 60 51 51  N  LON 51 20 28  W"
PosFix(a$)
print a$
print pt.rtime + "," + pt.rid + ",", pt.rlat, "," ,pt.rlon
print

a$ = "14:55:28  UTC  Flight ID = UAE5    LAT 37 45 27  S  LON 44 56 57  W "
PosFix(a$)
print a$
print pt.rtime + "," + pt.rid + ",", pt.rlat, "," ,pt.rlon
print

DO:UNTIL INKEY$ <> ""
CLOSECONSOLE
END
'----------------------------  END OF TEST PROGRAM  -------------------------------------



SUB PosFix(pos:string)
'----------------------------------------------------------------------------------------
' Parse HFDL position reports and extract time, IDs, latitudes and longitudes.
' Call PosConv twice, once for latitude and once for longitude.
' Insert results in UDT structure (ready for log and plot routines).
'----------------------------------------------------------------------------------------

def pp:double
def dg$,mi$,se$,di$:string
def p1,p2,tmp:int

' TIME (UTC):
pt.rtime = left$(pos,2) + mid$(pos,4,2) + mid$(pos,7,2)

' FLIGHT ID:
pt.rid   = mid$(pos,28,6)


' -----------  ARRANGE LATITUDES: 

' degrees
p1 = instr(pos,"LAT ") + 4
p2 = p1
WHILE mid$(pos,p2,1) <> " "
dg$ = dg$ + mid$(pos,p2,1)
p2 = p2 + 1
ENDWHILE

' minutes
p2 = p1+3
WHILE mid$(pos,p2,1) <> " "
mi$ = mi$ + mid$(pos,p2,1)
p2 = p2 + 1
ENDWHILE

' seconds
p2 = p1+6
WHILE mid$(pos,p2,1) <> " "
se$ = se$ + mid$(pos,p2,1)
p2 = p2 + 1
ENDWHILE

' latitude direction
p1 = instr(pos,"LON")-3
di$ = mid$(pos,p1,1)

' LATITUDE:
pt.rlat = PosConv(dg$,mi$,se$,di$)


' -----------  ARRANGE LONGITUDES:

' degrees
p1 = instr(pos,"LON ") + 4
p2 = p1
dg$ = ""
tmp=0

WHILE mid$(pos,p2,1) <> " "
dg$ = dg$ + mid$(pos,p2,1)
p2 = p2 + 1
tmp = tmp +1
ENDWHILE

if tmp = 3 :' If Longitude is a 3 digit value

' minutes
p2 = p1+4
mi$ = ""
WHILE mid$(pos,p2,1) <> " "
mi$ = mi$ + mid$(pos,p2,1)
p2 = p2 + 1
ENDWHILE

' seconds
p2 = p1+7
se$ = ""
WHILE mid$(pos,p2,1) <> " "
se$ = se$ + mid$(pos,p2,1)
p2 = p2 + 1
ENDWHILE

else :' Longitude is a 2 digit value

' minutes
p2 = p1+3
mi$ = ""
WHILE mid$(pos,p2,1) <> " "
mi$ = mi$ + mid$(pos,p2,1)
p2 = p2 + 1
ENDWHILE

' seconds
p2 = p1+6
se$ = ""
WHILE mid$(pos,p2,1) <> " "
se$ = se$ + mid$(pos,p2,1)
p2 = p2 + 1
ENDWHILE

endif

' longitude direction
di$=""
'tmp = len(pos)
di$ = mid$(pos,p2+2,1)

' LONGITUDE:
pt.rlon = PosConv(dg$,mi$,se$,di$)

RETURN


SUB PosConv(degrees:string,minutes:string,seconds:string,dval:string) as double
'----------------------------------------------------------------------------------------
' Converts from degrees, minutes and seconds to decimal degrees
' Decimal degrees =  degrees + (minutes/60) + (seconds/3600)
' Make separate calls for latitude and longitude.
' South latitudes and/or West longitudes are negative values
'----------------------------------------------------------------------------------------
def ret,tmp:double
def tret:string
ret=0
tmp=0

ret = val(degrees)
tmp = val(minutes)
ret = ret + tmp/60
tmp = val(seconds)
ret = ret + tmp/3600

select dval
case "S"
ret = -ret
case "W"
ret = -ret
endselect

RETURN ret




Title: Re: Building databases whitout really knowing how...
Post by: Egil on July 08, 2016, 07:35:19 am
Quote from: Andy on July 08, 2016, 06:55:06 am
I might be completely off the mark here


I refuse to comment on that... But thanks for the code.  :)



Egil
Title: Re: Building databases whitout really knowing how...
Post by: billhsln on July 08, 2016, 09:15:48 am
If you wish to keep more than 1 record and still stay with a 15 minute limit, use flight and timestamp as keys, then to get the most current, select * from table where flight=xxx order by timestamp desc.  Then read the first for most current and the next for where it was before, etc for as many as exist.  You can still do the delete based on 15 minutes.

Bill
Title: Re: Building databases whitout really knowing how...
Post by: Egil on July 08, 2016, 10:17:09 am
Thanks Bill. Thats probably a very good idea.  :)

Beginning to get a fair understanding of what I'm up to. So now I have many items to work with.


What is  bothering me right now, is that my internet connection has become rather slow. We had a power breakdown in the area a couple of hours ago. It lasted for only a few minutes. But after that, both the cellphone and data networks has been rather slow. My provider said on the phone that they are unable to send a maintanance crew to the area till late monday afternoon, and they could not guarantee that the networks would last that long. So if I "dissapear" from the forum this weekend, you know the reason.
Norwegian Communications Authority has a website where we can test the connection speed for the internet. Normally I get 16 Mbit/s download and 1.6 Mbit/s upload speed. But right now I got 0.07 Mbit/s download and 0.01 Mbit/s upload. Which means that I am unable to watch Tour de France this weekend, because I receive it on a  channel that is streamed to my TV via internet.... :'( :'( :'( :'(