61 lines
2.2 KiB
Python
61 lines
2.2 KiB
Python
|
"""fires hypertable: NASA FIRMS active fire/hotspot detections
|
||
|
|
|
||
|
|
Revision ID: 002_fires
|
||
|
|
Revises: 001_initial
|
||
|
|
Create Date: 2026-08-24
|
||
|
|
"""
|
||
|
|
|
||
|
|
from alembic import op
|
||
|
|
import sqlalchemy as sa
|
||
|
|
|
||
|
|
|
||
|
|
# revision identifiers, used by Alembic.
|
||
|
|
revision = '002_fires'
|
||
|
|
down_revision = '001_initial'
|
||
|
|
branch_labels = None
|
||
|
|
depends_on = None
|
||
|
|
|
||
|
|
|
||
|
|
def upgrade() -> None:
|
||
|
|
# The PK IS the idempotency natural key (latitude, longitude, acq_time,
|
||
|
|
# satellite). acq_time is the hypertable partitioning column and is part of
|
||
|
|
# the PK, which satisfies TimescaleDB's requirement that every unique index
|
||
|
|
# include all partitioning columns.
|
||
|
|
op.create_table(
|
||
|
|
'fires',
|
||
|
|
sa.Column('latitude', sa.Float(), nullable=False),
|
||
|
|
sa.Column('longitude', sa.Float(), nullable=False),
|
||
|
|
sa.Column('brightness', sa.Float(), nullable=False), # bright_ti4, K
|
||
|
|
sa.Column('confidence', sa.String(10), nullable=False), # n/l/h or %
|
||
|
|
sa.Column('acq_time', sa.DateTime(timezone=True), nullable=False),
|
||
|
|
sa.Column('satellite', sa.String(16), nullable=False),
|
||
|
|
sa.Column('instrument', sa.String(16)),
|
||
|
|
sa.Column('bright_ti5', sa.Float()),
|
||
|
|
sa.Column('frp', sa.Float()),
|
||
|
|
sa.Column('daynight', sa.String(1)),
|
||
|
|
sa.Column('scan', sa.Float()),
|
||
|
|
sa.Column('track', sa.Float()),
|
||
|
|
sa.Column('version', sa.String(32)),
|
||
|
|
sa.Column('raw', sa.JSON()),
|
||
|
|
sa.Column('ingested_at', sa.DateTime(timezone=True),
|
||
|
|
server_default=sa.func.now(), nullable=False),
|
||
|
|
sa.PrimaryKeyConstraint(
|
||
|
|
'latitude', 'longitude', 'acq_time', 'satellite',
|
||
|
|
name='pk_fires_natural_key',
|
||
|
|
),
|
||
|
|
)
|
||
|
|
|
||
|
|
# 1-day chunks keep retention/drop_chunks operations cheap and scans local.
|
||
|
|
# (TimescaleDB auto-creates an index on the partition column acq_time.)
|
||
|
|
op.execute(
|
||
|
|
"SELECT create_hypertable('fires', 'acq_time', "
|
||
|
|
"if_not_exists => TRUE, chunk_time_interval => INTERVAL '1 day')"
|
||
|
|
)
|
||
|
|
op.create_index('ix_fires_bbox', 'fires', ['longitude', 'latitude'])
|
||
|
|
|
||
|
|
|
||
|
|
def downgrade() -> None:
|
||
|
|
op.drop_index('ix_fires_bbox', table_name='fires')
|
||
|
|
op.execute("SELECT drop_hypertable('fires', if_exists => TRUE)")
|
||
|
|
op.drop_table('fires')
|