Last active
August 20, 2023 04:58
-
-
Save rma92/16216ba75de05901ac5e3ebc5b234205 to your computer and use it in GitHub Desktop.
Florida Parcel data preprocessing in Spatialite
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--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'); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment