|
All extracted files are ASCII TAB (hex 09) delimited files. To import these files into your database and use them successfully, you will need to understand the relationships between them. Currently, there are 3 files. These are:
We are including a short data dictionary giving you a brief description of each file and their respective fields. To view the actual table definitions, see our sample Table Creation and Import Scripts This is the public version of our Real Estate Master File, combined with the Real Estate Legal File. The file contains property address, ownership, current and previous assessment data including exempt codes, most recent sale, and record status and classification. Also, it contains the complete legal description for each parcel. The property assessment information displayed on our intranet is derived from the elements similar to those contained in this file. |
| Remast Table Field Definitions | ||
|---|---|---|
| 1 | rm_taxkey | Ten character taxkey that is unique to each assessment record |
| 2 | rm_chkdgt | Check digit associated with rm_taxkey. This field is still required for older mainframe systems |
| 3 | rm_county_code | Specifies in which county the parcel is located |
| 4 | rm_record_status | Specifies if the record is Active, Pending Add, Pending Drop, or Dropped. Pending adds and drops depend on the completion of an internal combination and division package. Note, check the rm_divorg_year and rm_drop_year fields of this record for the year in which the 'pending…' statuses will be resolved. Internal packages can sometimes go through a complicated routing process, taking much time, before the statuses of all records involved in the package are changed from 'pending…' to add and/or dropped statuses. Each year, all records with a dropped status are removed from our Real Estate Master File. This is usually done late in December . We keep a history file of all taxkeys involved in any combination and division package, and that file will be available on request. |
| 5 | rm_nbhd | Neighborhood |
| 6 | rm_plat_page | GIS plat page |
| 7 | rm_house_lo | Specifies the property house number(low) |
| 8 | rm_house_hi | Specifies the property house number(high) |
| 9 | rm_house_sfx | Specifies the property house suffix (i.e. 2753A) |
| 10 | rm_street_dir | Specifies street direction |
| 11 | rm_street_name | Specifies the street name |
| 12 | rm_street_type | Specifies the street type ( Avenue=AV, Blvd=BL etc) |
| 13 | rm_house_unit_nr | Applies to condominium units. Specifies the unit nr when it exists. |
| 14 | rm_ownername_line1 | Specifies the first line of the owner name |
| 15 | rm_ownername_line2 | Specifies the second line of the owner name, if it exists |
| 16 | rm_ownername_line3 | Specifies the third line of the owner name, if it exists |
| 17 | rm_owner_street_addr | Specifies the street address (number dir and name)of the owner |
| 18 | rm_owner_city_state | Specifies the city and state portion of the owner address |
| 19 | rm_owner_zip | Specifies the zipcode portion of the owner address |
| 20 | rm_current_class | Specifies the property class (Residential, Mercantile, Condominium, Exempt , Apartments, Omitted, Special Mercantile, Manfucturing ) |
| 21 | rm_current_sym | Unfinished - 'U' or Doomaged = 'L' or nothing |
| 22 | rm_current_land | Current Land assessment |
| 23 | rm_current_imprv | Current Improvements assessment |
| 24 | rm_current_total | Current Land assessment + Current Improvements assessment |
| 25 | rm_current_exm_type | Exempt code (partial or wholly exempt) |
| 26 | rm_current_exm_land | Exempt portion of land assessment |
| 27 | rm_current_exm_imprv | Exempt portion of land assessment |
| 28 | rm_current_exm_total | Exmept current Land assessment + Exempt Current Improvements assessment |
| 29 | rm_previous_class | Each year on the 1st of January, all 'Current' assessment value fields (above) are copied to their corresponding 'Previous' assessment value fields (left) |
| 30 | rm_previous_sym | |
| 31 | rm_previous_land | |
| 32 | rm_previous_imprv | |
| 33 | rm_previous_total | |
| 34 | rm_previous_exm_type | |
| 35 | rm_previous_exm_land | |
| 36 | rm_previous_exm_imprv | |
| 37 | rm_previous_exm_total | |
| 38 | rm_name_change_date | Specifies the date that ANY part of the owner name / address information was changed by our online system. This does not necessarily indicate a sale! |
| 39 | rm_asmt_change_date | Specifies the date that ANY of rm_current_land, rm_current_imprv, or rm_current_total changed either from online posting due to abbas or objections, or batch posting due to revaluation. |
| 40 | rm_asmt_change_rsn | Specifies an internal department assessment change code |
| 41 | rm_convey_date | Specifies the last conveyance date for the property |
| 42 | rm_convey_type | Specifies the last conveyance type for the property |
| 43 | rm_convey_fee | Specifies the last conveyance fee for the property |
| 44 | rm_divorg_year | Specifies the year in which the property was first listed. References are accurate for the last four years. Prior to that, data was derived from the old mainframe 'Comdiv' file. |
| 45 | rm_divorg_seq | Specifies a uniques package number within rm_divorg_year. See the notes associated with rem_drop_seq |
| 46 | rm_drop_year | Specifies the year in which the property will be deleted from our Real Estate Master File |
| 47 | rm_drop_seq | Specifies a unique sequence number within rm_drop_year. Note that each
combination and division package usually contains both new key(s) and
key(s) to be dropped. For example, if package 2000, 190 is a division
having 2 new keys AA and BB that are derived from key ZZ, then:
|
| 48 | rm_zoning | Specifies the zoning associated with the parcel. We are not responsible for maintaining this data. If you don't think it's correct, contact DCD or GIS |
| 49 | rm_aldrm_district | Specifies the aldermanic district associated with the parcel. We are not responsible for maintaining this data. If you don't think it's correct, contact GIS |
| 50 | rm_landuse | Specifies the landuse code associated with the parcel. We are not responsible for maintaining this data |
| 51 | rm_historic_code | Specifies the historic code associated with the parcel. We are not responsible for maintaining this data |
| 52 | rm_arg_system | Specifies where to find the inventory attribute data associated with this class of property |
| 53 | rm_legal_desc | Specifies the complete legal description of the parcel |
|
This is a more complicated file. It consolidates property inventory information. This information originates from disparate assessment modeling data, residing in various attribute tables within our database. Since the assessment valuation models are different for various classifications of property, the attribute data stored for each property class is not the same. We've included all the elements for public use and translated all the internal Assessor codes to text for your convenience. The file is currently divided into four sections:
|
| Inventory_Attributes Table Field Definitions | ||
|---|---|---|
| 1 | taxkey | Parcel ten character taxkey |
| 2 | dwelling_nr | Specifies the listing card associated with the improvement. Sometimes a parcel can have multiple buildings associated with it. These are listed in separate records |
| 3 | rvs_indicator | Residential: Specifies whether the card contains improvements or just vacant land |
| 4 | rvs_living_units | Residential: Number of separate living unitsassociated with a listing. Usually it contains the number 1, except for entities like Duplexes, Townhouses, or Apartments |
| 5 | rvs_story_height | Residential: Story height |
| 6 | rvs_style | Residential: Description of building style |
| 7 | rvs_extwall | Residential: Description of exterior wall type |
| 8 | rvs_aircond | Residential: Yes or no for central air conditioning |
| 9 | rvs_recroom | Residential: Yes or no for existence of a rec. room |
| 10 | rvs_bsmt_type | Residential: Description of basement; Full, Partial, Crawl etc. |
| 11 | rvs_system_type | Residential: Description of heating system |
| 12 | rvs_fin_bsmt_sqft | Residential: Finished sqft in basement |
| 13 | rvs_unf_bsmt_sqft | Residential: Unfinished sqft in basement. Note you must add the two fields to get the basement footprint |
| 14 | rvs_fin_1stfl_sqft | Residential: Finished sqft on 1st floor |
| 15 | rvs_fin_2ndfl_sqft | Residential: Finished sqft on 2nd floor |
| 16 | rvs_fin_3rdfl_sqft | Residential: Finished sqft on 3rd floor |
| 17 | rvs_fin_attic_sqft | Residential: Finished attic sqft |
| 18 | rvs_fin_hstory_sqft | Residential: Finished half story sqft |
| 19 | rvs_year_built | Residential: Year built |
| 20 | rvs_full_baths | Residential: Number of full baths |
| 21 | rvs_half_baths | Residential: Number of half baths (powder rooms) |
| 22 | rvs_bed_rooms | Residential: Number of bed rooms |
| 23 | rvs_family_room | Residential: Number of family rooms |
| 24 | rvs_builtin_fireplaces | Residential: Number of built in fire places |
| 25 | rvs_total_fin_sqft | Residential: Total finished sqft |
| 26 | rvs_total_unf_sqft | Residential: Total unfinished sqft |
| 27 | rvs_attached_garage_sqft | Residential: Specifies the total sqft associated with an attached garage (or attached garages). This field is somewhat redundant, since the detailed garage information is provided below. |
| 28 | rvs_detached_garage_sqft | Residential: Specifies the total sqft associated with an detached garage (or detached garages). This field is somewhat redundant, since the detailed garage information is provided below |
| 29 | rvs_land_primary_site_sqft | Residential: Lists the sqft of the primary lot containing the house. Text descriptions of all lot entries (in dimensions) can be found below. This field is provided incase you want to do a search on sqft. The total sqft associated with a parcel will be the sum of this field and rvs_land_excess_sqft. |
| 30 | rvs_land_excess_sqft | Residential: Lists any excess land sqft associated with the parcel. Text descriptions of all lot entries (in dimensions) can be found below. This field is provided incase you want to do a search on sqft. The total sqft associated with a parcel will be the sum of this field and rvs_land_primary_site_sqft. |
| 31 | rvs_land_xs_flag_A | Residential: Specifies if this lot entry is for the primary site or is excess land |
| 32 | rvs_land_dimension_type_A | Residential: Specifies the dimension associated with the scaler. It can be LW FF SF or AC for length width, front foot, sqft or acres |
| 33 | rvs_land_scaler_1_A | Residential: Length, width, sqft or acres depending on rvs_land_dimension_type |
| 34 | rvs_land_scaler_2_A | Residential: Length or width |
| 35 | rvs_land_text_desc_A | Residential: Text description of the above four fields |
| 36 | rvs_land_xs_flag_B | There can be two detailed lot entries per parcel. The five fields on the left have the same definitions as the corresponding fields above |
| 37 | rvs_land_dimension_type_B | |
| 38 | rvs_land_scaler_1_B | |
| 39 | rvs_land_scaler_2_B | |
| 40 | rvs_land_text_desc_B | |
| 41 | rvs_oby_text_desc_A | Residential: Text description of outside yard and building entry. These are such things as garages, greenhouses, swimming pools etc. We've included up to four of these entries. (A,B,C,D) |
| 42 | rvs_oby_text_desc_B | Same as above |
| 43 | rvs_oby_text_desc_C | Same as above |
| 44 | rvs_oby_text_desc_D | Same as above |
| 45 | rvs_oim_text_desc_A | Residential: Text description of miscellaneous improvements. These are such things as carports or sometimes large unfinished improvements. We've included up to two of these (A,B) |
| 46 | rvs_oim_text_desc_B | Same as above |
| 47 | rvs_endof_record | Residential: End of records indicator denoted by (ervs) |
| 48 | cvs_tot_stories | Commercial: Total stories for this building |
| 49 | cvs_exterior_wall | Commercial: description of exterior wall |
| 50 | cvs_total_floor_sqft | Commercial: Total floor area listed in building in sqft |
| 51 | cvs_groundfloor_sqft | Commercial: Street level area in sqft |
| 52 | cvs_total_units | Commercial: Total number of unique sections: Depending on the field cvs_use_desc, this could indicate retail stores |
| 53 | cvs_use_desc | Commercial: The general use description for the building |
| 54 | cvs_lot_sqft | Commercial: Listing a portion of the total lot size in sqft. Dwelling_nr must equal 1 |
| 55 | cvs_lot_acres | Commercial: Listing a portion of the total lot size in acres. Dwellng_nr must equal 1 |
| 56 | cvs_lot_front | Commercial: Listing of front feet. Dwelling_nr must equal 1 |
| 57 | cvs_lot_depth | Commercial: Listing of depth feet. Dwelling_nr must equal 1 |
| 58 | cvs_lot_excess_land | Commercial: Listing a portion of the total lot size as excess land. Dwelling_nr must equal 1 |
| 59 | cvs_total_sqft | Commercial: This field contains the total lot size in sqft for the parcel. If there are multiple buidings on the parcel, then the total lot size will be associated with the first listed building (i.e. dwelling_nr = 1) |
| 60 | cvs_endof_record | Commercial: End of record section indicator denoted by (ecvs) |
| 61 | con_proj_name - | Condominium: Specifies the name of the condominium complex |
| 62 | con_proj_lotsize | Condominium: Specifies the lot size of the condominium complex |
| 63 | con_proj_style | Condominium: Specifies the general style of the units within the condominium complex |
| 64 | con_proj_total_units | Condominium: Specifies the total number of units within the condominium complex |
| 65 | con_proj_pool | Condominium: Specifies if the condominium complex has a pool |
| 66 | con_proj_tennis | Condominium: Specifies if the condominium complex has tennis courts |
| 67 | con_proj_clubhouse | Condominium: Specifies if the condominium complex has a clubhouse |
| 68 | con_unit_year_built | Condominium: Specifies the year built of the unit |
| 69 | con_unit_story_height | Condominium: Specifies the story height of the unit |
| 70 | con_unit_basement | Condominium: Specifies if the unit has a basement |
| 71 | con_unit_patio | Condominium: Specifies if the unit has a patio |
| 72 | con_unit_attic | Condominium: Specifies if the unit has an attic |
| 73 | con_unit_size_sqft | Condominium: Specifies the total sqft living area of the unit |
| 74 | con_unit_bed_rooms | Condominium: Specifies the number of bedrooms in the unit |
| 75 | con_unit_full_baths | Condominium: Specifies the number of bathrooms in the unit |
| 76 | con_unit_half_baths | Condominium: Specifies the number of half bathrooms in the unit |
| 77 | con_unit_aircond | Condominium: Specifies it the unit has central air conditioning |
| 78 | con_endof_record | Condominium: End of record section indicator denoted by (econ) |
|
This file contains the commercial sections associated with a commercial building. All internal Assessor commercial use codes have been translated to use descriptions for your convenience. |
| Cmrcl_Sections Table Field Definitions | ||
|---|---|---|
| 1 | sec_taxkey | Specifies the taxkey associated with the parcel containing a buliding that has commercial sections |
| 2 | sec_dwelling_nr | Specifies which swelling_nr on the parcel has these commercial section associated with it |
| 3 | sec_nr | Specifies the section number of this commercial section |
| 4 | sec_identical_units | Specifies how many units of similar use type, income and floor area are associated with this building (usually equals 1) |
| 5 | sec_unit_nr | Specifies the unit number (suite number) of the commercial section. This field is not a mandatory entry field in our evaluation system |
| 6 | sec_floor_level | Specifies the floor level of the building on which this section is located. This field is not a mandatory entry field in our evaluation system |
| 7 | sec_use_desc | Specifies the use description of this commercial section |
| 8 | sec_gross_area | Specifies the floor area of the building being used by this commercial section |
| 9 | sec_misc | Specifies any miscellaneous data associated with this commercial section. This char(50) field is for our internal use |
| 10 | sec_mktrent_sqft | Specifies the market rent per sqft for this section. |
SQL Table DescriptionsUse this SQL if this is the first time you've downloaded our files. We create the DB table definitions for you and execute the import of the data. Depending on your database: Sybase, Oracle, MsSQL, or Access, you may need to make slight modifications to the 'INPUT' syntax. Regardless of your database, change the file names that are being used in the 'INPUT' statements to the names of the data files you downloaded from our directory. For example, the Remast.txt 'INPUT' statement currently specifies REMAST_2000-10-21.TXT; you would change the name of that file to match the one you downloaded. |
// Table definition for Public View Of Remast
CREATE table remast_copy (
rm_taxkey char(10),
rm_chkdgt char(01),
rm_county_code char(20),
rm_record_status char(15),
rm_nbhd char(04),
rm_plat_page integer,
rm_house_lo integer,
rm_house_hi integer,
rm_house_sfx char(03),
rm_street_dir char(01),
rm_street_name char(20),
rm_street_type char(02),
rm_house_unit_nr char(06),
rm_ownername_line1 char(28),
rm_ownername_line2 char(28),
rm_ownername_line3 char(28),
rm_owner_street_addr char(28),
rm_owner_city_state char(28),
rm_owner_zip char(09),
rm_curr_class char(20),
rm_current_sym char(01),
rm_current_land integer,
rm_current_imprv integer,
rm_current_total integer,
rm_current_exm_type char(03),
rm_current_exm_land integer,
rm_current_exm_imprv integer,
rm_curr_exm_total integer,
rm_previous_class char(20),
rm_previous_sym char(01),
rm_previous_land integer,
rm_previous_imprv integer,
rm_previous_total integer,
rm_previous_exm_type char(03),
rm_previous_exm_land integer,
rm_previous_exm_imprv integer,
rm_previous_exm_total integer,
rm_name_change_date date,
rm_asmt_change_date date,
rm_asmt_change_rsn char(03),
rm_convey_date date,
rm_convey_type char(02),
rm_convey_fee numeric(9,2),
rm_divorg_year smallint,
rm_divorg_seq smallint,
rm_drop_year smallint,
rm_drop_seq smallint,
rm_zoning char(07),
rm_aldrm_district smallint,
rm_landuse char(07),
rm_historic_code char(07),
rm_attribute_system char(03),
rm_legal_desc char(720) ,
Primary Key (rm_taxkey));
input into remast_copy from d:\remast_2000-10-19.txt
format ascii delimited by '\x09';
commit;
/*
We've found the following indexes to be very useful. If you want to use them, remove
the following sql code from this comment area.
create index addedrecords on remast ( rm_divorg_year, rm_divorg_seq);
create index droppedrecords on remast ( rm_drop_year, rm_drop_seq);
create index basic_addr_lkp on remast ( rm_street_name, rm_street_dir, rm_house_lo);
*/
// Table definition for public view of Inventory_Attributes
CREATE Table Inventory_Attributes (
taxkey char(10),
dwelling_nr smallint,
rvs_indicator char(12),
rvs_living_units smallint,
rvs_story_height numeric (5,1),
rvs_style char(20),
rvs_extwall char(20),
rvs_aircond char(03),
rvs_recroom char(03),
rvs_bsmt_type char(30),
rvs_system_type char(15),
rvs_fin_bsmt_sqft integer,
rvs_unf_bsmt_sqft integer,
rvs_fin_1stfl_sqft integer,
rvs_fin_2ndfl_sqft integer,
rvs_fin_3rdfl_sqft integer,
rvs_fin_attic_sqft integer,
rvs_fin_hstory_sqft integer,
rvs_year_built smallint,
rvs_full_baths smallint,
rvs_half_baths smallint,
rvs_bed_rooms smallint,
rvs_family_room smallint,
rvs_builtin_fireplaces smallint,
rvs_total_fin_sqft integer,
rvs_total_unf_sqft integer,
rvs_attached_garage_sqft integer,
rvs_detached_garage_sqft integer,
rvs_land_primary_site_sqft integer,
rvs_land_excess_sqft integer,
rvs_land_xs_flag_A char(01),
rvs_land_dimension_type_A char(02),
rvs_land_scaler_1_A numeric(13,2),
rvs_land_scaler_2_A numeric(13,2),
rvs_land_text_desc_A char(45),
rvs_land_xs_flag_B char(01),
rvs_land_dimension_type_B char(02),
rvs_land_scaler_1_B numeric(13,2),
rvs_land_scaler_2_B numeric(13,2),
rvs_land_text_desc_B char(45),
rvs_oby_text_desc_A char(45),
rvs_oby_text_desc_B char(45),
rvs_oby_text_desc_C char(45),
rvs_oby_text_desc_D char(45),
rvs_oim_text_desc_A char(50),
rvs_oim_text_desc_B char(50),
rvs_endof_record char(04),
cvs_total_stories smallint,
cvs_extwall char(20),
cvs_total_floor_sqft integer,
cvs_groundfloor_sqft integer,
cvs_total_units smallint,
cvs_use_desc char(60),
cvs_lot_sqft integer,
cvs_lot_acres numeric(13,2),
cvs_lot_front integer,
cvs_lot_depth integer,
cvs_lot_excess numeric(13,2),
cvs_lot_total_sqft integer,
cvs_endof_record char(04),
con_proj_name char(40),
con_proj_lotsize numeric(13,2),
con_proj_style char(30),
con_proj_total_units integer,
con_proj_pool char(03),
con_proj_tennis char(03),
con_proj_clubhouse char(03),
con_unit_year_built smallint,
con_unit_story_height numeric(5,1),
con_unit_basement char(03),
con_unit_patio char(03),
con_unit_attic char(03),
con_unit_size_sqft integer,
con_unit_bed_rooms smallint,
con_unit_full_baths smallint,
con_unit_half_baths smallint,
con_unit_aircond char(03),
con_endof_record char(04) ,
Primary Key (taxkey, dwelling_nr));
input into Inventory_Attributes from d:\inventory_attributes_2000-10-19.txt
format ascii delimited by '\x09';
commit;
// Table definition for public view of Cmrcl_sections
CREATE Table Cmrcl_Sections (
taxkey char(10),
dwelling_nr smallint,
sec_nr smallint,
sec_identical_units smallint,
sec_unit_nr char(10),
sec_floor_level char(02),
sec_use_desc char(60),
sec_gross_area integer,
sec_misc char(50),
sec_mktrent numeric(9,2),
Primary Key (taxkey, dwelling_nr, sec_nr));
input into Cmrcl_Sections from d:\Cmrcl_sections_2000-10-19.txt
format ascii delimited by '\x09';
commit;