AcidMap/migrations/versions/29a56094509c_add_four_county_land_use_table.py

"""add_four_county_land_use_table

Revision ID: 29a56094509c
Revises: d89bb52ae481
Create Date: 2025-08-17 10:01:27.121198

"""
from alembic import op
import sqlalchemy as sa
import geoalchemy2


# revision identifiers, used by Alembic.
revision = '29a56094509c'
down_revision = 'd89bb52ae481'
branch_labels = None
depends_on = None


def upgrade():
    """升级数据库到当前版本 - 创建四县三种用电土地利用类型表"""
    # 创建四县三种用电土地利用类型表
    op.create_table(
        'four_county_land',
        sa.Column('gid', sa.Integer(), autoincrement=True, nullable=False, comment='主键ID'),
        sa.Column('objectid', sa.Integer(), nullable=True, comment='对象标识符'),
        sa.Column('bsm', sa.String(length=18), nullable=True, comment='标识码'),
        sa.Column('ysdm', sa.String(length=10), nullable=True, comment='要素代码'),
        sa.Column('tbybh', sa.String(length=18), nullable=True, comment='图斑预编号'),
        sa.Column('tbbh', sa.String(length=8), nullable=True, comment='图斑编号'),
        sa.Column('dlbm', sa.String(length=5), nullable=True, comment='地类编码'),
        sa.Column('dlmc', sa.String(length=60), nullable=True, comment='地类名称'),
        sa.Column('qsxz', sa.String(length=2), nullable=True, comment='权属性质'),
        sa.Column('qsdwdm', sa.String(length=19), nullable=True, comment='权属单位代码'),
        sa.Column('qsdwmc', sa.String(length=60), nullable=True, comment='权属单位名称'),
        sa.Column('zldwdm', sa.String(length=19), nullable=True, comment='坐落单位代码'),
        sa.Column('zldwmc', sa.String(length=60), nullable=True, comment='坐落单位名称'),
        sa.Column('tbmj', sa.Numeric(precision=19, scale=11), nullable=True, comment='图斑面积'),
        sa.Column('kcdlbm', sa.String(length=5), nullable=True, comment='扣除地类编码'),
        sa.Column('kcxs', sa.Numeric(precision=19, scale=11), nullable=True, comment='扣除系数'),
        sa.Column('kcmj', sa.Numeric(precision=19, scale=11), nullable=True, comment='扣除面积'),
        sa.Column('tbdlmj', sa.Numeric(precision=19, scale=11), nullable=True, comment='图斑地类面积'),
        sa.Column('gdlx', sa.String(length=2), nullable=True, comment='耕地类型'),
        sa.Column('gdpdjb', sa.String(length=2), nullable=True, comment='耕地坡度级别'),
        sa.Column('xzdwkd', sa.Numeric(precision=19, scale=11), nullable=True, comment='线状地物宽度'),
        sa.Column('tbxhdm', sa.String(length=6), nullable=True, comment='图斑细化代码'),
        sa.Column('tbxhmc', sa.String(length=20), nullable=True, comment='图斑细化名称'),
        sa.Column('zzsxdm', sa.String(length=6), nullable=True, comment='种植属性代码'),
        sa.Column('zzsxmc', sa.String(length=20), nullable=True, comment='种植属性名称'),
        sa.Column('gddb', sa.Integer(), nullable=True, comment='耕地等别'),
        sa.Column('frdbs', sa.String(length=1), nullable=True, comment='非入等标识'),
        sa.Column('czcsxm', sa.String(length=4), nullable=True, comment='城镇村属性码'),
        sa.Column('sjnf', sa.Integer(), nullable=True, comment='数据年份'),
        sa.Column('mssm', sa.String(length=2), nullable=True, comment='模式识别码'),
        sa.Column('hdmc', sa.String(length=100), nullable=True, comment='河道名称'),
        sa.Column('bz', sa.String(length=254), nullable=True, comment='备注'),
        sa.Column('shape_leng', sa.Numeric(precision=19, scale=11), nullable=True, comment='形状长度'),
        sa.Column('shape_le_1', sa.Numeric(precision=19, scale=11), nullable=True, comment='形状长度1'),
        sa.Column('shape_area', sa.Numeric(precision=19, scale=11), nullable=True, comment='形状面积'),
        sa.Column('geom', geoalchemy2.types.Geometry(geometry_type='POLYGON', srid=4526, from_text='ST_GeomFromEWKT', name='geometry'), nullable=True, comment='几何位置信息-多边形'),
        sa.PrimaryKeyConstraint('gid'),
        comment='四县三种用电土地利用类型数据'
    )
    
    # 创建空间索引
    op.execute("CREATE INDEX IF NOT EXISTS idx_four_county_land_geom ON four_county_land USING gist (geom)")


def downgrade():
    """将数据库降级到上一版本 - 删除四县三种用电土地利用类型表"""
    # 删除空间索引
    op.drop_index('idx_four_county_land_geom', table_name='four_county_land')
    # 删除表
    op.drop_table('four_county_land')