AcidMap/migrations/versions/66f4104d9498_init.py

"""init

Revision ID: 66f4104d9498
Revises: 
Create Date: 2025-05-10 18:05:19.822335

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


# revision identifiers, used by Alembic.
revision = '66f4104d9498'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    """升级数据库到当前版本"""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('Conventional_land_information',
    sa.Column('Lon', sa.Float(precision=53), nullable=False, comment='经度-Long.(°)'),
    sa.Column('Lat', sa.Float(precision=53), nullable=False, comment='纬度-Lat.(°)'),
    sa.Column('Altitude', sa.Numeric(), nullable=True, comment='海拔-Alt.(m)'),
    sa.Column('Landform', sa.Text(), nullable=True, comment='地貌类型'),
    sa.Column('Landuse', sa.Text(), nullable=True, comment='土地利用类型'),
    sa.Column('Soiltype', sa.Text(), nullable=True, comment='土壤类型'),
    sa.Column('Lithologic_type', sa.Text(), nullable=True, comment='岩性类型'),
    sa.PrimaryKeyConstraint('Lon', 'Lat'),
    comment='常规土地信息'
    )
    op.create_table('Point_information',
    sa.Column('Lon', sa.Float(precision=53), nullable=False, comment='经度-Long.(°)'),
    sa.Column('Lat', sa.Float(precision=53), nullable=False, comment='纬度-Lat.(°)'),
    sa.Column('Province', sa.Text(), nullable=False),
    sa.Column('City', sa.Text(), nullable=True, comment='市'),
    sa.Column('District', sa.Text(), nullable=True, comment='县'),
    sa.Column('Point_id', sa.Integer(), autoincrement=True, nullable=False, comment='唯一编码'),
    sa.PrimaryKeyConstraint('Point_id'),
    comment='点位信息'
    )
    op.create_table('fifty_thousand_survey_data',
    sa.Column('gid', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('id', sa.Float(precision=53), nullable=True),
    sa.Column('dwmc', sa.String(length=254), nullable=True),
    sa.Column('r_dwmc', sa.String(length=254), nullable=True),
    sa.Column('lat', sa.Float(precision=53), nullable=True),
    sa.Column('lon', sa.Float(precision=53), nullable=True),
    sa.Column('xmc', sa.String(length=254), nullable=True),
    sa.Column('zmc', sa.String(length=254), nullable=True),
    sa.Column('cmc', sa.String(length=254), nullable=True),
    sa.Column('tql_cd', sa.Float(precision=53), nullable=True),
    sa.Column('tql_as', sa.Float(precision=53), nullable=True),
    sa.Column('tql_pb', sa.Float(precision=53), nullable=True),
    sa.Column('ph', sa.Float(precision=53), nullable=True),
    sa.Column('zzm_cd', sa.Float(precision=53), nullable=True),
    sa.Column('zzm_as', sa.Float(precision=53), nullable=True),
    sa.Column('zzm_pb', sa.Float(precision=53), nullable=True),
    sa.Column('wzm_cd', sa.Float(precision=53), nullable=True),
    sa.Column('wzm_as', sa.Float(precision=53), nullable=True),
    sa.Column('wzm_pb', sa.Float(precision=53), nullable=True),
    sa.Column('tcb_cd', sa.Float(precision=53), nullable=True),
    sa.Column('tcb_as', sa.Float(precision=53), nullable=True),
    sa.Column('tcb_pb', sa.Float(precision=53), nullable=True),
    sa.Column('mcb_cd', sa.Float(precision=53), nullable=True),
    sa.Column('mcb_as', sa.Float(precision=53), nullable=True),
    sa.Column('mcb_pb', sa.Float(precision=53), nullable=True),
    sa.Column('tzs_cd', sa.Float(precision=53), nullable=True),
    sa.Column('tzs_as', sa.Float(precision=53), nullable=True),
    sa.Column('tzs_pb', sa.Float(precision=53), nullable=True),
    sa.Column('z_mzs_cd', sa.Float(precision=53), nullable=True),
    sa.Column('z_mzs_as', sa.Float(precision=53), nullable=True),
    sa.Column('z_mzs_pb', sa.Float(precision=53), nullable=True),
    sa.Column('w_mzs_cd', sa.Float(precision=53), nullable=True),
    sa.Column('w_mzs_as', sa.Float(precision=53), nullable=True),
    sa.Column('w_mzs_pb', sa.Float(precision=53), nullable=True),
    sa.Column('tzs_max', sa.Float(precision=53), nullable=True),
    sa.Column('mzs_max', sa.Float(precision=53), nullable=True),
    sa.Column('s_tfj_max', sa.Float(precision=53), nullable=True),
    sa.Column('s_mfj_max', sa.Float(precision=53), nullable=True),
    sa.Column('h_tfj_max', sa.Float(precision=53), nullable=True),
    sa.Column('h_mfj_max', sa.Float(precision=53), nullable=True),
    sa.Column('s_pdtj', sa.String(length=254), nullable=True),
    sa.Column('h_pdtj', sa.String(length=254), nullable=True),
    sa.Column('s_xtfx', sa.String(length=254), nullable=True),
    sa.Column('h_xtfx', sa.String(length=254), nullable=True),
    sa.Column('mzs_cd_max', sa.Float(precision=53), nullable=True),
    sa.Column('mzs_as_max', sa.Float(precision=53), nullable=True),
    sa.Column('mzs_pd_max', sa.Float(precision=53), nullable=True),
    sa.Column('tzs_cd_fj', sa.Float(precision=53), nullable=True),
    sa.Column('tzs_as_fj', sa.Float(precision=53), nullable=True),
    sa.Column('tzs_pb_fj', sa.Float(precision=53), nullable=True),
    sa.Column('mzs_cd_fj', sa.Float(precision=53), nullable=True),
    sa.Column('mzs_as_fj', sa.Float(precision=53), nullable=True),
    sa.Column('mzs_pb_fj', sa.Float(precision=53), nullable=True),
    sa.Column('s_cd_pdtj', sa.String(length=254), nullable=True),
    sa.Column('s_cd_pdtj1', sa.String(length=254), nullable=True),
    sa.Column('s_pb_pdtj', sa.String(length=254), nullable=True),
    sa.Column('s_cd_xtfx', sa.String(length=254), nullable=True),
    sa.Column('s_as_xtfx', sa.String(length=254), nullable=True),
    sa.Column('s_pb_xtfx', sa.String(length=254), nullable=True),
    sa.Column('s_cd_fz', sa.Float(precision=53), nullable=True),
    sa.Column('s_as_fz', sa.Float(precision=53), nullable=True),
    sa.Column('cd_as_pdtj', sa.String(length=254), nullable=True),
    sa.Column('cd_as_fhwr', sa.String(length=254), nullable=True),
    sa.Column('wrlx', sa.String(length=254), nullable=True),
    sa.Column('wrlx_pb', sa.String(length=254), nullable=True),
    sa.Column('cec__cmol_', sa.Float(precision=53), nullable=True),
    sa.Column('tom_g_kg_', sa.Float(precision=53), nullable=True),
    sa.Column('mmax_cd', sa.Float(precision=53), nullable=True),
    sa.Column('mmax_as', sa.Float(precision=53), nullable=True),
    sa.Column('mmax_pb', sa.Float(precision=53), nullable=True),
    sa.Column('bcf_cd', sa.Float(precision=53), nullable=True),
    sa.Column('bcf_as', sa.Float(precision=53), nullable=True),
    sa.Column('bcf_pb', sa.Float(precision=53), nullable=True),
    sa.Column('nl_g_kg_', sa.Float(precision=53), nullable=True),
    sa.Column('fl_g_kg_', sa.Float(precision=53), nullable=True),
    sa.Column('sl_g_kg_', sa.Float(precision=53), nullable=True),
    sa.Column('nl_zb', sa.Float(precision=53), nullable=True),
    sa.Column('fl_zb', sa.Float(precision=53), nullable=True),
    sa.Column('sl_zb', sa.Float(precision=53), nullable=True),
    sa.Column('trlx', sa.String(length=254), nullable=True),
    sa.Column('dtpa_cd', sa.Float(precision=53), nullable=True),
    sa.Column('lin_suan_er_qing_an', sa.Float(precision=53), nullable=True),
    sa.Column('dtpa_pb', sa.Float(precision=53), nullable=True),
    sa.Column('zb_cd', sa.Float(precision=53), nullable=True),
    sa.Column('zb_as', sa.Float(precision=53), nullable=True),
    sa.Column('zb_pb', sa.Float(precision=53), nullable=True),
    sa.Column('tql_cr', sa.Float(precision=53), nullable=True),
    sa.Column('tql_hg', sa.Float(precision=53), nullable=True),
    sa.Column('f_2_00mm', sa.String(length=254), nullable=True),
    sa.Column('2_1mm', sa.Float(precision=53), nullable=True),
    sa.Column('1_0_5mm', sa.Float(precision=53), nullable=True),
    sa.Column('0_5_0_25mm', sa.Float(precision=53), nullable=True),
    sa.Column('0_25_0_05m', sa.Float(precision=53), nullable=True),
    sa.Column('0_05_0_02m', sa.Float(precision=53), nullable=True),
    sa.Column('0_02_0_002', sa.Float(precision=53), nullable=True),
    sa.Column('f_0_002mm', sa.Float(precision=53), nullable=True),
    sa.Column('ph_fj', sa.Float(precision=53), nullable=True),
    sa.Column('ph_fj_2', sa.Float(precision=53), nullable=True),
    sa.Column('geom', geoalchemy2.types.Geometry(geometry_type='POINT', from_text='ST_GeomFromEWKT', name='geometry'), nullable=True),
    sa.PrimaryKeyConstraint('gid')
    )
    # 注释掉索引创建
    # op.create_index('idx_fifty_thousand_survey_data_geom', 'fifty_thousand_survey_data', ['geom'], unique=False, postgresql_using='gist')
    # op.create_index(op.f('ix_fifty_thousand_survey_data_geom'), 'fifty_thousand_survey_data', ['geom'], unique=False)
    op.create_table('raster_table',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('rast', geoalchemy2.types.Raster(from_text='raster', name='raster'), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    # op.create_index(op.f('ix_raster_table_rast'), 'raster_table', ['rast'], unique=False)
    op.create_table('surveydata',
    sa.Column('id', sa.Float(precision=53), autoincrement=True, nullable=False),
    sa.Column('dwmc', sa.String(length=50), nullable=True),
    sa.Column('r_dwmc', sa.String(length=50), nullable=True),
    sa.Column('lat', sa.Numeric(precision=9, scale=6), nullable=True),
    sa.Column('lon', sa.Numeric(precision=9, scale=6), nullable=True),
    sa.Column('xmc', sa.String(length=50), nullable=True),
    sa.Column('zmc', sa.String(length=50), nullable=True),
    sa.Column('cmc', sa.String(length=50), nullable=True),
    sa.Column('tql_cd', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('tql_as', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('tql_pb', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('ph', sa.Numeric(precision=4, scale=2), nullable=True),
    sa.Column('zzm_cd', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('zzm_as', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('zzm_pb', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('wzm_cd', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('wzm_as', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('wzm_pb', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('tcb_cd', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('tcb_as', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('tcb_pb', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('mcb_cd', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('mcb_as', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('mcb_pb', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('tzs_cd', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('tzs_as', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('tzs_pb', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('z_mzs_cd', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('z_mzs_as', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('z_mzs_pb', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('w_mzs_cd', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('w_mzs_as', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('w_mzs_pb', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('tzs_max', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('mzs_max', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('s_tfj_max', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('s_mfj_max', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('h_tfj_max', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('h_mfj_max', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('s_pdtj', sa.String(length=10), nullable=True),
    sa.Column('h_pdtj', sa.String(length=10), nullable=True),
    sa.Column('s_xtfx', sa.String(length=50), nullable=True),
    sa.Column('h_xtfx', sa.String(length=50), nullable=True),
    sa.Column('cec_cmol', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('tom_g_kg', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('trlx', sa.String(length=50), nullable=True),
    sa.Column('dtpa_cd', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('dtpa_pb', sa.Numeric(precision=10, scale=6), nullable=True),
    sa.Column('geom', geoalchemy2.types.Geometry(geometry_type='POINT', srid=4326, from_text='ST_GeomFromEWKT', name='geometry'), nullable=True),
    sa.Column('mzs_cd_max', sa.Float(precision=53), nullable=True),
    sa.Column('mzs_as_max', sa.Float(precision=53), nullable=True),
    sa.Column('mzs_pd_max', sa.Float(precision=53), nullable=True),
    sa.Column('tzs_cd_fj', sa.Float(precision=53), nullable=True),
    sa.Column('tzs_as_fj', sa.Float(precision=53), nullable=True),
    sa.Column('tzs_pb_fj', sa.Float(precision=53), nullable=True),
    sa.Column('mzs_cd_fj', sa.Float(precision=53), nullable=True),
    sa.Column('mzs_as_fj', sa.Float(precision=53), nullable=True),
    sa.Column('mzs_pb_fj', sa.Float(precision=53), nullable=True),
    sa.Column('s_cd_pdtj', sa.String(), nullable=True),
    sa.Column('s_cd_pdtj1', sa.String(), nullable=True),
    sa.Column('s_pb_pdtj', sa.String(), nullable=True),
    sa.Column('s_cd_xtfx', sa.String(), nullable=True),
    sa.Column('s_as_xtfx', sa.String(), nullable=True),
    sa.Column('s_pb_xtfx', sa.String(), nullable=True),
    sa.Column('s_cd_fz', sa.Float(precision=53), nullable=True),
    sa.Column('s_as_fz', sa.Float(precision=53), nullable=True),
    sa.Column('cd_as_pdtj', sa.String(), nullable=True),
    sa.Column('cd_as_fhwr', sa.String(), nullable=True),
    sa.Column('wrlx', sa.String(), nullable=True),
    sa.Column('wrlx_pb', sa.String(), nullable=True),
    sa.Column('cec__cmol_', sa.Float(precision=53), nullable=True),
    sa.Column('tom_g_kg_', sa.Float(precision=53), nullable=True),
    sa.Column('mmax_cd', sa.Float(precision=53), nullable=True),
    sa.Column('mmax_as', sa.Float(precision=53), nullable=True),
    sa.Column('mmax_pb', sa.Float(precision=53), nullable=True),
    sa.Column('bcf_cd', sa.Float(precision=53), nullable=True),
    sa.Column('bcf_as', sa.Float(precision=53), nullable=True),
    sa.Column('bcf_pb', sa.Float(precision=53), nullable=True),
    sa.Column('nl_g_kg_', sa.Float(precision=53), nullable=True),
    sa.Column('fl_g_kg_', sa.Float(precision=53), nullable=True),
    sa.Column('sl_g_kg_', sa.Float(precision=53), nullable=True),
    sa.Column('nl_zb', sa.Float(precision=53), nullable=True),
    sa.Column('fl_zb', sa.Float(precision=53), nullable=True),
    sa.Column('sl_zb', sa.Float(precision=53), nullable=True),
    sa.Column('lin_suan_er_qing_an', sa.Float(precision=53), nullable=True),
    sa.Column('zb_cd', sa.Float(precision=53), nullable=True),
    sa.Column('zb_as', sa.Float(precision=53), nullable=True),
    sa.Column('zb_pb', sa.Float(precision=53), nullable=True),
    sa.Column('tql_cr', sa.Float(precision=53), nullable=True),
    sa.Column('tql_hg', sa.Float(precision=53), nullable=True),
    sa.Column('f_2_00mm', sa.String(), nullable=True),
    sa.Column('2_1mm', sa.Float(precision=53), nullable=True),
    sa.Column('1_0_5mm', sa.Float(precision=53), nullable=True),
    sa.Column('0_5_0_25mm', sa.Float(precision=53), nullable=True),
    sa.Column('0_25_0_05m', sa.Float(precision=53), nullable=True),
    sa.Column('0_05_0_02m', sa.Float(precision=53), nullable=True),
    sa.Column('0_02_0_002', sa.Float(precision=53), nullable=True),
    sa.Column('f_0_002mm', sa.Float(precision=53), nullable=True),
    sa.Column('ph_fj', sa.Float(precision=53), nullable=True),
    sa.Column('ph_fj_2', sa.Float(precision=53), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    # op.create_index('idx_surveydata_geom', 'surveydata', ['geom'], unique=False, postgresql_using='gist')
    # op.create_index(op.f('ix_surveydata_geom'), 'surveydata', ['geom'], unique=False)
    op.create_table('unit_ceil',
    sa.Column('gid', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('OBJECTID', sa.Float(precision=53), nullable=True),
    sa.Column('BSM', sa.String(length=20), nullable=True),
    sa.Column('PXZQDM', sa.String(length=2), nullable=True),
    sa.Column('PXZQMC', sa.String(length=50), nullable=True),
    sa.Column('CXZQDM', sa.String(length=4), nullable=True),
    sa.Column('CXZQMC', sa.String(length=50), nullable=True),
    sa.Column('SUM_NYDTBM', sa.Numeric(), nullable=True),
    sa.Column('XCDYBM', sa.String(length=20), nullable=True),
    sa.Column('Shape_Leng', sa.Numeric(), nullable=True),
    sa.Column('Shape_Area', sa.Numeric(), nullable=True),
    sa.Column('geom', geoalchemy2.types.Geometry(geometry_type='GEOMETRYZM', srid=4490, dimension=4, from_text='ST_GeomFromEWKT', name='geometry'), nullable=True),
    sa.PrimaryKeyConstraint('gid')
    )
    # op.create_index('idx_unit_ceil_geom', 'unit_ceil', ['geom'], unique=False, postgresql_using='gist')
    # op.create_index(op.f('ix_unit_ceil_geom'), 'unit_ceil', ['geom'], unique=False)
    # ### end Alembic commands ###


def downgrade():
    """将数据库降级到上一版本"""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_unit_ceil_geom'), table_name='unit_ceil')
    op.drop_index('idx_unit_ceil_geom', table_name='unit_ceil', postgresql_using='gist')
    op.drop_table('unit_ceil')
    op.drop_index(op.f('ix_surveydata_geom'), table_name='surveydata')
    op.drop_index('idx_surveydata_geom', table_name='surveydata', postgresql_using='gist')
    op.drop_table('surveydata')
    op.drop_index(op.f('ix_raster_table_rast'), table_name='raster_table')
    op.drop_table('raster_table')
    op.drop_index(op.f('ix_fifty_thousand_survey_data_geom'), table_name='fifty_thousand_survey_data')
    op.drop_index('idx_fifty_thousand_survey_data_geom', table_name='fifty_thousand_survey_data', postgresql_using='gist')
    op.drop_table('fifty_thousand_survey_data')
    op.drop_table('Point_information')
    op.drop_table('Conventional_land_information')
    # ### end Alembic commands ###