Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
A No Tow subscription service where city residents can register a license plate and then receive alerts (email/text/voice) if a vehicle with that plate is towed.
See maintenance tasks.
Residents can use the page at https://www.cityofboston.gov/towing/alerts.
The resident is able to register a license plate and receive one or any of:
an email alert (managed by SQL Server)
a text alert (SMS via email)
a voice alert (managed by Twilio)
Every 15 minutes the city gets an update from the police on towed vehicles. For each new vehicle towed the license plate is checked against plates registered by residents and alerts are sent when matches are found.
This is an ASP application hosted on ZPCOBWEB01.web.cob (a DMZ IIS Server).
The code is is found in the following folder:
This page generates forms to collect information for email, text and voice alert subscriptions. The forms all submit to subscribe.asp
which does the actual subscription for alerts that are sent when a registered vehicle is towed.
Email:
Subscription - First the script checks if the email & plate is already subscribed. If it is not, then the script checks how many license plates are already registered against the email address, if its more than 10 it wont register this new one (unless the email is on a whitelist - see box out below). If the plate is not registered to this address, and the address has less than 10 plates registered to it (or is whitelisted) then the email/plate combo is registered in the table towed_emails
in the database Towing
on vSQL01 (aka ZPDMZSQL01).
Unsubscription - First the script checks if the email & plate is already subscribed. If it is, then the entry is removed from towed_emails
and is added to towed_emails_optout
in the database Towing
on vSQL01 (aka ZPDMZSQL01).
Fleet Owners Whitelist.
Line 125 of subscribe.asp contains a white list of subscribers who can register more than 10 vehicles.
Text:
Text subscription is completed by adding a record to the towing_emails
table, but using an email address that routes through the subscribers telephone provider. In this way sms messages can be sent without needing to use an sms gateway.
There is a "No Provider' option, which should ideally be removed.
Provider email to text gateways can be found here: https://avtech.com/articles/138/list-of-email-to-sms-addresses/
Note: We do have the option to send SMS via Twilio using a stored procedure in the twiSQL
database, however this incurs an additional cost. As at 2021-11-17, this process does work, and is used by the SMS block in the sp_process_towing_messages
stored procedure. To be certain that SMS messages are delivered, it could be that users who select 'No Provider' are added to the towing_sms
table.
Voice:
Subscription - First the script checks if the email & plate is already subscribed. If it is not, then the script checks how many license plates are already registered against the email address, if its more than 10 it wont register this new one (there is no whitelist for voice registrations). If the plate is not registered to this address, and the address has less than 10 plates registered to it then the email/plate combo is registered in the table towed_phonenumbers
in the database Towing
on vSQL01 (aka ZPDMZSQL01).
Unsubscription - First the script checks if the email & plate is already subscribed. If it is, then the entry is removed from towed_phonenumbers
and is added to towed_phonenumbers_optout
in the database Towing
on vSQL01 (aka ZPDMZSQL01).
This page allows the user to provide an email address and the system will email a list of plates registered to that email address.
The email handler used is installed as part of the IIS service at ZPCOBWEB01.web.cob (10.241.250.22), and the SMTP server the mail is routed through is at smtp.web.cob (10.241.250.209)
The utility code which manages connections to the Database Server and posts queries etc to the server is contained within:
D:\wwwcob\includes\stdf.inc
Note: the same include file has a mail handler which connects to (public) mail.cityofboston.gov (140.241.251.209) - but this seems to be deprecated.
The database server used by this sub-service is vSQL01 (aka ZPDMZSQL01). The server is hosted in the DMZ in the web.cob (aka lincdom) domain. Developers need a separate and specific account to be set up on the web.cob domain to view/edit databases and tables on this server.
The primary database used by this sub-service is Towing. This database holds information on registered plates and vehicles towed.
The tables used by this sub-service are:
The email/voice is handled by the SMTP service on the MSSQL Server.
Manages SMS message dispatch via the Twilio SMS gateway.
This database is used by the sp_process_towing_messages
. There are no tables inside the database, and 3 stored procedures. There is also an Assembly (twilioSQL) loaded., plus (possibly auto-created) a Service Broker.
The stored procedure SendBrokerMessage
is called in order to route an SMS message via Twilio.
The police update information on newly towed vehicles every 15 minutes. The police have a job/process that pushes the data to vSQL01.
The police data is inserted directly into the Towline_bpd
table by the Police IT department (contacts below). The actual SSIS-ODBC process involves truncating the towline_bpd table and then bulk inserting a complete set of new records.
Note: The MSSQL bulk insert does not fire triggers by default.
The police account (youvebeentowed (?)) needs permission to truncate the Towline_bpd
table and to (bulk) insert new records. It does not need permissions to stored procedures, triggers or the twiSQL database.
Reminder emails created and sent by the remindme.asp
page (one-time & on-demand by the resident) are routed through an SMTP server the mail at smtp.web.cob.
As the police update the towline_bpd
table (i.e. as vehicles are towed) alerts are originated and handled by the Towing database and TwiSQL database on the MSSQL Server at vSQL01.
Alerting is fully decoupled from the cityofboston.gov hosted asp pages, and initiated by the MSSQL server vSQL01.
There is a stored procedure, sp_process_towing_messages
, in the Towing
database which is executed every 5 minutes by the SQL Job; TowingSendMail
.
sp_process_towing_messages
reads the towline_bpd
table and discovers new tow events. It then determines which tows have subscriptions. Finally, it dispatches the outbound communications to subscribers.
The sp evaluates the inserted rows, looks to see if the license plate is registered (towed_emails, towed_phonenumbers and towed_sms
), and if so ends out an alert.
The sp uses the system stored procedure sp_send_dbmail
, to send emails directly to the subscriber from the MS SQL server.
Some SMS subscribers are also in the towed_emails
table because the subscriber has registered for SMS/text by providing a phone number and provider. (see box below)
The sp interfaces directly with Twilio (for unspecified-provider SMS's)
The sp drops records into a queue for voice processing (which runs on a scheduled task).
The sp records which recipients have been communicated with, and which have been processed
The sp maintains statistics on what has been sent out.
Alert emails:
Emails are sent directly from Towing.dbo.sp_process_towing_messages
. The sp constructs and send them out using the built-in MSSQL email service (from msdb.dbo.sp_send_dbmail
).
The email process handles both emails and (the majority of) sms messages. SMS strategy is to send an email to a mobile provider specific email address which then routes the email as an SMS to the recipient/subscriber.
For example the email record in towed_emails
781###@att.net was registered as phone number 781### and AT&T as the provider. Using that email format prompts an email-to-sms service at att.net.
SMS/Text messages:
The majority of text messages are sent via email-to-sms (see box-out above).
Note: Some subscriptions are saved in the table towed_sms
and initiated by process_towing_messages
.
Voice calls:
The process is managed within sp_process_towing_messages
which is executed every 5 minutes by the SQLAgent Job TowingSendMail
.
Voice calls identified in the stored procedure are queued into the table Towing_twilio_Queue
in the Towing
database.
Every 10 minutes, a scheduled task Towing_Twilio_Queue
runs on zpcobweb10
(10.241.250.22). The task runs a script c:\installs\scripts\curl_towing_queue_process.bat
.
The script simply calls twilio-alert-place.aspx.
curl.exe -connect-timeout 300 http://zpcobweb01.web.cob/towing/alerts/twilio/twilio-alert-place.aspx
Twilio-alert-place.aspx
app/script calls a stored procedure Towing.dbo.spTowingTwilioQueueRead
which fetches queued voice messages from the towed_phone_alerts_queued
table. For each queued message the endpoint payload and creates a Twilio object and initiates the outbound call (via the Twilio REST API). The app/script also removes the record from the queue (towed_phone_alerts_queued
) using the stored procedure towing.dbo.spTowingTwilioQueueDelete
.
The message which is sent to theTwilio REST service (to make the voice call to the subscriber) includes a call to an endpoint twiml-alert-content.aspx
This app/script receives the towed vehicle details from the querystring/payload and creates and returns the correct message for the Twilio voice service to read out.
The message also defines an interaction where the call recipient can hear the message again, obtain tow company information or unsubscribe (using spTowingTwilioUnsubscribe
) from future voice alerts.
There is an additional endpoint at Twilio-incoming.aspx
. This is provided to handle incoming calls to the No-Tow telephone number. This app/script determines the caller phonenumber and looks up recent towing records to see if a vehicle with a plate registered to this number has been towed. If so the details of the tow event are read back to the caller
The job which initiates the sp which processes new alerts does need extensive permissions.
Stored Procedures (execute permission):
msdb.dbo.sp_send_dbmail: to actually send out emails using MSSQL Mail services.
twiSQl.dbo.SendBrokerMessage: This interacts with a service on the twiSQL DB on the same server (????) There is a twilioSQL assembly loaded on the twiSQL database, but not on the Towing DB.
Functions (Call/Execute permission):
Towing.dbo.functionVerboseReasonCode
Towing.dbo.functionUnabbreviateTowCompany
Towing.dbo.functionUnabbreviateYear
Towing.dbo.functionUnabbreviateCarMake
Towing.dbo.functionUnabbreviateCarColor
Towing.dbo.URLEncode
Tables (Insert and read permission):
Towing.dbo.Towline_bpd (also requires delete permission)
Towing.dbo.towed_import_log
Towing.dbo.towed_emails
Towing.dbo.towed_emails_optout
Towing.dbo.towed_alerts_log
Towing.dbo.towed_phonenumber
Towing.dbo.towed_phone_alerts_queued
Towing.dbo.towed_phone_alerts_log
Towing.dbo.towed_sms
Towing.dbo.towed_sms_alerts_log
Voice alerts are somehow processed by Twilio using a complicated call-back process to cityofboston.gov.
This needs further investigation and access to the twilio UI.
Statistics as at 10/27/2021 | Value |
---|---|
Staff | Position | |
---|---|---|
Email subscribers List
765,168
Voice Subscribers
3.292
SMS Subscribers
1 (Satyen)
TableName
Key Fields
Description
towed_emails
towed_phonenumbers
towed_sms
subscriber_email, or
subscriber_phone
subscriber_plate
subscriber_state
Contains list of mail, sms and voice subscribers and their plates to monitor.
Towline_bpd
License Plate
Tow Date-Time
Infringement Location
Contains a list of all vehicles towed by services authorized by the City.
towed_emails_log
towed_phonenumbers_log
towed_sms_log
Contains log of alerts raised
towed_import_log
Contains a summary of activity (used in daily report)
towed_phone_alerts_queue
Contains a queue for voice messages which are then processed by an external service.
Towline_complete
Archive for the complete list of towed vehicles since time began.
Rich Petruccelli
Data Services Manager
Thomas Hutchings (Hutch)
Frank Alexopoulos
Apps development
The street sweeping and no tow (this link and this link) applications are legacy applications. As part of the 'Apps Modernization' capitally funded project for DoIT/Digital we are re-doing this application.
Historical Satyen Shah (very part-time consultant) has helped maintain the applications.
The applications need a complete overhaul. Preliminary research by students groups has been completed. However, a synthesis of the research and how to proceed is needed (i.e. build versus buy; if build then how), designs, and implementation still need to be completed.
Street sweeping information is found online and on physical street signs on City streets. The City tells constituents that the street sign is the source of truth. The City database for the physical signage doesn't currently match the database for the web page/application; they're close but sometimes changes are made to the signage without updating the web. This means that sometimes the two are out of sync. Paul Taylor, IT for Public Works Department, can update the information so they match. This is an opportunity for improvement.
Student research 1: https://drive.google.com/drive/folders/1OG9rH34o1NCwci1k6Vb7sXBcnoyglpy6?usp=sharing
Student research 2: https://drive.google.com/drive/folders/16C0NGhkNFQgr1CI2Rg6WEBNH-pdCwwdV?usp=sharing
Historical context/notes from a company that registers/monitors a fleet of vehicles.
At one point in time Satyen suggested the City suggest we connect the data to the RMV, which they have not agreed to.
When boston.gov was first launched this was included, but not implemented elsewhere. Should decide if/how keeping when these applications are re-visited: https://www.boston.gov/having-car-city#towed-cars
Semi-related: Analytics is helping move this to Analyze Boston rather than cityofboston.gov (Courtney Moores is running point on the effort): https://www.cityofboston.gov/publicworks/sweeping/Resurfacing.aspx
An overview of the currently inactive Dog Licenses project
The Dog Licenses app is a tool for applying for, renewing and looking up a dog license in the City of Boston. The app is owned by Animal Care/Control.
App is currently located here: https://www.cityofboston.gov/animals/licenseapp/
Yearly maintenance needed on this app: Dropdown for previous year needs to be updated yearly around the turn of the new year (January 1) so that people can reference a license number from the year prior.Dropdown for year needs to be updated yearly around the turn of the new year (January 1) so that people can reference a license number from the year prior.
Improve the existing business process(es) associated with applying for new dog license or renewing/looking up an existing dog license.
Tracking: Essentially the cityofboston.gov sends Animal Care and Control an email, and staff manually enter information from that email into Chameleon.
Chameleon is a software used by Animal Care and Control to track the licenses - all info that is submitted. Theoretically shares information nationally with any groups that also use the software and/or with major sites for lost pets, etc.
Form Submission: Custom asp.net form app.
Payments: Connects to Invoicecloud payments API - payment data sent via invoicecloud.
05/07/2019 - Reilly checked with Susan Hynes - Susan couldn’t find any contracts in Peoplesoft financials for Chameleon or Chameleon beach
Animal Care and Control mentioned that Georges/Enterprise worked with Chameleon, so Jeanethe check with Joe/Goerges on this. Georges said the only one he knew that worked on a connection for animal licenses was Jeff Ng who helped connect cityofboston.gov to invoice cloud a few years ago. More on this here: See line 20 here: https://docs.google.com/spreadsheets/d/1P6aBfR0rlltQgrCupTcvCvO70HzViQe6HxI3IiJWLXA/edit#gid=1207577308
General discovery with Alexis/Animal Care and Control
Explore use of chameleon (https://chameleonbeach.com/), i.e. software used by Animal Care and Control to track the licenses - all info that is submitted.
Explore automation of manual entry of information from emails (cityofboston.gov) into Chameleon. Can this be automated?
Discrepancies between what is asked in person or via mail versus what is asked online. Note: There are differences - Example: We don't prompt for the aging population to get a discount like we do in person and via mail.
Explore fee waiver use cases (e.g., do seeing eye dogs get fees waived?)
Online payment. Is this another opportunity for Stripe? Currently connected to Invoice Cloud, but users get kicks to invoice cloud landing page.
Explore merits of moving to Drupal or something else like forms, relative to keeping the app separate?
'Breaks' from time to time due to miscellaneous issues
We have feedback from constituents from feedback emails and customer satisfaction survey which is a Seamlessdoc > needs to be pulled/analyzed.
Project Plan
Jeanethe is on deck to set up a time with Animal Care and Control for next steps
PHASE I: DISCOVER
List of internal users and roles
List of external users groups
Anyone needing to get a dog in Boston licensed, which
User journey map
For each group, a list of their needs, and whether/how they are being met by existing processes or technology
Summarized insights from interviews with users (internal and external)
Summarized information and supporting documentation on existing processes and technologies and any constraints they impose
Survey of existing solutions (digital or not, in government or other industries)
Metrics to determine what success looks like
Hypotheses you will test during the prototyping phase
PHASE II: PLAN
Project scope
Preliminary project timeline, including milestones, deliverables, and resources needed at each stage
PHASE III: PROTOTYPE
Define minimum viable prototype to test with users
User testing plan and script
Summarized findings from user testing
PHASE IV: BUILD/BUY
Build vs. buy recommendation with supporting rationale
If buying, summarized information on the vendor selected
List of expected phases of implementation (e.g. staged releases or alpha version)
Documentation on the technology or processes involved
Plan for collecting and incorporating feedback
Plan for publicizing the product
PHASE V: IMPROVE
Notes from retrospective
Feedback from users post-launch
User metrics
Plan going forward
connects to Animal DB on vsql71.cityhall.boston.cob.
Username and password contained in web.config in /animals/licenseapp
There is payments processing. at cityofboston.gov/animals/licenseapp it passes off to payments cloud somehow at https://www.invoicecloud.com/portal/cloudpayment.aspx
No Tow is a branded term referring to a collection of services designed to allow city of Boston residents to manage their parking activities to avoid being towed.
The No Tow suite is comprised of the following sub-applications/sub-services:
Street Sweeping Reminders: A subscription service where emails can be sent to registered constituents reminding them of sweeping days for a nominated street. Subscription here: https://www.cityofboston.gov/publicworks/sweeping/
Street Occupancy Alerts: A subscription service where emails can be sent to registered residents advising them of temporary parking changes (no parking permits issued etc) for a nominated street. Subscription here: https://www.cityofboston.gov/streetoccupancy/search/
Towing Alerts: A subscription service where city residents can register a license plate and then receive alerts (email/text/voice) if a vehicle with that plate is towed. Subscription here: https://www.cityofboston.gov/towing/alerts/
Towing Search: A lookup service to see if a vehicle with a specified license plate has been towed. Search here: https://www.cityofboston.gov/towing/search/ This page on boston.gov also has a lookup: https://www.boston.gov/departments/transportation/what-you-need-know-about-towing
Subscription Search: City residents can get a list of subscriptions for a supplied email on this page https://www.cityofboston.gov/publicworks/sweeping/remindme.asp
The No Tow link on the side menu on cityofboston.gov links to this: https://www.cityofboston.gov/contact/default.asp?id=187 which is a departments listing page on boston.gov
A No Tow subscription service where emails can be sent to registered constituents reminding them of sweeping days for a nominated street.
The Summer/Winter sweeping timetable is automatically adjusted by the scripts and does not require developer intervention to restrict notifications in the winter months.
See Summer/Winter section of lyris.asp below.
Residents can use the page at https://www.cityofboston.gov/publicworks/sweeping/.
First the resident searches for their street or a calendar. Matching street results are returned from a master schedule/calendar and the resident nominates which streets should be included in the alert. The resident then supplies an email address and a preferred email reminder time.
Subscription from this page registers the resident for street sweeping reminders, and also for Street Occupancy alerts.
The sending of emails is managed by a Lyris email server installed on the city network. The body of the email is constructed in a script called by lyris at lyris.asp
.
This service is active, there appear to be around 5 new subscriptions per day. There are 100,488 (as at 2021-10-01) current active subscriptions, but many may be old and refer to defunct emails. Lyris should be able to provide bounce reports.
If the list is managed in Lyris, then entries removed in Lyris should be manually removed from the PwdSweepingEmails table or the remindme.asp (see Subscription Search) will be inaccurate.
Lyris maintains subscribers (members) in its no-tow list.
Note: Lyris subscribes street occupancy and street sweeping members to the same no-tow list.
Note: Lyris manages its list members and removes those which cannot be delivered to. Hence the numbers in Lyris are lower than in the Towing DB tables.
This is an ASP application hosted on ZPCOBWEB01.web.cob (a DMZ IIS Server).
The code is is found in the following folder:
This is the configuration file and this contains the database credentials.
This page contains a list of cancellations. It looks like known sweeping holidays (e.g. Veterans day) are loaded for the year in advance, and then ad-hoc cancellations (e.g. Snow or Ice) are loaded as the cancellation is advised by PWD. Cancellations for general street sweeping and overnight sweeping are maintained separately.
The search form for the registration process is contained on this page. The search is conducted against the PwdSweeping
table in the Towing
database on vSQL01 (aka ZPDMZSQL01).
The residents subscription choices (streets & dates) are managed by this page for use in the subscription process.
Subscription is initiated on this page but the actual subscription process is handed off to subscribetostreet.asp
(see below).
Also the user can download a file which can be imported into calendars supporting the iCalendar format (this is most calendars). The file generation and download is managed by addtocalendar.ics.asp
(see below)
If the default page is called with a querystring, then a search is performed and results returned. This is a relatively sophisticated search and provides schedules and the next sweeping event date.
Announcements column has a very outdated entry for 2013.
This page controls subscriptions and unsubscriptions.
Subscriptions:
Subscribing involves first removing and then adding the residents email and street into the table PwdSweepingEmails
in the Towing
database on vSQL01 (aka ZPDMZSQL01).
if this is a first time subscription, the user is added or removed from the Lyris email server at http://listserv.cityofboston.gov/subscribe/subscribe.tml.
If this is not a first time subscription, then the users time preference is added to the members_
table in the Lyris
database on vSQL01 (aka(ZPDMZSQL01).
Unsubscriptions:
Unsubscribing involves removing the residents email and street from the table PwdSweepingEmails
in the Towing
database on vSQL01 (aka ZPDMZSQL01).
The iCalendar (ics) format is presently supported by: Google Calendar, Apple Calendar (formerly iCal), IBM Notes (formerly Lotus Notes), Yahoo! Calendar, Evolution (software), eM Client, Lightning extension for Mozilla Thunderbird and SeaMonkey, and partially by Microsoft Outlook and Novell GroupWise
This page extracts a schedule from the PwdSweeping
table in the Towing
database on vSQL01 (aka ZPDMZSQL01) and formats into an ics format and then downloads the ics file to the residents computer.
This page is a microservice endpoint managed by IIS/ASP.
A list of cancellation and block-out dates is provided at the top of the script. (This list mirrors those input into default.asp).
The endpoint is designed to be called with an email address and date in the querystring. The script will return nothing if the email recipient is not scheduled to receive an email, and a full html if the recipient is due an email (according to the streets and Email time preference settings).
The script runs SQL statements against the PWDSweepingEmails
, PWDSweeping
and PwdDist
tables in the Towing
database on vSQL01 and applies logic to determine if an email is required for that recipient.
(See Lyris below in Connected Services)
The script manages the annual program dates according to this "rule":
The Daytime Street Cleaning Program runs from April 1 to November 30 in most Boston neighborhoods ... (however) ... Daytime street sweeping continues into the winter in the North End, South End, and Beacon Hill ... (which) ... stops on December 31 and starts up again on March 1.
The body text for the Street Sweeping reminder emails is set in this script.
The body of the Street Occupancy emails is also set in this script.eh
Email Schedule
Emails are sent out by a scheduled mailing from lyris at 7am, 2pm and 5pm each day. That scheduled mailing calls this script to generate emails which Lyris then sends out. The constituent can choose a time (7am, 2pm, 11pm) when they register. A constituent may make multiple separate registrations for the same street with different notification times to get multiple notifications.
The script will accept a date as part of the querystring, but if no date is provided, then tomorrows date is assumed.
7am, 2pm and 5pm Notifications: Lyris does not provide a date as part of its request, so the script generates emails advising of daytime sweeping for following day, and overnight sweeping for the following night. e.g. Email generated on Friday provide notifications for daytime sweeping on Saturday, and overnight sweeping Saturday evening though Sunday morning.
Sweeping Cancellations
The script can be manually edited and advance cancellation dates can be added.
If the logic determines that a recipient should receive an email, and a matching date is found in the list of cancellations a note is added to the reminder advising that sweeping has in fact been cancelled.
dictCancellations.Add
All sweeping on this day is cancelled (i.e. both daytime and overnight). Content Editors should add to this dictionary (i.e. list of cancellations):
1. For planned city sweeping holidays (e.g. Christmas). Adding to this dictionary is a scheduled task to be undertaken every year at the start of November for the following calendar year.
2. On notification from Public Works Dept when one-off, unplanned exceptional circumstances (e.g. snow storms) occur.
dictOvernightCancellations.Add
Only overnight sweeping is cancelled. Content Editors should add to this dictionary (i.e. list of cancellations) when notified by Public Works Dept for one-off, unplanned exceptional circumstances (e.g. snow storms).
Note: In some cases cancellations are decided late in the evening, after emails have been sent.
Remember emails remind about sweeping the next day
Because in most cases some emails reminders will have been sent before the cancellation occurs and the cancellation is added to the script - the city Content Editors also place a cancellation notice on the homepage of boston.gov.
Summer/Winter Adjustments.
The City has 3 classifications which indicate which winter sweeping program is operated on city managed streets. Streets are classified as Normal (no sweeping 01 Dec through 31 March), Northend Pilot (no sweeping 01 Jan through last day of Feb) and YearRound (streets sweept all year). .
The PwdSweeping
table in the Towing
database holds information on streets managed by the city and columns yearround
and northendpilot
to indicate the streets sweeping program. (if neither column has a "1" in it, then the street is "Normal"). There also appears to be a Charlestown Pilot (defined as streets in district 1C) The timetable is controlled using the same flags, but a note is added to emails to advise elevated fines in lieu of towing.
If there is an issue with a constituent receiving a sweeping notification during a winter "no-sweeping" period then:
Ask the constituent to check signage to be sure the mail was sent in error. If the mail does appear to be incorrect, then
Check the Street definition at the page: cityofboston.gov/publicworks/sweeping/admin/default.aspx (check dashlane for the password) Ensure the checkboxes next to "Winter Extension" (aka Northend Pilot) and "Year Round" are set properly, The checkboxes are correctly set, then
Contact a developer.
This page allows an administrator to login, alter and export detailed and low-level street cleaning metadata in the PwdSweeping
table.
Login information is in DashLane.
The PwdSweeping
table, contains all information on the partitioning and scheduling of Boston City streets cleaning activities.
The utility code which manages connections to the Database Server and posts queries etc to the server is contained within:
D:\wwwcob\includes\stdf.inc
The database server used by this sub-service is vSQL01 (aka ZPDMZSQL01). The server is hosted in the DMZ in the web.cob (aka lincdom) domain. Developers need a separate and specific account to be set up on the web.cob domain to view/edit databases and tables on this server.
The main database used by this sub-service is Towing.
It appears that the code uses a trusted connection to the database server. To connect to this database you first need to have an account in the lincdom domain. Then you need to have the Microsoft SQL Server Management Studio installed on your work PC. Then you need to connect to the server at zpdmzsql01.web.cob using your lincdom account (user:LINCDOM\username + password:userpassword). This should work.
The tables used by this sub-service are:
There is a database called Lyris on the same server (vSQL01).
The Lyris list server (on zLyris) uses the Lyris
database on vSQL01. The recipients (members) and their send-time preferences are stored in the members_
table.
Both Lyris and the Towing
DB maintain a list of subscribers.
The list in Lyris (the members list) is maintained and bad emails and unsubscribes them,
The list in the Towing
DB is not maintained and hence there are many unsubscribed members in to Towing
DB email tables.
Recipients are selected by Lyris from its (curated) list, but the recipients preferences and streets etc are taken from the Email table in the Towing
DB.
Lyris is used for dispatching the street sweeping emails. This is an in-house email server (a mailing list server) which has a subscription API at http://listserv.cityofboston.gov/subscribe/subscribe.tml. The list subscribed to for sweeping alerts is no-tow
.
The Lyris server is available on https://listserv.cityofboston.gov
login can be provided by James Duffy and will be your city email address and a password. If you are a Server Administrator you will be able to configure the actual Lyris service.
The list used is named no-tow
, subscribers are added to the list, and removed from it when they unsubscribe.
Lyris is responsible for:
Maintaining the list of current subscribers (members),and
Scheduling and initiating the 7am, 2pm and 5pm mailings each day, and
Physically sending the emails required.
BUT Lyris does not
Know the members preferred email time, or
Know the members street selection, or
Blackout dates, or
Generate or manage the email body text
(These are managed by the :lyris.asp script)
A page at admin/default.aspx allows an administrator to maintain information on the partitioning and scheduling of Boston City streets cleaning activities used (primarily) by the NoTow suite of apps..
PWD tend to re-organize street cleaning at the start of the "season" in March/April, and they will update the database table, using the admin page, at that time. Subsequent schedule changes are maintained in the table by PWD staff as they occur. The main PWD staff member doing this work is Paul Taylor. Any errors and omissions reported during the year are reviewed by Paul Taylor and (if required) he updates/alters the database using the admin page. This final step could be done by Digital provided a note is sent to Paul after the change is made.
The current street sweeping database can be dumped to a csv here: https://www.cityofboston.gov/publicworks/sweeping/admin/database.csv
The PwdSweeping
is not a copy of any other database, and it does not appear that there is any other centralized record of the street partitioning and sweeping schedule.
Districts can be found from an ArcGIS map on the COB ArcGIS server.
Additional street information, including linear feet from street origin to intersections can be found here: http://zppwdapp01.web.cob:83/apps/stations/ (created and maintained by Paul Taylor)
Potential Data Issues
(Needs verifying)
The email registration for Street Occupancy and Street Sweeping alerts uses an ID from the the PwdSweeping table (MainID). If a street is split or merged, it is possible that email registrations could become orphaned.
Examples:
169th Street get split into two (for sweeping activities). The original schedule started at 0ft and ended at 1000ft, with a MainID of 236 and sweeping was even days. The street now gets split by adding a new street partition (MainID of 423) from 501ft to 1000ft and sweeping is set for odd days. The original (MainID 236) is updated so it now starts at 0ft but ends at 500ft. The issue is that anyone who lives between 500 and 1000ft will be registered against MainID 236 instead of MainID 423 and will thus get email reminders for the wrong days.
A similar type of issue could occur if streets are merged, but in this case registrations the street being "removed" would stop getting emails even though from their perspective the sweeping continues unchanged.
Statistics as at 10/27/2021 | Value |
---|---|
Table Name | Important Fields | Description |
---|---|---|
Total List
39,218 (lyris)
101,139 (PwdSweepingEmails - Towing DB)
August 2021 subscriptions
317
September 2021 subscriptions
473
August 2021 Unsubscribe
-258
August 2021 unsubscribe
-256
Table Name
Key Fields
Description
PwdSweeping
MainID
St_name
Dist(rict)
StartTime
EndTime
Side
... [schedule info]
Contains street sweeping schedule information for streets in the city.
This table is managed from the admin/default.aspx page.
PwdSweepingEmails
EmailAddr
StreetID
Contains information on who has subscribed to what.
StreetID maps to the MainID in the PwdSweeping table.
This table is maintained by the scripts in this sub-service. It is also used by the Subscription Search (aka remind me).
members_
EmailAddr_
FullName_
List_
Neighborhood
NowTowTimePreference
Allston_Brighton_
BackBay_BeaconHill
BayVillage_
Charlestown_
Chinatown_Downtown_
Dorchester_
EastBoston_
Fenway_Kenmore_
HydePark_
JamaicaPlain_
Mattapan_
MidDorchester_
MissionHill_
NorthEnd_WestEnd_
Roslindale_
Roxbury_
SouthBoston_
SouthEnd_
WestRoxbury_
A list of subscribed members.
[NowTowTimePreference] is 24hr clock for email time pref.
The legacy back-end sits mainly on a MSSQL Server, with some data manipulation performed by asp pages on the IIS server
As a first step, it is possible to strip all business logic and processing from the SQL server, and extract to a module within Drupal.
The best way to do this might be to create a rest service which is capable of subscribing and unsubscribing users, and processing alerts.
Then a front end can be created to call the various endpoints of the REST service to query, add and remove subscriberse
Use Drupal as core.
Drupal content type manages the email subscription list
Drupal is used to render subscription forms etc as webpages on boston.gov
Cron in drupal manages communications dispatches.
Cron in drupal manages sync of Street sweeping (SAM) records and Street Occupancy (Hansen) records.
Use PostMark as the email delivery mechanism.
Retain Twillio for SMS (and voice?)
Use either existing MSSQL DB (COB Datacenter) or else port to MySQL (on Acquia)
This app initiates and manages inquiries from the public on existing workers' compensation claims filed with the City.
This is a form and data grid C# app hosted on ZPCOBWEB01 (cityofboston.gov IIS server in DMZ).
The application is found in the folder at /wwwcob/workerscomp/inquiryform.
The normal workflow is:
Inquiry is filed by a member of the public at workerscomp/inquiryform.aspx.
The inquiry is saved in a database table workerscomp.inquiries in VSQL01
An email is returned to the inquirer
The workers compensation team login to workercomp/inquiryform/inquiryadmin.aspx and review the request.
They can make notes and mark the inquiry as open or closed
They can respond to the inquirer using (email sent via /workerscomp/inquiryform/response.aspx
The database server used is on VSQL01 (DMZ database), and the database is workerscomp.
As of March 2023, emails are now routed through justice.cityhall.boston.cob. (prior to 2023 updates ZSMTP01 - smtp.web.cob was used)
ZPCOBWEB01, an IIS 10 webserver, hosts the legacy cityofboston.gov website. These applications are either Classic ASP (VBScript) or ASP.net (C# or VB.net) apps.
The applications on this server are all slated for either decommission, or migration to a more appropriate host and/or boston.gov.
These docs inform or manage the migration process.
From the Legacy App Discovery Project: https://docs.google.com/spreadsheets/d/1Th2htYUHf4LVvbQ_rWC0eLBhAiHGKjszzO8w2tg9i8g/edit?usp=sharing
List of applications on this server:
A No Tow subscription service where emails can be sent to registered residents advising them of temporary parking changes (no parking permits issued etc) for a nominated street.
First the resident searches for their street or a specific permit number. Matching permits are returned as a list, along with a list of streets matching the search term or permit provided. The resident nominates which streets should be included in the alert and then supplies an email address and a preferred email reminder time.
Email body text is generated in the lyris.asp
script and the email generation is managed by the Lyris server (listserv.boston.gov)
Lyris subscribes street occupancy members to the no-tow list.
This is an ASP application hosted on ZPCOBWEB01.web.cob (a DMZ IIS Server).
The code is found in the following folder:
The search form for the registration process is contained on this page. The search is conducted against the occupancyupdates, SAM_streets
and OccupancyPermitsHansen
tables in the Towing
database on vSQL01 (aka ZPDMZSQL01).
The residents subscription choices (streets & dates) are managed by this page for use in the subscription process.
This page controls subscriptions and unsubscriptions.
Subscriptions:
Subscribing involves first removing and then adding the residents email and street into the table OccupancyEmails
in the Towing
database on vSQL01 (aka ZPDMZSQL01).
If this is not a first time subscription, then the users time preference is added to the members_
table in the Lyris
database on vSQL01 (aka(ZPDMZSQL01).
Unsubscriptions:
Unsubscribing involves removing the residents email and street from the table OccupancyEmails
in the Towing
database on vSQL01 (aka ZPDMZSQL01).
The utility code which manages connections to the Database Server and posts queries etc to the server is contained within:
D:\wwwcob\includes\stdf.inc
The database server used by this sub-service is vSQL01 (aka ZPDMZSQL01). The server is hosted in the DMZ in the web.cob (aka lincdom) domain. Developers need a separate and specific account to be set up on the web.cob domain to view/edit databases and tables on this server.
The database used by this sub-service is Towing.
It appears that the code uses a trusted connection to the database server. To connect to this database you first need to have an account in the lincdom domain. Then you need to have the Microsoft SQL Server Management Studio installed on your work PC. Then you need to connect to the server at zpdmzsql01.web.cob using your lincdom account (user:LINCDOM\username + password:userpassword). This should work.
The tables used by this sub-service are:
OccupancyUpdates
appears to contain information on that last date the permits were synchronized from some external system (probably Hansen),
OccupancyPermits/OccupancyPermitsHansen
contains information on issued permits,
SAM_Streets
contains street name information, from the (most likely sync'd from the SAM system).
OccupancyEmails
contains a list of subscribers for street occupancy emails
members_
table contains information on email scheduling.
There is a database called Lyris on the same server (vSQL01).
It seems that the Lyris mail server uses the Lyris
database on vSQL01. It also seems the recipients and their send-time preferences are described in the members_
table. Further, Lyris seems to get the streets the members are subscribed to from the OccupancyEmails
table.
The list used is no-tow
.
Get a login to Lyris to see how the listserv process works.
It is possible that the emails are generated outside of Lyris and by the MSSQL Server. This needs investigation.
Information from the CoB custom application Street Address Management (SAM) appears to be used to provide some street information.
It is likely that there is some ERL process (managed by Analytics?) to move data from SAM databases (MSSQL) to the Towing Database on ZPDMZSQL01 (vSQL01).
Someone should find this migration process. Maybe Sundar/Satyen or Analytics have some knowledge.
Hansen is the CoB permitting system.
Hansen is the authoritative source for issued permits. It is likely that there is some ERL process (managed by Analytics?) to move data from Hansen (MSSQL) to the Towing Database on ZPDMZSQL01 (vSQL01). The date of the last data sync with Hansen is shown on the search page.
App | Location | Test Page/Links | Notes |
---|---|---|---|
Residents can use the page at .
Statistics as at 10/27/2021 | Value |
---|
Subscription is initiated on this page but the actual subscription process is handed off to subscribetostreet.asp
().
if this is a first time subscription, the user is added or removed from the Lyris email server at .
Lyris is used for handling the street occupancy emails. This is an in-house email server (a mailing list server) which has an API at . The list used is no-tow
.
Assessing
/assessing/search
use parcel 05204203000 or 0100010000
Property Search by pid
Property Search by owner
Boards & Commisions Admin
//zpappweb01:cityclerk/commissions/account/Login
Note this is not on ZPCOBWEB01, it is on ZPAPPWEB01
City Clerk
/cityclerk/hearing
Doing Business As
use business name = "Tow"
Dog License
/animals/licenseapp
use tag A-01036 name Juniper
Fire Permits
/fire/inspections/sdiform
ISD Breathe Easy
/isd/housing/bmc/admin
Use RichardO / test
Street Resurfacing
/publicworks/sweeping
Classic ASP app
Street Sweeping
/publicworks/sweeping
aspx (c#) app
Taxi Vouchers
/elderly/vouchers
account details unknown
This site is apparently NOT USED
aspx (vb.net) app
aspnetdb database disconnected (or user login removed)
Towed Cars
use plate 123456
Video Library
/cable
Use "street" and "Public Works"
Workers Compensation
/workerscomp/claiminquiry
Login details in dashlane
Total List | 10,559 (OccupancyEmails - Towing DB) |
The Dog Licenses app is a tool for applying for, renewing and looking up a dog license in the City of Boston. The app is owned by Animal Care/Control.
App is currently located here: https://www.cityofboston.gov/animals/licenseapp/
See the project page at Animal Control
The asp.net (C#) codebase is found on ZPCOBWEB01
(IIS server) in the folder /wwwcob/animals/licenseapp.
Connects to the AnimalControl
database on vsql01.web.cob
to cache in-process license applications.
Connects to the Animal
database on vsql71.cityhall.boston.cob
to search for licenses and to update the Chameleon database used by Animal Control Dept.
Usernames and passwords contained in web.config
in /animals/licenseapp
There is payments processing: the app passes off to InvoiceCloud portal (https://www.invoicecloud.com/portal/cloudpayment.aspx) when the application form (form.aspx
) is submitted (viaProcessPay.aspx
).
Payment results are handled by a callback/webhook page Final.aspx
.
The year dropdown on the search form needs to be updated with the last calendar year (20YY) so that people can search a previous license number for that year.
31 March is when the current licenses expire.
From 01 April license tag numbers will be prefixed with AYY- where YY is the year for the March when they expire.
There is typically nothing to be done by digital, because the tag sequential number is reset by Animal Control staff in Chameleon.
The Digital Team is responsible for maintaining the ASP app and databases which drive the assessing online application.
See tabbed notes for December (on this page) for detailed instructions.
When ready to deploy:
See tabbed notes for July (on this page) for detailed instructions.
The ASP application is served by an IIS Server from zpcobweb01.web.cob.
On the Windows server hosting the IIS Server, the ASP files which make up the assessing online app can be found at d:\wwwcob\assessing
.
The files which are appear to be live (i.e. have no redirect to boston.gov) and which currently live in the folder are:
Create sandbox ASP page for testing.
Create a copy of default.asp
named default20XXQ3.asp
.
Update DB connection string.
Once the SQLDBA's have cloned the current live database:
In the global.asa
for the IIS server (in the root of wwwcob folder) there are a number of database connection strings defined. Search for assessingupdates
and you will find a block defining the assessing-online apps connection strings. Make a new connection string called assessingupdates_20YYQ3
and point it at the new database created by the DBA's.
The constant for AssessingSearchDB
in (line 58 of) default20YYQ3.asp
indicates the actual connection string for the page to use. Change it to assessingupdates_20YYQ3
(which will then run SQL queries on the page in the assessingupdates20YYQ3
database in the MSSQL Server).
Make changes to dates and tax rates for next year.
Using data provided by the Assessing Team, update the constants in default20YYQ3.asp.
Refer to the notes in the default.asp page next to each constant for guidance.
Testing/verification of the new data can now be completed at:
https://cityofboston.gov/assessing/search/default20YYQ3.asp
The page rendered by this script should have a banner indicating this is still a test page along with info on the date being simulated (see below) and the connection string being used.
This banner will automatically be removed when the page is deployed (i.e. when the file is renamed default.asp
)
While testing, you can add &date=YYYY-MM-DD
to the URL to test the site for a future date and see that the date fields and the availability of exemption forms links are altering on the page as expected.
Create sandbox ASP page for testing.
Create a copy of default.asp
named default20YYQ1.asp
.
Update DB connection string.
Once the SQLDBA's have cloned the current live database:
In the global.asa
for the IIS server (in the root of wwwcob folder) there are a number of database connection strings defined. Search for assessingupdates
and you will find a block defining the assessing-online apps connection strings. Make a new connection string called assessingupdates20YYQ1
and point it at the new database created by the DBA's.
The constant for AssessingSearchDB
in (line 56 of) default20YYQ1.asp
indicates the actual connection string for the page to use. Change it to assessingupdates20YYQ1
(which will then run SQL queries on the page in the assessingupdates20YYQ1
database in the MSSQL Server).
Testing/verification of the new data can now be completed at:
https://cityofboston.gov/assessing/search/default20YYQ1.asp
Typically the dates in the form do not change, and the tax rate should also not change. Really, the only expected change to default20YYQ1.asp
is to change the DB connection string name.
There are a number of forms which are provided as part of the assessment process during the first part of each calendar year.
PDF's are generated by a service on boston.gov so the templates for these forms are saved in docroot/sites/default/files/pdf_templates/pdf/FY20YY
on the boston.gov webserver.
When a constituent requests a form, it is processed through the bos_assessing
managed endpoint at boston.gov/assessing-online/
This endpoint adds a barcode (related to the parcelid) and selected tax bill information onto the form.
See Assessing Form generation notes here, and more detailed notes on PDF generation here.
Various assessing forms are made available to constituents based on a calendar. The calendar is set in the constants fields in wwwcob\assessing\search\default.asp
(see box above).
Once those dates are set, the form display is controlled by code and no further action is needed to enable or disable them.
PDF files can be modified and copied to the boston.gov web server using this command (from the docroot folder):
Non-Prod Environments
rsync -arz -essh -P sites/default/files/pdf_templates/pdf/FY20YY/* "bostond8.dev@bostond8dev.ssh.prod.acquia-sites.com:/mnt/gfs/bostond8dev/sites/default/files/pdf_templates/pdf/FY20YY/"
This command copies to the dev environment. All other Acquia non-prod environments map this folder, so running this command simultaneously copies across for all non-prod environments.
Prod Environment
rsync -arz -essh -P sites/default/files/pdf_templates/pdf/FY20YY/* "bostond8.prod@bostond8.ssh.prod.acquia-sites.com:/mnt/gfs/bostond8/sites/default/files/pdf_templates/pdf/FY20YY/"
Each year new forms for exemptions and other request are generated and will be provided by the Assessing Team.
There is nothing to be done with forms in the July updates.
The database which contains the data for the assessing online app is contained onvsql01
(aka zpdmzsql01, vsql02
, zrb01
(?)). This may well be migrated to vsql71
at some point.
The assessing department validate and transfer data to the Digital Team via an MS Access database. This database contains tables of data that should be mapped and uploaded to databases on the MS SQLServer.
MSAccess data is imported into tables prefixed "_" in the MSSQL Database.
Stored procedures are then written to update/merge data from the import tables into the main tables used by the app. The stored procedure is saved in the DB as a record of the updates and manipulations performed.
There is a bash script on the cityofboston IIS server (zpcobweb01
) at /wwwcob/assessing/search/extract.sh
which migrates and pre-processes the data from MSAccess to the MS SQL Server. In a perfect world, this script can just be run to preform the migration (it takes some hours to complete).
Obtain the updated copy of the MS Access database.
In the assessingupdates20YYQ3
database, delete all tables prefixed "_", these are import tables from the previous Q1 update.
Following these instructions, use the extract.sh
script to copy tables from Access to assessingupdates20YYQ3
in MS SQL.
Notes from Assessing Team will detail any particular instructions on data manipulations that are required.
To keep a record and for replaying updates, and data updates (from import tables to main tables) and data manipulations should be coded into stored procedures and executed from the stored procedure.
The stored procedure should be saved in the DB, and named sp_20YYQ3_import
. If multiple sp's are used, then number them sequentially (sp_20YYQ3_import_1
etc).
For example, in 2023Q3 there is a (incorrectly named...) stored procedure sp_update_current_owners
which should be executed to make additional data changes to parcel/property ownership.
Testing/verification of the new data can then be completed at:
https://cityofboston.gov/assessing/search/default20YYQ3.asp
Obtain the updated copy of the MS Access database.
In the assessingupdates20YYQ1
database, delete all tables prefixed "_", these are import tables from the previous Q3 update.
Following these instructions, copy tables from Access to assessingupdates20YYQ1
in MS SQL.
Notes from Assessing Team will detail any particular instructions on data manipulations that are required.
To keep a record and for replaying updates, and data updates (from import tables to main tables) and data manipulations should be coded into stored procedures and executed from the stored procedure.
The stored procedure should be saved in the DB, and named sp_20YYQ1_import
. If multiple sp's are used, then number them sequentially (sp_20YYQ1_import_1
etc).
Filename | Last Modified | Description |
---|---|---|
..\global.asa (wwwcob)
2022
Contains the database connection strings for the whole cityofboston.gov website. See drawer below.
paymentinfo.asp
07/2017
payments.asp
11/2015
res_prop_sales_archive.asp
03/2016
Suspect this file is not live, just an archive of res_sales_prop.asp which has not been redirected.
taxratehistory.asp
03/2016
global.asa (assessing)
08/2013
Contains connection strings which do not appear to be used.
paymentshistory\default.aspx
03/2014
Unknown, appears to be a file uploader - inserts records into the Treasury database on vsql01
search\default.asp
11/2022
This is the main script which drives the AOL application. See drawer below.
search\hotline.asp
06/2020
Unknown, looks unused.
search\json.asp
05/2016
Unknown, looks unused.
This application is largely maintenance-free. There are no annual processes.
There following is a list of tasks we are requested from time-to-time
From time to time, a fleet operator asks us to add a block of plates to the system as a batch rather than them entering them one at a time via the interface.
In some cases:
a the list will be the full list they want monitored, in which case all 3 steps below should be followed, or
b. the list is just a block of new plates to register, in which case only the last step below needs to be completed.
The database is VSQL01.web.cob
and the database is towing
.
(optional) Gather a list of existing plates registered to this email, and export to a csv or json or something.
(optional) Delete existing plates for this email:
Insert batch of new records:
This is done by adding the new recipient to the No-Tow@cityofboston.gov
email group.
How-tos and gotchas for legacy Assessing On Line (aka AOL -sic)
The dates in search/default.asp
(approx lines 43-48):
These dates are used to control a text block which invites residents to submit Property Revaluation submission/requests.
The PublicDisclosureStartAnnouncing, PublicDisclosureStartOnWeb
and PublicDisclosureEnd` should all be the same date/time, or be in the past to prevent the message from displaying.
Generally, the dates do not need to be updated year-to-year unless advised by Assessing.
When dates are set to show the block, this is what it looks like this (using Nov 2023 based dates):
If requested by the Assessing Dept, the current owner of a parcel/property will need to be changed on occasion. You need to know the old owner name, the new owner name and the parcel id - this change can be made directly in VSQL01
using this script:
This page contains information on the database tables in assessingsearch on VSQL01. (NOTE: assessingupdates is a clone of assessingsearch for dev)
This view does not appear to be used by default.asp
or any other procedures.
This view could be deleted from the DB.
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
This table is accessed from default.asp
.
Check the columns in the MS Access database to see if new information is provided. If the unlikely event that it is, then check with Assessing Dept to see if the info needs to be displayed.
If so, then the query and HTML table in default.asp
needs to be updated to display the new information.
This table is accessed from default.asp
.
Potentially, there is some adjustment of the bid amount depending on billing schedule.
Check the tables in the MS Access database to see if new BID region/categories have been created. If the unlikely event there are, then check with Assessing Dept to see if the info needs to be displayed.
If so, then this table will need an additional column for the new bid region/category, and the HTML in default.asp
will need to be updated.
This table is accessed from default.asp
.
This table is accessed from default.asp
.
It seems that this table only contains owners with the seqno
>= 1, so the primary current owners.
2022 year only included seqno
= 1, but the code looks like it will be OK with seqno
> 1
This table is accessed from default.asp
.
This data should be unchanged between years, but double check with Assessing Team to be sure.
Nothing to do, just be sure the table exists from the previous year
This table is accessed from default.asp
.
This table does not appear to be used.
This table is accessed from default.asp
.
This table is accessed from default.asp
.
This table is accessed from default.asp
.
This data should be unchanged between years, but double check with Assessing Team to be sure.This data should be unchanged between years, but double check with Assessing Team to be sure.
This table is accessed from default.asp
.
This table is accessed from default.asp
.
This table is accessed from default.asp
.
This table is accessed from default.asp
.
This table is accessed from default.asp
.
This table is accessed from default.asp
.
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:
Column | Source | Notes |
---|---|---|
Column | Source | Notes |
---|---|---|
Column | Source | Notes |
---|---|---|
Column | Source | Notes |
---|---|---|
Column | Source | Notes |
---|---|---|
Column | Source | Notes |
---|---|---|
Column | Source | Notes |
---|---|---|
Column | Source | Notes |
---|---|---|
Column | Source | Notes |
---|---|---|
Column | Source | Notes |
---|---|---|
Column | Source | Notes |
---|---|---|
Column | Source | Notes |
---|---|---|
Column | Source | Notes |
---|---|---|
Column | Source | Notes |
---|---|---|
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
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.
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)
Parcel_id nchar(10)
PK
seqno tinyint
PK
owner_name nvarchar(255)
id int
Short_Description nvarchar(10)
Description nvarchar(50)
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)
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_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)
property-code smallint
property-class tinyint
property-class-description nvarchar(255)
property-code-description nvarchar(255)
property-code-state bit
property-code-city bit
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 Personal Ex Type 1
or Personal Ex Type 2
is not null.
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)
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)
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)
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
Parcel_id nchar(10)
Fiscal_Year smallint
Assessed_value bigint
Land_use nvarchar(4)