Florida Parcel data preprocessing in Spatialite

cleanup.sql

--run `spatialite newdb.db`, fix the attach line to be the parcel data as converted by GDAL.  If you select by zip code, use the commented line to convert the zip code to an integer to speed up searching.
PRAGMA synchronous=OFF;
PRAGMA count_changes=OFF;
PRAGMA journal_mode=OFF;
PRAGMA temp_store=OFF;
attach database 'parcels2022.db' as p;
--update p.parcels_2020 set phyzip = cast(phyzip as integer);

-- Create the new table 'parcels_x'
CREATE TABLE 'parcels_x' (
    "objectid" INTEGER PRIMARY KEY AUTOINCREMENT,
    'cntyname' VARCHAR(15),
    'link' VARCHAR(30),
    'parcelid' INTEGER,
    'nparno' VARCHAR(35),
    'doruc' VARCHAR(3),
    'pauc' VARCHAR(3),
    'parusedesc' VARCHAR(250),
    'spass_cd' VARCHAR(12),
    'improvval' INTEGER,
    'lndval' INTEGER,
    'jv' INTEGER,
    'jv_chng' INTEGER,
    'jv_hmstd' INTEGER,
    'av_sd' INTEGER,
    'av_nsd' INTEGER,
    'av_hmstd' INTEGER,
    'tv_sd' INTEGER,
    'tv_nsd' INTEGER,
    'jv_class_use' INTEGER,
    'oname' VARCHAR(30),
    'oaddr1' VARCHAR(40),
    'oaddr2' VARCHAR(40),
    'ocity' VARCHAR(40),
    'ostate' VARCHAR(25),
    'ozipcd' VARCHAR(5),
    'phyaddr1' VARCHAR(40),
    'phycity' VARCHAR(30),
    'phyzip' INTEGER,
    'po_name' VARCHAR(35),
    'po_zip' INTEGER,
    'slegal' VARCHAR(30),
    'altkey' VARCHAR(20),
    'actyrblt' INTEGER,
    'effyrblt' INTEGER,
    'totlvgarea' INTEGER,
    'nobuldng' INTEGER,
    'noresunts' INTEGER,
    'parsplt' VARCHAR(5),
    'lndsqfoot' FLOAT,
    'constclass' VARCHAR(3),
    'distr_cd' VARCHAR(5),
    'distr_yr' VARCHAR(5),
    'saleprc1' INTEGER,
    'saleyr1' INTEGER,
    'saleprc2' INTEGER,
    'saleyr2' INTEGER,
    'nbrhdcd' VARCHAR(10),
    'publiclnd' VARCHAR(2),
    'taxauthcd' VARCHAR(7),
    'sec' VARCHAR(3),
    'twn' VARCHAR(3),
    'rng' VARCHAR(3),
    'censusbk' VARCHAR(20),
    'sourceage' VARCHAR(40),
    'sourcedate' TIMESTAMP,
    'lat_dd' FLOAT,
    'long_dd' FLOAT,
    'mgrs' VARCHAR(20),
    'googlemap' VARCHAR(100),
    'acres' FLOAT,
    'exmpt' VARCHAR(3),
    'lu_res' VARCHAR(3),
    'lucode' VARCHAR(3),
    'gcid' INTEGER,
    'descript' VARCHAR(50),
    'flag' VARCHAR(5),
    'fgdlaqdate' TIMESTAMP,
    'autoid' INTEGER,
    'shape_length' FLOAT,
    'shape_area' FLOAT,
    'shape' MULTIPOLYGON
);

INSERT INTO parcels_x
SELECT
    objectid,
    cntyname,
    link,
    CAST(parcelid AS INTEGER),
    nparno,
    doruc,
    pauc,
    parusedesc,
    spass_cd,
    CAST(improvval AS INTEGER),
    CAST(lndval AS INTEGER),
    CAST(jv AS INTEGER),
    CAST(jv_chng AS INTEGER),
    CAST(jv_hmstd AS INTEGER),
    CAST(av_sd AS INTEGER),
    CAST(av_nsd AS INTEGER),
    CAST(av_hmstd AS INTEGER),
    CAST(tv_sd AS INTEGER),
    CAST(tv_nsd AS INTEGER),
    CAST(jv_class_use AS INTEGER),
    oname,
    oaddr1,
    oaddr2,
    ocity,
    ostate,
    ozipcd,
    phyaddr1,
    phycity,
    CAST(phyzip AS INTEGER),
    po_name,
    CAST(po_zip AS INTEGER),
    slegal,
    altkey,
    CAST(actyrblt AS INTEGER),
    CAST(effyrblt AS INTEGER),
    totlvgarea,
    nobuldng,
    noresunts,
    parsplt,
    lndsqfoot,
    constclass,
    distr_cd,
    distr_yr,
    CAST(saleprc1 AS INTEGER),
    CAST(saleyr1 AS INTEGER),
    CAST(saleprc2 AS INTEGER),
    CAST(saleyr2 AS INTEGER),
    nbrhdcd,
    publiclnd,
    taxauthcd,
    sec,
    twn,
    rng,
    censusbk,
    sourceage,
    sourcedate,
    lat_dd,
    long_dd,
    mgrs,
    googlemap,
    acres,
    exmpt,
    lu_res,
    lucode,
    gcid,
    descript,
    flag,
    fgdlaqdate,
    autoid,
    shape_length,
    shape_area,
    shape
FROM p.parcels_2020 where phyzip = 33131;
select PROJ_SetDatabasePath('C:\bin\proj\proj.db');
UPDATE parcels_x SET shape = CompressGeometry( SanitizeGeometry( GeomFromTWKB( AsTWKB( Transform(shape, 4326), 5 ), 4326) ) );
select RecoverGeometryColumn('parcels_x', 'shape', 4326, 'GEOMETRY');
SELECT CreateSpatialIndex('parcels_x', 'shape');