The existing birdbox setup remains the same, its a Raspberry Pi ZeroW running Pikrellcam for motion detection and auto capture. The LED illumination, IR cut and RasPi IR camera module etc remain unchanged.
 |
| Like before.. but with entrance hole activity logged to a database |
v3.0 entrance hole counter updates...
The two other boxes that I have 'in the wild' with entrance hole counters log partial and full 'in' vs 'out' events activity to a text file. This updates (v3.0) to a Postgres database instead. Using a database rather than a text file opens up more possibilities, in particular allowing collected data to be accessed remotely. I plan to pull data off for analysis on a separate machine, for example to populate activity plots on my grafana nestbox CCTV-sytle dashboard. Having the activity data sitting in a database makes this much easier. I've used a 'dockerised' version of postgres as its simpler to setup than installing it from scratch.
This post will cover
1) Docker Installation
2) Docker-Compose Installation
3) Add Postgres container to Docker installation
4) Creation of a database to log activity
5) The activity logging script itself
I use a base image of Raspbian 'Lite' to start off. This takes up less space on the SD card but may need more things installing along the way than than 'full-fat' version. You can follow these setup steps on a Raspberry Pi ZeroW, but be prepared to wait a long time for some of them (especially for Docker Compose). I use a spare Pi 3 or 4 for the build (much quicker) and transfer the sd card over to a Zero W when its done. The installtion Pi is assumed to be networked. There's about a zillion how-tos online for that.
1) Docker installation
Think of Docker as an empty applications box. Once Docker is running there are many many pre-configured applications than can be (fairly) effortlessly run without having to do lots of fiddly setup...
This is mostly lifted from these sites, with my comments added:
Install commands (I left my hashed-out comments in)
If it all works, you get the following message:
sudo apt-get install apt-transport-https ca-certificates software-properties-common -y curl -fsSL get.docker.com -o get-docker.sh && sh get-docker.sh sudo groupadd docker #not actually required, groupadd: group 'docker' already exists sudo gpasswd -a $USER docker newgrp docker #avoids reboot docker run hello-world #test that it works
If it all works, you get the following message:
Hello from Docker!
This message shows that your installation appears to be working correctly. Details here: https://dev.to/rohansawant/spin-up-a-postgres-docker-container-on-the-raspberry-pi-in-2-minutes-2klo and https://dev.to/rohansawant/installing-docker-and-docker-compose-on-the-raspberry-pi-in-5-simple-steps-3mgl
2) Docker-Compose Installation
Install proper dependencies:
sudo apt-get install libffi-dev libssl-dev sudo apt-get install -y python python-pip #?had issue with python 2.7.13 not being compatible with the version of docker-compose below
#edit
sudo apt-get install -y python3 python3-pip
sudo apt-get remove python-configparser
Install Docker Compose
#sudo pip install docker-compose #takes a long time on a Pi ZeroW
# edit 12/2/21sudo pip3 install docker-compose
#see details here
3) Add a Postgres database container to Docker:
See https://github.com/CT83/Raspberry-Pi-PostGres-Docker-Compose
Note, MySQL is another option, there are MySQL images available on Docker, or maybe install from scratch. I went with Postgres as I like pgAdmin as a sql interpretor.
# clone the repo which contains the Compose file: git clone https://github.com/CT83/Raspberry-Pi-PostGres-Docker-Compose.git # Up the container: cd Raspberry-Pi-PostGres-Docker-Compose sudo docker-compose up --build -d # note this returns an error for a file it cant find: # ERROR: Couldn't find env file: /home/pi/Raspberry-Pi-PostGres-Docker-Compose/.env # fix this by making a dummy file first: touch .env # then re-run sudo docker-compose up --build -d
All being well, postgres database should be running in a docker container on the host Pi, on the default port (5432). As-is the default user is postgres, password is password@7979 You can change these in the docker-compose.yml.
pgAdmin is a handy sql interpretor/tool for managing local or remote postgres databases. Install it on a networked PC and you should be able to remotely connect to the postgres instance running on the Pi created above. See https://www.pgadmin.org/. Google for loads of tutorials around this bit.
4) Creation of a database to log activity
Within pgAdmin (on a remote PC), this sql command should make the necessary database and table within it
CREATE DATABASE db_activity WITH OWNER = postgres ENCODING = 'UTF8' LC_COLLATE = 'C.UTF-8' LC_CTYPE = 'C.UTF-8' TABLESPACE = pg_default CONNECTION LIMIT = -1; CREATE TABLE public.entrance_log ( sensor integer, state integer, event_time timestamp without time zone ) WITH ( OIDS = FALSE ) TABLESPACE pg_default; ALTER TABLE public.entrance_log OWNER to postgres;
I'm starting basic, so there's only only one table, for the entrance hole activity.
5) The activity logging script
This is written in Python. Need to install some extra python packages first:
#install the postgres/sql connector psycopg2 pip install psycopg2 #it does not work properly, need to run the following command first: sudo apt-get install libpq-dev #(suggestion here: https://github.com/psycopg/psycopg2/issues/699)
The following python script creates a entry in the db_activity database in a table called entrance_log.
A simple bird 'head bob in' event breaks the outer beam[1,0], then the inner beam [2,0], then withdraws: Inner beam whole again [2,1], then outer beam whole [1,1].
Note that in this example, Outer beam = 1, Inner beam = 2, Broken = 0, Whole = 1
Database entry
 |
| This above event as recorded in postgres database |
I added some error checking code to stop the script from crashing out if a beam is triggered and it cant see the postgres database for whatever reason - it will carry on merrily logging to file instead.
I have left in the 'log to file' functionality as a backup, I'll may remove that eventually, its there as a backup.
 |
| The above event as logged to a txt file |
HoleSensor.py
import RPi.GPIO as GPIO from time import sleep #postgres error handling from here: https://kb.objectrocket.com/postgresql/python-error-handling-with-the-psycopg2-postgresql-adapter-645 #postgres add record adapted from here: https://pynative.com/python-postgresql-insert-update-delete-table-data-to-perform-crud-operations/ # import sys to get more detailed Python exception info import sys # import the connect library for psycopg2 from psycopg2 import connect # import the error handling libraries for psycopg2 from psycopg2 import OperationalError, errorcodes, errors # import the psycopg2 library's __version__ string from psycopg2 import __version__ as psycopg2_version from datetime import datetime # Change log # 16/09/19 updated for zerocam 7 # 14/12/19 modified to write activity to a local postgres database, database implemented in docker # postgres error handler added so the script does not crash out if the database isnt available for whatever reason GPIO.setmode(GPIO.BCM) #use BCM pin numbering system GPIO.setwarnings(False) whichBirdcam = 'zerocam6' #file used to log entrance actions EntrancelogFile='/home/pi/zerocam6/logs/'+zerocam6_birdlog.txt' OpenEntrancelogFile= open(EntrancelogFile, 'a', 0) #function to return the current time, formatted as # e.g. 13 Jun 2013 :: 572 def getFormattedTime(): now = datetime.now() return now.strftime("%d %b %Y %H:%M:%S.") + str(int(round(now.microsecond/1000.0))) #note that this does not work correctly, as rounding 040 will give 40, but this modified function is only meant as a backup for the local file log # ********* postgres stuff # define a function that handles and parses psycopg2 exceptions def print_psycopg2_exception(err): # get details about the exception err_type, err_obj, traceback = sys.exc_info() # get the line number when exception occurred line_num = traceback.tb_lineno # print the connect() error print '\n' "psycopg2 ERROR:", err, "on line number:", line_num print "psycopg2 traceback:", traceback, "-- type:", err_type # psycopg2 extensions.Diagnostics object attribute print '\n' "extensions.Diagnostics:", err.diag # print the pgcode and pgerror exceptions print "pgerror:", err.pgerror print "pgcode:", err.pgcode, '\n' def logEntranceEventPostgres(sensor, state): dt = datetime.now() #set the census datetime for the event try: conn = connect( dbname = "db_activity", user = "postgres", host = "127.0.0.1", password = "password@7979") except OperationalError as err: # pass exception to function print_psycopg2_exception(err) # set the connection to 'None' in case of error conn = None # if the connection was successful if conn != None: # declare a cursor object from the connection cursor = conn.cursor() print "cursor object:", cursor, '\n' #dt = datetime.now() cursor.execute('INSERT INTO entrance_log (sensor,state,event_time) VALUES (%s,%s,%s)', (sensor,state,dt,)) conn.commit() count = cursor.rowcount print count, "record(s) inserted successfully into entrance_log table" # close the cursor object to avoid memory leaks cursor.close() # close the connection object also conn.close() #append the same timestamp data to file for good measure #formattedTime_old = dt.strftime("%d %b %Y %H:%M:%S.") + str(int(round(dt.microsecond/1000.0))) #note that this is NOT correct since it 'rounds' a 041ms to 41 formattedTime = dt.strftime('%d %b %Y %H:%M:%S.%f')[:-3] #this works correctly #write to file until we figure out if the same data is being captured OpenEntrancelogFile.write(str(sensor) + "," + str(state) + "," + formattedTime + "\n") #setup GPIOs detect_OUTER = 22 #6 #set GPIO pin for Outer photransducer (input) detect_INNER = 23 #12 #set GPIO pin for Inner photransducer (input) print 'detect_OUTER = ' + str(detect_OUTER) print 'detect_INNER = ' + str(detect_INNER) #Constants #OUTER_BEAM = 1 #INNER_BEAM = 2 #WHOLE = 1 #BROKEN = 0 # setup GPIO pins: GPIO.setup(detect_OUTER, GPIO.IN) #set Outer GPIO Phototransducer as input GPIO.setup(detect_INNER, GPIO.IN) #set Inner GPIO Phototransducer as input #indicate the point the program started in the log OpenEntrancelogFile.write("### recordBird_v3 starting up at:" + getFormattedTime() + "\n") print "============================================" print whichBirdcam.upper() + ": Starting up entrance hole counter script..." sleep (0.5) #Set initial state of WasBroken for both beams: OUTER_WasBroken = False INNER_WasBroken = False # LED status check # LEDstate= GPIO.input(detect_INNER) print "" print "detect_OUTER status = " + str(GPIO.input(detect_OUTER)) print "detect_INNER status = " + str(GPIO.input(detect_INNER)) print "" #When the detector 'sees' IR led, the detector pin is 0/LOW/False #When the detector does not 'see ' IR led, the detector pin is 1/HIGH/True def checkStatus(): if GPIO.input(detect_OUTER): #if OUTER detector does not see IR led, print error, GPIO.input = HIGH print "OUTER beam detect failure!, status = " +str(GPIO.input(detect_OUTER)) #quit() else: print "OUTER beam detect - passed :) | Status = "+str(GPIO.input(detect_OUTER)) if GPIO.input(detect_INNER): #if INNER detector does not see IR led, print error, GPIO.input = HIGH print "INNER beam detect failure!, status = " +str(GPIO.input(detect_INNER)) #quit() else: print "INNER beam detect - passed :) | Status = "+str(GPIO.input(detect_INNER)) print "============================================" print "" def status2(): print "============================================" print "OUTER_IsWhole = "+str(OUTER_IsWhole) print "OUTER_WasBroken = "+str(OUTER_WasBroken) print "" print "INNER_IsWhole = "+str(INNER_IsWhole) print "INNER_WasBroken = "+str(INNER_WasBroken) print "============================================" print "" checkStatus() # (x,y) # x=beam (1=Outer,2=inner) # y=state (1=Whole,0=Broken) while (True): OUTER_IsWhole = (GPIO.input(detect_OUTER) == 0) #read current state of beam INNER_IsWhole = (GPIO.input(detect_INNER) == 0) #read current state of beam #print "" #print "INNER BeamIsWhole = " + str(INNER_IsWhole) #print "OUTER BeamIsWhole = " + str(OUTER_IsWhole) sleep(0.05) if (not OUTER_IsWhole and not OUTER_WasBroken): #if OUTER beam is broken [FALSE], and OUTER_WasBroken=FALSE (ie default value) #GPIO.output(greenLED, 1) #greenLED output to HIGH OUTER_WasBroken = True print "(OUTER,Broken)"+ getFormattedTime() status2() #logEntranceEvent(1,0) logEntranceEventPostgres(1,0) if (OUTER_IsWhole and OUTER_WasBroken): #if Outer beam is whole [TRUE] and OUTER_WasBroken=TRUE #GPIO.output(greenLED, 0) #greenLED output to LOW OUTER_WasBroken = False print "(OUTER,Whole)"+ getFormattedTime() status2() #logEntranceEvent(1,1) logEntranceEventPostgres(1,1) if (not INNER_IsWhole and not INNER_WasBroken): #if INNER beam is broken [FALSE], and INNER_WasBroken=FALSE (ie default value) #GPIO.output(redLED, 1) #redLED output to HIGH INNER_WasBroken = True print "(INNER,Broken)"+ getFormattedTime() status2() #logEntranceEvent(2,0) logEntranceEventPostgres(2,0) if (INNER_IsWhole and INNER_WasBroken): #if INNER beam is whole [TRUE] and INNER_WasBroken=TRUE #GPIO.output(redLED, 0) #redLED output to LOW INNER_WasBroken = False print "(INNER,Whole)"+ getFormattedTime() status2() #logEntranceEvent(2,1) logEntranceEventPostgres(2,1) GPIO.cleanup()
To make this script run in the background, add an entry to /etc/rc.local as follows:
# open a text editor in the command window
sudo nano /etc/rc.local
# after this section in the rc.local file _IP=$(hostname -I) || true if [ "$_IP" ]; then printf "My IP address is %s\n" "$_IP" fi # add the following python /home/pi/zerocam6/HoleSensor.py &
No comments:
Post a Comment