What to do with the database

[mcguirepr89]

As mentioned by @CaiusX, feeding the BirdNET analyzed data into a database is the obvious next step in building this project out for research purposes.

Below is a list of some isolated values (for BirdNET-Lite) that are available from each piece of analyzed data. These values are already ready to use in the extraction.service and can be repurposed for database entry as needed (I HOPE!!).

• Species Common Name
• Species Scientific Name
• Confidence Score
• Overlap
• Exact timestamp accurate to ~3 seconds
• Latitude & Longitude

Other data that could potentially be made available to accompany what is already available:

• Weather information (if the BirdNET-system retains internet connectivity during data collection).
  • I've been wondering how to incorporate periodic weather reports that can take place in the morning, midday, and evening. This is not very practical without a database to assert the relationships between the tables and values.

Let's use this discussion to figure out how to get a database playing along with the BirdNET--system!

Thanks everyone!

[DD4WH]

Yes, that is a good idea!
From this database, one could add the possibility of drawing a graph for

• a specific species (histogram of confidence scores)
• no. of species exceeding a given confidence score for a determined time span
• etc.

One additional parameter would be the sensitivity that the user chose for the BirdNET-Lite model to work with

[mcguirepr89]

@CaiusX I am very close to having the analyze.py script write to a database but need your Python help.
I have setup a very simple database with one table for now: detections
The table just uses each field from the part of your script that creates the new BirdDB.txt file. They are as follows:
Date, Time, Sci_Name, Com_Name, Confidence, Lat, Lon, Cutoff, Week, Sens, Overlap

I'm VERY close to having analyze write results straight to the DB. The issue I'm running into right now is how the "Sci_Name" and "Com_Name" variables are created, using entry[0].replace('_', ';'). If I could parse that entry into two separate variables, then we will be in business. I tried just replacing the semi-colon with a comma in hopes that it would suffice to split the names into two fields, but mysql still reads it as one field and complains about missing a piece of data.

Let me know if this is something you think you can help out with. I am sharing the relevant parts of my analyze.py code here for now:

import mysql.connector
...
   
    # Write detections to Database
    for i in detections:
        print("\n", detections[i][0],"\n")
    with open('BirdDB.txt', 'a') as rfile:
            for d in detections:
                print("\n", "Database Entry", "\n")
                for entry in detections[d]:
                    if entry[1] >= min_conf and (entry[0] in WHITE_LIST or len(WHITE_LIST) == 0):
                        current_date = now.strftime("%Y/%m/%d")
                        current_time = now.strftime("%H:%M:%S")
                        rfile.write(str(current_date) + ';' + str(current_time) + ';' + entry[0].replace('_', ';') + ';' \
                        + str(entry[1]) +";" + str(args.lat) + ';' + str(args.lon) + ';' + str(min_conf) + ';' + str(week) + ';' \
                        + str(sensitivity) +';' + str(args.overlap) + '\n')

###############################################################################    
###############################################################################    

                        def insert_variables_into_table(Date, Time, Sci_Name, Com_Name, Confidence, Lat, Lon, Cutoff, Week, Sens, Overlap):
                            try:
                                connection = mysql.connector.connect(host='localhost',
                                                                     database='birds',
                                                                     user='birder',
                                                                     password='supersecretdbpassword')
                                cursor = connection.cursor()
                                mySql_insert_query = """INSERT INTO detections (Date, Time, Sci_Name, Com_Name, Confidence, Lat, Lon, Cutoff, Week, Sens, Overlap)
                                                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """
                        
                                record = (Date, Time, Sci_Name, Com_Name, Confidence, Lat, Lon, Cutoff, Week, Sens, Overlap)

                                cursor.execute(mySql_insert_query, record)
                                connection.commit()
                                print("Record inserted successfully into detections table")

                            except mysql.connector.Error as error:
                                print("Failed to insert record into detections table {}".format(error))
                            
                            finally:
                                if connection.is_connected():
                                    connection.close()
                                    print("MySQL connection is closed")
    
                        insert_variables_into_table(str(current_date), str(current_time), entry[0].replace('_', ', '), \
                        str(entry[1]), str(args.lat), str(args.lon), str(min_conf), str(week), \
                        str(sensitivity), str(args.overlap))
###############################################################################    
###############################################################################  
                        print(str(current_date) + ';' + str(current_time) + ';' + entry[0].replace('_', ';') + ';' + str(entry[1]) +";" + str(args.lat) + ';' + str(args.lon) + ';' + str(min_conf) + ';' + str(week) + ';' + str(sensitivity) +';' + str(args.overlap) + '\n')  
                        time.sleep(3)
                        now = datetime.now()

if __name__ == '__main__':

    main()

[mcguirepr89]

I got his working!!!
I added this to split that variable:

species = entry[0]
sci_name,com_name = species.split('_')

and then change the "insert_variables_into_table" function to:

insert_variables_into_table(str(current_date), str(current_time), sci_name, com_name, \
str(entry[1]), str(args.lat), str(args.lon), str(min_conf), str(week), \
str(sensitivity), str(args.overlap))

[mcguirepr89]

Hello, all --

Now that the system writes detections to a database, what should we do with it?

Personal goals for use with the database for now:

• Create D3.js charts and graph visualizations of the data for the web interface (I've got a lot to learn before I'm able to get this working, but I'm hopeful!)
• Add weather data for detections
• Add microphone specs to detections
• Create detected species tables to provide the user with information about a detected bird
• Add the actual extracted audio to the database and change the web interface to something that retrieves the sound files from the database. This will open up web-design to someone who knows the bells and the whistles. PHP is already setup and configured to access the database when needed.
• Create species table from labels.txt (this will be done later today)
• Create a PHP web interface for querying the database for specific information
• I've reached out to Stefan Kahl (@kahst) to ask him whether it would be at all helpful for this project to feed the data back to him for the next model. I've not received any kind of answer nor acknowledgment of the offer. That said, it seems like a waste for us to collect this data and sit on it when the whole database would be so easily shared.

You all please let me know other opportunities that might be worth pursuing now that the system feeds a MariaDB.

Thoughts for improvement on how the system currently writes to the database:

• It would be best if analyze.py did the database creation, I think -- I know this is very easily doable, but I blew past it when working on getting the new table entries to work from analyze.py.
• I'm sure the data could be structured better. I know nothing about structuring data, so I know I will be going about things the longest and hardest way possible (as is my signature style).

[ghost]

The R-Language can be used for statistic analyze. If the system runns for a long time simple statistics from the db can be ideally generated with R.