City of
~nE.-a••~
1 ~
r ::;ii~,
DEPARTMENT OF PUBLIC UTILITIES
MEMORANDUM
DATE:
TO:
FROM:
June 5, 2024
BROCK D. BUCHE, PE, PLS, Director
Department of Public Utilities ~
PAUL AMICO, PE, Assistant Director-Administration ~
Department of Public Utilities - Administration Division SUBJECT: DECLARATION OF CITY'S PROCESS FOR MAILING NOTICE OF
PUBLIC HEARING ON PROPOSED SOLID WASTE RATE CHANGES
This memorandum describes development of the mailing database and the actions taken to mail notice of the hearing at which the City Council would consider proposed residential solid waste rate changes, as required by Proposition 218, to property owners and ratepayers.
Solid Waste Rate Update Requirements
The Proposition 218 notification process requires that the City identify parcels upon which the residential solid waste service charges will be imposed and mail the record owners of those parcels written notice of the hearing on the proposed rate update (Notice). In 2014, the City entered into a Settlement Agreement and General Release of Claims with Doug Vagim, Diane Smith, and Steve Wayte (Vagim Settlement). In addition to the requirements in Proposition 218, the Vagim Settlement requires, among other things, that, before adopting any utility rate or fee increase in the 10 years following the Agreement's effective date, the City provide notice, to both property owners and tenant ratepayers.
Mailing Database Data Sources
Generating a mailing database that complies with the requirements to send the Notice to both owners of record and tenant ratepayers required City staff to join three different databases maintained for different purposes: two different City departments and one by the County of Fresno (County) Assessor. Each of the databases includes a unique number, also known as a primary key, to identify individual records and perform database joins and queries. Each of the databases also has a corresponding layer in the City's and County's geographic information systems (GIS) maps that spatially define property boundaries, service locations, and address points. The following lists the databases used to generate the mailing database and the primary key in each that was used to join the data:
June 5, 2024
1. The County Assessor’s parcel database is used to maintain property ownership data and names of the owners of record. It contains a table called “ownr_all_dat” and the Assessor’s Parcel Number (APN) is the primary key used to uniquely identify parcels of land and the parcel’s owner of record. Ownr_all_dat is the source of the Proposition 218-required property owner data.
2. The City Department of Finance – Utilities Billing and Collection (UB&C) Division’s customer billing database is used to maintain active ratepayer data, bill for solid waste services, and maintain ratepayer names and mailing addresses. The service locations are mapped as a point layer in the City’s GIS, and the service location primary key is called the “LCID.” The customer identification primary key is contained only in the billing database and is called the “CSID.” This database includes multiple tables that needed to be joined to prepare the database of active solid waste customers at the parcels receiving solid waste service , and it is the source of the required ratepayer data:
a. Table UT200AP contains customer name and mailing address data. The CSID is primary key in this table.
b. Table UT210AP contains customer account status (active or inactive) and data to join the account to a location. This table includes the LCID and CSID. c. Table UT220AP contains information on the services active at a location (solid waste, water, or wastewater services) and it includes the LCID and CSID to join the customer account and location data.
d. Table UT120AP contains information on all locations where DPU services are available, whether the services are active or inactive. The LCID is the primary key in this table, and it also includes LCIDs for locations in new developments where service will be provided in the future. e. Table UT100AP contains customer class, billing cycle, and location data.
This table includes the LCID for existing service locations , and it also includes LCIDs for locations in new developments where service will be provided in the future.
All the tables in the billing database are hosted by the City’s software-as-a-service (SAAS) vendor on an off -site server, and the working tables used by DPU are copied each night from the vendor to a local City-owned server.
3. The City Information Services Department (ISD) – GIS Division’s (GIS Division) address database contains GIS layers used to maintain property address data and to obtain validated postal addresses. It contains a table called “addresses_all” and the addresses are mapped as a point layer, and the primary key is called the “ADRID.” The table also includes the APN and LCID associated with the ADRID.
This data is the source of the address for the utility service location in the utility billing database . This data is also used to join the APN, LCID, and ADRID to maintain accurate address data for developed parcels and locations receiving City services. GIS Division also maintains a table called “Parcels_All” that was used in mailing database generation.
June 5, 2024
Mailing of Notice of Public Hearing
City staff prepared three different Notice mailings on the following dates and to the following numbers of recipients:
1. March 15, 2024 to 146,106 recipients
2. March 26, 2024 to 3,875 recipients
3. May 6, 2024 to 142,992 recipients
After the first mailing ( Mailing 1 ), City staff received calls and emails from recipients inquiring about Notices that had incorrect owner or ratepayer names, and City staff also became aware that some active ratepayers did not receive the Notice. UB&C also verified that some of the owner and ratepayer data was incorrect. Upon further investigation, City staff determined that using the mailing list generation tool developed by the City’s GIS Division to add tenant ratepayers to the mailing database either d idn’t include active ratepayers or included tenants who are not ratepayers. To provide the Notice to owners of record and ratepayers who were inadvertently missed in Mailing 1, City staff performed a GIS query known as a “spatial join” to select all parcels with an LCID point inside the parcel to generate a list of all APNs with solid waste service. This APN list was compared to the Mailing 1 database and 3,875 additional records were generated and added to the Mailing 1 database, and a second mailing (Mailing 2) was sent to provide notice to these 3,875 ratepayers. However, since th is Notice did not provide the additional owners of record and ratepayers with 45 -days’ notice of the public hearing, the public hearing needed to be continued or rescheduled to provide those recipients with the full 45 -days’ notice.
Continuing or rescheduling the hearing provided City staff additional time to generate a third database using a different approach that could be used to compare the regenerated mailing database to the combined Mailings 1 and 2 database to identify records with incorrect owner names and include the tenant ratepayers who didn’t receive the Notice .
Identifying the errors and omissions required comparison of the name and address text string fields in these large databases. Manually comparing over 140,000 records would have required significant staff time and effort to complete, so a database tool called a “fuzzy string comparer” was used that assigns a score to records based on how closely the text strings match. Staff conducted a sensitivity analysis and used best judgement to determine the appropriate score for a match, but City staff could not confidently confirm that the tool would produce accurate results and generate a mailing database that would comply with the Proposition 218 and Vagim Settlement requirements. Consequently, City staff and the Administration concluded that rescheduling the public hearing and re - noticing all owners of record and ratepayers using the third database was the best and most efficient option to provide 45 -day notice of the public hearing to owners of record and tenant ratepayers. A third mailing (Mailing 3) was sent using this database.
Development of each of the mailing databases is described below.
June 5, 2024
Mailing 1 Database Development
The Mailing 1 database was generated using the databases listed above and City staff also utilized a tool developed by the City GIS Division that can generate mailing lists for discrete geographic areas or selections of parcels. The tool uses a join of addresses_all and County property data and selects data that includes the APN, location address, and the mailing address where tax bills are sent. The tool compares location address and mailing address of the selected parcels, and if those addresses are different, it adds records to the results that include “Occupant Of” the location address to generate a mailing list that can send correspondence to both owners of record and any “occupant of” a location. This tool will be referred to as the GIS Division Mailing Address Generation Tool.
The Mailing 1 Database was generated using the following process:
1. DPU staff used the local copy of the UB&C billing database to join to addresses_all using the LCID to generate a list of APNs with active solid waste service. The result of this join will be referred to as the Active Service Parcels Table.
2. The Active Service Parcels Table was joined to Parcels_All using the APN to generate the list of the owners of record with active solid waste service. The result of this join will be referred to as the Owners of Record Table.
3. The Owners of Record Table was used as the selection for the GIS Division Mailing Address Generation Tool to generate a list of records that will be referred to as the Mailer Owner/Occupant Table. Records with “Occupant Of” in the Mailer Owner/Occupant Table were assigned a “T” to designate tenant ratepayers, and all other records were assigned an “O” to designate owners of record.
4. The Mailer Owner/Occupant Table was joined to the utility billing database to generate the Mailing 1 database using the owner of record name for records designated with the “O” and the customer name from the utility billing database for records designated with the “T.” The mailing address for the Notice was the property address for owners and service address for tenant ratepayers.
Each record in the Mailing 1 database was assigned a unique sequential number called “ID” as the mailing database primary key, and a 2-dimensional QR code that corresponds to the ID was generated and printed on the protest card. The printing and mailing vendor is scanning this QR code to link the protest to the parcel and to also identify whether the protest is a duplicate, since only one protest per parcel will be counted.
Mailing 2 Database Development
As described above, after Mailing 1, City staff performed a spatial join to generate a list of all APNs with solid waste service , and 3,875 Notices were sent to owners of record and ratepayers. At this time, City staff also determined that the automated process that copies the UB&C billing database from the SAAS vendor to the local City servers had malfunctioned in November 2023, before the Mailing 1 database was generated using the local data. This malfunction is believed to be a source of the incorrect owner or ratepayer June 5, 2024
names that were identified after Mailing 1. C ity staff also determined that the use of the GIS Division Mailing Address Generation Tool to reach tenant ratepayers did not produce accurate results and could not be relied upon to provide the Notice to the tenant ratepayers. After this analysis and investigation, City staff determined that recreating the mailing database to eliminate the use of the GIS Division Mailing Address Generation Tool to reach ratepayers would be the best course of action to produce a reliable mailing database.
The Mailing 2 database was considered to be an extension of the Mailing 1 database.
The ID for the records in the Mailing 2 database are also unique and were continued from the unique sequential number in the Mailing 1 database.
Mailing 3 Database Development
After City staff and the Administration concluded that completely recreating the mailing database was the best course of action to meet the requirements of Proposition 218 and the Vagim Settlement, the Mailing 3 database was developed using the following process:
1. ISD staff queried the SAAS vendor’s database directly to obtain the most current data for active customers and service locations. This table contained LCID and CSID data and will be referred to as the Active Customers Table.
2. GIS Division staff joined the County’s property ownership data to Addresses_All to generate a table of owners of record and APN from the County data for each LCID in the City’s data. This table will be referred to as the Owners of Record Table.
3. The Active Customers and Owners of Record Tables were joined to produce the Mailing 3 Database of owners of record and ratepayers.
The Mailing 3 Database primary key is also a unique sequential number called “ID” that is different than the numbers in the ID field in the Mailing 1 and 2 databases. This will allow City staff to differentiate the records in the Mailing 3 database from the Mailing 1 and 2 databases. The Mailing 3 database also includes a field called “List” that contains an “X” to further differentiate these records. The QR code and X are printed on the protest card, and the return envelope also has a yellow stripe on it t o visually distinguish the Mailing 3 protests from Mailings 1 and 2.
The Mailing 3 Database was spot checked by City staff for accuracy by randomly reviewing individual records in all of the data sources and comparing to what was generated in the Mailing 3 database development. Several iterations of the queries were conducted to produce a reliable and accurate database for Mailing 3.
Using the Databases to Mail Notices
The Presort Center of PacWest Direct (Presort) is a print and mailing facility that specializes in bulk printing and mailing for a variety of functions, including customer and taxpayer communication and elections. Presort used the records in the mailing databases to print unique Notices and protest cards for each owner of record and ratepayer included June 5, 2024
in the databases. Each of the Notices can be scanned using the 2-dimensional QR code to identify which owner of record or ratepayer the mailing was sent to.
Notice mailings were sent on the following dates to the following numbers of recipients:
1. March 15, 2024 to 146,106 recipients
2. March 26, 2024 to 3,875 recipients
3. May 6, 2024 to 142,992 recipients
Comparing Mailing Databases to Validate Protests
Only one protest per APN will be counted , and any protest received from any of the mailings will also need be counted, so comparing protest data from each of the mailings will be performed to validate the protest data.
The following process will be used to validate the protest data:
1. Use LCID to compare protests received from each of the mailings to identify whether multiple protests from a service location have been received.
2. If there are multiple protests from a single LCID, prepare a table of LCIDs that also contains the APN. This will be called the Valid Protest Table.
3. Summarize the Valid Protest Table to eliminate duplicate APNs and count each APN as a single protest.
Memorandum Preparation
The following City staff were involved in mailing database development and contributed to this memo:
• Steve Deedon
• Edith Smith
• Niteshkumar Patel
• Lorraine Kuroda