Database Tables
This page contains information on the database tables in assessingsearch on VSQL01. (NOTE: assessingupdates is a clone of assessingsearch for dev)
Views
This view does not appear to be used by default.asp
or any other procedures.
This view could be deleted from the DB.
Stored Procedures
None of these stored procedures appear to be used by default.asp
or any view or triggers.
These procedures could be deleted from the DB
Database Table Structure
additional_data
This table is accessed from default.asp
.
Column | Source | Notes |
---|---|---|
parcel_id nvarchar(10) | MS Access | PK The unique parcel ID |
living_area int | MS Access | |
gross_area int | MS Access | |
year_built smallint | MS Access | |
year_remodeled smallint | MS Access | |
condo_units_residential smallint | MS Access | |
condo_units_commercial smallint | MS Access | |
condo_units_mixed smallint | MS Access | |
stories decimal(4, 1) | MS Access |
bid
This table is accessed from default.asp
.
Potentially, there is some adjustment of the bid amount depending on billing schedule.
Column | Source | Notes |
---|---|---|
parcel_id nvarchar(10) | PK The unique parcel ID | |
bid_greenway money | MS Access | This is extracted and compiled from the greenway_bid table in the MSAccess database. |
bid_downtown money | MS Access | This is extracted and compiled from the greenway_bid table in the MSAccess database. |
condo_attributes
This table is accessed from default.asp
.
Column | Source | Notes |
---|---|---|
parcel_id nchar(10) | ||
Style nvarchar(20) | ||
Exterior Condition nvarchar(14) | ||
Exterior Wall nvarchar(14) | ||
Grade nvarchar(14) | ||
Stories nvarchar(4) | ||
'#Units' decimal(9, 0) | ||
Street Type nvarchar(50) | ||
Square Feet of Living area decimal(9, 0) | ||
Base Floor nvarchar(4) | ||
Fireplaces nvarchar(4) | ||
'# Floors' nvarchar(4) | ||
Total Rooms nvarchar(4) | ||
Bedrooms nvarchar(4) | ||
Bathrooms nvarchar(4) | ||
Half Bathrooms nvarchar(4) | ||
Bathroom Style1 nvarchar(14) | ||
Bathroom Style2 nvarchar(14) | ||
Bathroom Style3 nvarchar(14) | ||
Kitchen Style nvarchar(14) | ||
Kitchen Type nvarchar(14) | ||
Heat Type nvarchar(14) | ||
Interior Condition nvarchar(14) | ||
Interior Finish nvarchar(14) | ||
Orientation nvarchar(14) | ||
Corner Unit nvarchar(14) | ||
View nvarchar(14) | ||
condo_main nchar(10) | ||
CNS_BID decimal(9, 0) |
current_owners
This table is accessed from default.asp
.
Column | Source | Notes |
---|---|---|
Parcel_id nchar(10) | PK | |
seqno tinyint | PK | |
owner_name nvarchar(255) |
landuse_described
This table is accessed from default.asp
.
Column | Source | Notes |
---|---|---|
id int | ||
Short_Description nvarchar(10) | ||
Description nvarchar(50) |
outbuildings
This table is accessed from default.asp
.
Column | Source | Notes |
---|---|---|
parcel_id nchar(10) | PK | |
line_number tinyint | PK | |
Code nvarchar(50) | ||
Tot Units decimal(9, 2) | ||
Quantity decimal(9, 2) | ||
Quality nvarchar(50) | ||
Condition nvarchar(50) |
Overval_application_numbers
This table does not appear to be used.
parcel
This table is accessed from default.asp
.
Column | Source | Notes |
---|---|---|
parcel_id nchar(10) | PK | |
street_number nvarchar(10) | ||
street_name nvarchar(50) | ||
apartment_no nvarchar(20) | ||
suffix nvarchar(2) | ||
landuse nvarchar(2) | ||
owner nvarchar(255) | ||
condo_main nchar(10) |
parcel_all_owners
This table is accessed from default.asp
.
Column | Source | Notes |
---|---|---|
parcel_id nchar(10) | ||
street_number nvarchar(10) | ||
street_name nvarchar(50) | ||
apartment_no nvarchar(20) | ||
suffix nvarchar(2) | ||
landuse nvarchar(2) | ||
owner nvarchar(255) | ||
condo_main nchar(10) |
propertycodes_described
This table is accessed from default.asp
.
Column | Source | Notes |
---|---|---|
property-code smallint | ||
property-class tinyint | ||
property-class-description nvarchar(255) | ||
property-code-description nvarchar(255) | ||
property-code-state bit | ||
property-code-city bit |
Res_exempt
This table is accessed from default.asp
.
Column | Source | Notes |
---|---|---|
parcel_id nvarchar(10) | MSAccess:Tyler Real Estate Export File | |
residential_exemption nvarchar(255) | MSAccess:Tyler Real Estate Export File | |
personal_exemption bit | MSAccess:Taxes | Defaults to 0 but is 1 if either of |
RESIDENTIAL PROPERTY ATTRIBUTES
This table is accessed from default.asp
.
Column | Source | Notes |
---|---|---|
parcel_id nvarchar(255) | ||
Building Seq float | ||
Composite Land Use nvarchar(255) | ||
Building Style nvarchar(255) | ||
Rooms nvarchar(255) | ||
Bedrooms nvarchar(255) | ||
Full Bath float | ||
Half Bath float | ||
Other Fixtures float | ||
Bath Style 1 nvarchar(255) | ||
Bath Style 2 nvarchar(255) | ||
Bath Style 3 nvarchar(255) | ||
Kitchens float | ||
Kitchen Type nvarchar(255) | ||
Kitchen Style 1 nvarchar(255) | ||
Kitchen Style 2 nvarchar(255) | ||
Kitchen Style 3 nvarchar(255) | ||
Fireplaces float | ||
AC Type nvarchar(255) | ||
Heat Type nvarchar(255) | ||
Interior Condition nvarchar(255) | ||
Interior Finish nvarchar(255) | ||
View nvarchar(255) | ||
Grade nvarchar(255) | ||
"# of Parking Spots" nvarchar(255) | ||
Year Built float | ||
Story Height nvarchar(255) | ||
Roof Cover nvarchar(255) | ||
Roof Structure nvarchar(255) | ||
Exterior Finish nvarchar(255) | ||
Exterior Condition nvarchar(255) | ||
Foundation nvarchar(255) |
tax_preliminary
This table is accessed from default.asp
.
Column | Source | Notes |
---|---|---|
parcel_id nchar(10) | ||
Bill Year smallint | ||
Bill Number int | ||
RE Tax Amt decimal(12, 2) | ||
CPA Amt decimal(12, 2) | ||
Downtown BID Amt decimal(12, 2) | ||
Greenway BID Amt decimal(12, 2) | ||
Total Billed Amt decimal(12, 2) | ||
taxbill
This table is accessed from default.asp
.
Column | Source | Notes |
---|---|---|
parcel_id nchar(10) | ||
condo_main nchar(10) | ||
ward_precinct_block nvarchar(7) | ||
street_number nvarchar(10) | ||
street_number_suffix nvarchar(10) | ||
street_name nvarchar(50) | ||
street_name_only nvarchar(50) | ||
street_name_suffix nvarchar(2) | ||
apt_unit nvarchar(20) | ||
city nvarchar(50) | ||
location_zip_code nvarchar(5) | ||
location_zip_code_plus_four nvarchar(4) | ||
owner nvarchar(50) | ||
mail_addressee nvarchar(50) | ||
mail_street_address1 nvarchar(50) | ||
mail_street_address2 nvarchar(50) | ||
mail_city_and_state nvarchar(50) | ||
mail_zip_code nvarchar(5) | ||
mail_zip_code_plus_four nvarchar(4) | ||
country nvarchar(50) | ||
future_owner nvarchar(50) | ||
future_mail_addressee nvarchar(50) | ||
future_owner_mail_street_address1 nvarchar(50) | ||
future_owner_mail_street_address2 nvarchar(50) | ||
future_owner_mail_city_and_state nvarchar(50) | ||
future_owner_mail_zip_code nvarchar(5) | ||
future_owner_mail_zip_code_plus_four nvarchar(4) | ||
future_owner_country nvarchar(50) | ||
land_area int | ||
land_use nvarchar(2) | ||
exempt_code nvarchar(4) | ||
property_type nvarchar(4) | ||
state_class_code nvarchar(1) | ||
residential_building_value decimal(12, 2) residential_land_value decimal(12, 2) residential_air_rights_value decimal(12, 2) | ||
commercial_building_value decimal(12, 2) commercial_land_value decimal(12, 2) commercial_air_rights_value decimal(12, 2) | ||
industrial_building_value decimal(12, 2) industrial_land_value decimal(12, 2) industrial_air_rights_value decimal(12, 2) | ||
open_space_land_value decimal(12, 2) | ||
exempt_building_value decimal(12, 2) exempt_land_value decimal(12, 2) | ||
total_building_value decimal(12, 2) total_land_value decimal(12, 2) total_value decimal(12, 2) | ||
latest_sale_date date | ||
latest_bkpgcert nvarchar(10) | ||
residential_exemption_flag bit | ||
coop_value decimal(12, 2) | ||
clause_abatement_type_1 nvarchar(6) clause_abt_1_pct_ownership nvarchar(5) clause_abt_1_pct_occupancy nvarchar(5) clause_abatement_type_2 nvarchar(6) clause_abt_2_pct_ownership nvarchar(5) clause_abt_2_pct_occupancy nvarchar(5) | ||
paraplegic nvarchar(6) | ||
workoff_type nvarchar(6) workoff_credit_amt decimal(12, 2) | ||
sewer_betterment_amt decimal(12, 2) street_betterment_amt decimal(12, 2) sidewalk_betterment_amt decimal(12, 2) | ||
penalty1_type nvarchar(6) penalty1_amt decimal(12, 2) penalty2_type nvarchar(6) penalty2_amt decimal(12, 2) penalty3_type nvarchar(6) penalty3_amt decimal(12, 2) | ||
fine1_type nvarchar(6) fine1_amt decimal(12, 2) fine2_type nvarchar(6) fine2_amt decimal(12, 2) fine3_type nvarchar(6) fine3_amt decimal(12, 2) | ||
BID_type nvarchar(6) BID_amt decimal(12, 2) | ||
nontax_collection_type nvarchar(6) nontax_collection_amt decimal(12, 2) | ||
personal_exemption_flag bit | ||
condo_main_value decimal(12, 2) |
taxes
This table is accessed from default.asp
.
Column | Source | Notes |
---|---|---|
parcel_id nchar(10) | PK | |
gross_tax decimal(12, 2) | ||
net_tax decimal(12, 2) | ||
persexempt_1 decimal(12, 2) | ||
persexempt_2 decimal(12, 2) | ||
persexempt_total decimal(12, 2) | ||
resexempt decimal(12, 2) | ||
cpa decimal(12, 2) | ||
code_enforcement_tax decimal(12, 2) | ||
38D_fine decimal(12, 2) | ||
sidewalk_betterment decimal(12, 2) | ||
street_betterment decimal(12, 2) | ||
bill_number int |
value_history
This table is accessed from default.asp
.
Column | Source | Notes |
---|---|---|
Parcel_id nchar(10) | ||
Fiscal_Year smallint | ||
Assessed_value bigint | ||
Land_use nvarchar(4) |
Updating Database Tables
As noted elsewhere, data for the MS SQL databases are provided by Assessing in MS Access database/s.
The number of rows in the database tables is high (e.g. 150k - 400k records) and the data size of the tables can be high (30 - 500 MB).
This means a strategy for updating the SQL database with the source data needs to accommodate the size of the import, and the time it will take to import.
Request a new
assessingupdates20YYQX
database be created for data import.a. In Q1 this should be a copy of the previous Q3 database. b. in Q3 this can be an empty database.
Extract the schema from Access database and use to create new import tables in the new database.
Export the data from each of the source tables in the MS Access database as a series of INSERT statements.
Manipulate the data from the working tables into the "permanent" tables in
assessingupdates20YYQX
.
In 12/2023 for FY2024 Q3 a script was created (intended to run on a linux workstation or in a WSL2 session on Windows). The constants from line 13-22 in the script need to be chaked and updated as needed.
Assuming the import is being performed on a Linux computer or Windows computer in a WSL2 session, with the /wwwcob/assessing/search
forlder copied/cloned locally - to run the script:
Last updated