Next / Previous / Contents / NM Tech homepage

Abstract

Describes a database to represent data from the Audubon Christmas Bird Counts, and a Python-language interface to that database.

This publication is available in Web form and also as a PDF document. Please forward any comments to john@nmt.edu.

Table of Contents

1. Introduction and scope
2. Downloadable files
3. Glossary
3.1. Count
3.2. Circle
3.3. Year number
3.4. Circle-year
3.5. Year key
3.6. Kind of bird
3.7. Count week birds
4. General design notes
4.1. Attributes of the principal entities
4.2. SQL considerations
5. Using the pycbc interface
5.1. The CBCData class
5.2. The Nation class
5.3. The Region class
5.4. The Physio class
5.5. The Circle class
5.6. The Effort class
5.7. The Census class
6. The SQL schema
6.1. pycbc.py: Prologue
6.2. Imports
6.3. Manifest constants
6.4. class CBCData: The database interface
6.5. The nations table
6.6. The regions table
6.7. The physios table
6.8. The circles table
6.9. The cir_reg table
6.10. The cir_physio table
6.11. The efforts table
6.12. The censuses table
6.13. Object-relational mapping
6.14. CBCData.__init__(): Constructor
6.15. CBCData.genNations()
6.16. CBCData.getNation()
6.17. CBCData.genRegions()
6.18. CBCData.getRegion()
6.19. CBCData.genPhysios()
6.20. CBCData.getPhysio()
6.21. CBCData.getRegionCircle()
6.22. CBCData.genCircles()
6.23. CBCData.genCirclesByName()
6.24. CBCData.genRegionCircles()
6.25. CBCData.genPrimaryRegionCircles()
6.26. CBCData.genCirclesByPhysio()
6.27. CBCData.getCircle(): Retrieve a specific circle
6.28. CBCData.genEfforts()
6.29. CBCData.getEffort(): Retrieve a specific effort record
6.30. CBCData.overlappers(): Find overlapping circles
6.31. CBCData.degMinAdd(): Lat/long arithmetic
6.32. CBCData.overlapCheck(): Do these circles overlap?
6.33. CBCData.__circleSep(): Compute the separation of two circles
6.34. CBCData.__terraCircle(): Convert a circle center to a terrestrial position
7. The staticloader script: Populate the static tables
7.1. staticloader: Prologue
7.2. staticloader: main()
7.3. staticloader: loadNations()
7.4. staticloader: addNation
7.5. staticloader: loadRegions()
7.6. staticloader: addRegion()
7.7. staticloader: loadPhysios()
7.8. staticloader: addPhysio()
7.9. staticloader: check()
7.10. staticloader: Epilogue
8. Conversion from the old MySQL database
8.1. Schema of the 1998 database
8.2. mycbc.py: Interface to the 1998 database
8.3. class MyCBC: Interface to the old database
8.4. MyCBC.__init__()
8.5. MyCBC.__mapTable: Locate and bind a table
8.6. MyCBC.genCirs(): Generate all circles
8.7. MyCBC.genStnds(): Generate all the circle-years for a given circle
8.8. MyCBC.getEff(): Retrieve the eff row for a given circle-year
8.9. MyCBC.getAsPub(): Retrieve the aspub row for a circle-year
8.10. MyCBC.genCens(): Generate census records for one circle-year
9. transloader: Copy over the MySQL database
9.1. transloader: Prologue
9.2. transloader: main()
9.3. transloader: readPassword()
9.4. transloader: dbCopy()
9.5. transloader: copyCir(): Copy data for one circle
9.6. transloader: addCircle()
9.7. transloader: addCircleYear()
9.8. transloader: addCensus()
9.9. transloader: Epilogue
10. Static data files

1. Introduction and scope

The National Audubon Society has been conducting the Christmas Bird Count (CBC) since 1900. The author has been working with digital representations of this database since 1975. This document represents a complete redesign of a previous version of the database.

  • This database is an older version of the current database maintained by the National Audubon Society. The author's current work is not an attempt to provide a parallel database. It is mainly of interest as an example of contemporary database design, and it also supports the author's work as the New Mexico regional editor of the CBC.

  • The starting point for the current work was the database design documented in the 1998 database specification. The current effort is a reimplementation of the data in this older database, with an improved design based on third normal form database normalization.

  • The current work is also a case study in database implementation using the Python programming language and the SQLAlchemy object-relational database mapping system.

    It will be implemented using the Postgresql database engine, and the data will be loaded from a representation of the old database that uses the MySQL database engine. Details of this translation process are discussed in Section 8, “Conversion from the old MySQL database”.