All layers are rebuilt using newest parcel boundary layer from Cartographer once a year after certification and then again after BOW, which occur sometime in May/June - the Assessor's office needs to give GIS analyst a 2 week window to go through all processes needed for this major update. Additional changes may be done after BOE adjourns in mid-July (appeals from the public on their tax assessment notices). Dee Dee is able to print a report that just shows the parcel numbers of those parcels that had changes occur during the BOE process. BE21 memos can be pulled for the parcels that had changes to help define what changes need to be made in the attribute table.
Monthly Updates via
Append data process: Run SQL Query "MONTHLY_UPDATE_ALL_TABLES" which pulls data from 3 different tables. Parcel master ownership, deed transfers and all addressing information. Copy all data and headers to Working_Updates_YYYY.xls.
Run formulas to turn all deed transfer values into text format prior to using the data to append the updates to the layer in AGOL.
IMAGES: Copy the images folder located here: Images
and then paste to local drive. Run a renaming script to make all path names lower-case, located here GISStoragRenaming_Images
and then paste images folder in gis.
Run ANNUAL_IMAGEINDEX.sql query in SQL which pulls lrsn, extension, image_path, and image_date from GRM_Main.dbo.v_image_index. Copy all fields with headers and paste for use into Image_Template.xlsx located here: GISStoragPARCEL_MAP_TEMPLATES.
Make all letters lowercase and then use the FORMULAS_Images tab to make a single lrsn/parcel record with multiple fields of all associated images.
ImageYN, Image11Y, Image 21Y, and Image 31Y fields need to be added to Parcels for Reporting layer to be read by GeoCortex. If there is an image associated, then ImageYN is Y, if there are 11 or more sketches associated, then Image11Y is Y, and so on.
ASSESSMENT VALUES
Though Assessment values are also accounted for in the Parcels for Reporting Layer, the Assessment Values layer has many-to-one which is necessary for graphs. Data from TSBv_Cadastre table in GRM Main, fields needed are PIN, TaxYear, ValueType, ValueAmount. ValueType 102/Land, 103/Improvements, 112/Total.
IMPROVEMENTS
Improvements are many-to-one and are used for the improvements with sketches sheets in the GeoCortex report. ANNUAL_REAL_IMPROV.sql query was written to pull the majority of fields necessary for this layer, which pulls from GRM_public.dbo.real_improv. ANNUAL_FULLBATH.sql is found to be more accurate in actual number of full baths than real_improv. Before running these queries, need to be sure public Cama tables have been updated by Assessor's department.
Create a UniqueID for each improvement record based on LRSN+PropExt (many properties have more than one improvement which has its own PropExt). This Unique ID is also created for sketches and then used to join sketch URL's to the appropriate improvement record. SketchPath.sql query is run to ascertain the sketch name and path to the sketch and then sketch images can be pulled from SktImage
folder and copied into gis
to create the URL links necessary for the necessary fields.
ImageYN, Image11Y, Image 21Y, and Image 31Y fields need to be added to be read by GeoCortex. If there is a sketch associated, then ImageYN is Y, if there are 11 or more sketches associated, then Image11Y is Y, and so on.
TAX CHARGES
Though Tax charges are also accounted for in the Parcels for Reporting Layer, the Tax Charges layer has many-to-one which is necessary for graphs. Query in SQL server from TaxMaster view for each parcel for last 5 years. RevObID and LRSN are the same identifier and so a one-to-many join was performed in ArcGIS Pro based on these values.
ANNUAL_TAX_MASTER.sql query is run and pulls data from GRM_Main.dbo.tsbv_TaxMaster. The fields needed are revobjid, taxyear, TaxOnlyCharges. Only need the last 5 years worth.
OTHER NOTES
Added YES for those with Homeowners Exemption per TSBv_MODIFIERS - using standard cap and calc cap override records. Per Dee Dee Gossi: The Calc cap override is used when individual owns a parcel or parcels that has something attached to it, such as a mobile home. The calcap override is used when a personal owns two or more parcels that are "under" an acre. So if one parcel is .204 acres and they own the contiguous parcel that is .113 acres I would relate this parcels and apply the calcap override so that the computer spreads the exemption appropriately over all parcels involved. The modifier for homeowners exemption usually will say 100% of $100,000 but the computer knows not to exceed the $100,000.