Cleaned Oracle Data base of 60,000 "bad" addresses. The first step in this project was to review access to add this type of data. Second training was held to review proper procedures to add this data. The actual cleaning was done in several passes. Deficient addresses, those missing cities or zip codes were geocoded. Those that were found with 100% accuracy were updated. Those that were found not to be in our jurisdiction were disabled from future use.
Comparisons were made against other databases, and typical spelling errors were fixed. In the end 50,000 of the addresses were fixed this way. The remaining 10,000 were fixed by hand. A GIS map, shown here, was created using city boundaries, zip codes, streets and parcels. Addresses that were identified as correct were updated. Those that were not exactly identified were given their closest approximate city and zip then were disabled so they would not be reused. Two reports were created to verify and maintain the integrity of the data. |
Over 5,000 physical files needed to be prepped and sent to the archive office. Working with student volunteers the files were first boxed in numerical order. The minimum amount of information was then verified to exist in electronic form.
SQL plus was then used to update Oracle with the box number the file was stored in. An index for each box was created and the paperwork prepared for transfer. Finally the boxes were actually delivered. Some of the problems encountered included, turnover of the student volunteers and the need for retraining. Several boxes of files were "found" stored in different locations after the first few shipments were delivered. The boxes indexes and paperwork had to be corrected. Several "parts" of an individual file were stored in different locations in our office. These "parts" needed to be recombined creating a complete file. The old Rolodex "paper index" was also compared to the database and archived. Several "lost" paper files were identified this way and were added to the database completing the data set. Phase 2 of the archiving project consisted of archiving 5282 Conditional Use Files, 846 Permitted Use Files, 1421 Miscellaneous Files, 828 FCOZPU Files, 91 FCOZCU Files, and 48 PUD Files. For a Grand Total of 8516 files filling an additional 522 boxes to the 273 boxes from phase one.
|
I simplified the organization of the Business License section of our database from over 50 license types to 5. In addition, working with the financial staff, the name and amounts audited. This resulted in consistent naming and values.
The primary reporting tool used in our office is Crystal Reports. This data requests included business that have paid a particular fee with a time frame or licenses that have not paid a particular fee. Building information requested includes permits that have not had activity in a particular timeframe. General office reports include things like how many files were done and how long the average time was. In addition to the Business Licenses projects above several corrections to old, business license reports were done. These include a report of cabaret’s, licenses by zip code, out of business licenses for Police, corrections to the V.O.C letter, corrections to the renewal card, Business Licenses that have Fees paid but still owe monies, licenses that have an issue date but no expiration date, changes to the administrative fee, and new Business License renewals. The text “HO” was changed in the database to “Home Occ.” A “How to” for the new renewal process was created and staff was trained. |
|
With all the addressing cleaned up additional analysis could be done. Business License tax information was reviewed. I worked with others to verify the tax revenue was being sent to the correct jurisdiction. This resulted in several hundred thousand dollars’ worth of corrections. The County Mayor and Council signed a resolution stating their gratitude to the team.
|