Next / Previous / Contents / NM Tech homepage

6.11. The efforts table

Each row in this table represents a published count for a specific circle in a specific year number. The lat and lon columns have a foreign-key relation to the circles table.

Many of the columns will have null values, especially in the early years. However, the year number (year_no), date (yyyymmdd), and number of observers (n_obs) are always present.
    efforts_table = schema.Table('efforts', meta,
        schema.Column('lat',         types.CHAR(4)),
        schema.Column('lon',         types.CHAR(5)),
        schema.Column('year_no',     types.CHAR(3),  nullable=False),
        schema.Column('year_key',    types.CHAR(5),  nullable=False),
        schema.Column('yyyymmdd',    types.DATE,     nullable=False),
        schema.Column('as_lat',      types.CHAR(4)),
        schema.Column('as_lon',      types.CHAR(5)),
        schema.Column('as_name',     types.VARCHAR(80)),
        schema.Column('n_obs',       types.SMALLINT,  nullable=False),
        schema.Column('ph_tot',      types.NUMERIC(6,2)),
        schema.Column('ph_foot',     types.NUMERIC(6,2)),
        schema.Column('ph_car',      types.NUMERIC(6,2)),
        schema.Column('ph_other',    types.NUMERIC(6,2)),
        schema.Column('h_fd',        types.NUMERIC(6,2)),
        schema.Column('h_owl',       types.NUMERIC(6,2)),
        schema.Column('pm_tot',      types.NUMERIC(6,2)),
        schema.Column('pm_foot',     types.NUMERIC(6,2)),
        schema.Column('pm_car',      types.NUMERIC(6,2)),
        schema.Column('pm_other',    types.NUMERIC(6,2)),
        schema.Column('m_owl',       types.NUMERIC(6,2)),
        schema.PrimaryKeyConstraint('lat', 'lon', 'year_no', 'year_key'),
        schema.ForeignKeyConstraint(('lat', 'lon'),
            ('', 'circles.lon')))

        efforts_table.c.year_no, efforts_table.c.year_key)

    class Effort(object):
        def __init__(self, lat, lon, year_no, year_key, yyyymmdd,
                as_lat, as_lon, as_name, n_obs,
                ph_tot=None, ph_foot=None, ph_car=None, ph_other=None,
                h_fd=None, h_owl=None,
                pm_tot=None, pm_foot=None, pm_car=None, pm_other=None,
   = lat
            self.lon = lon
            self.year_no = year_no
            self.year_key = year_key
            self.as_lat = as_lat
            self.as_lon = as_lon
            self.as_name = as_name
            self.yyyymmdd = yyyymmdd
            self.n_obs = n_obs
            self.ph_tot = ph_tot
            self.ph_foot = ph_foot
            self.ph_car = ph_car
            self.ph_other = ph_other
            self.h_fd = h_fd
            self.h_owl = h_owl
            self.pm_tot = pm_tot
            self.pm_foot = pm_foot
            self.pm_car = pm_car
            self.pm_other = pm_other
            self.m_owl = m_owl
        def __repr__(self):
            return ( "<Effort(%sn %sw[%s-%s])>" %
                     (, self.lon, self.year_no, self.year_key) )