Streetbook

A classic ASP app which allows resident to look up streets and get static information about the street.

Basic Information

App Server	ZPCOBWEB01
Files location	D:\wwwcob\publicworks\streetbook
App files	default.asp - landing page, contains search textbox qryResponse.asp - runs the query to find streets matching search entered and returns table of results, qryResponse2.asp - runs a query to find the information for a selected street and returns results as a table. loader.asp - imports data from a file (streetbook_import.txt) into the database table.
Import data file	streetbook_import.txt - a csv exported from the excel file supplied by PWD. IMPORTANT 1. The file format must be: comma separated records, each field surrounded by double quotes (even if numeric fields). The double-quoting of all fields is important. 2. The field order must also be as per the example file streetbook_example.txt found on the server. The field order is important. 3. The file must be called streetbook_import.txt and be placed in the same folder as the loader.asp file. The file name is important.
Database Server	VSQL01
Database	pwd
Tables	StreetBook - Production table containing streetbook records StreetBook_2024 - Backup of prod data as at 10/10/2024 (not used by any prod functionality) StreetBook_du - testing table (not used by any prod functionality)
Scheduled Task	Streetbook Updater - uses powershell to open loader.asp every morning at 1am. Tip: You can run the scheduled task manually at any time. This is equivalent to opening loader.asp in a browser.
Nightly output file	The output from loader.asp is captured in the file loader_output.txt. You can see the results of the last scheduled task run in this file (its timestamp shows the date it was created = the last run datetime of the scheduled task).

How to update the Streetbook database

The loader.asp file has been set up to run as a scheduled task on ZPCOBWEB01. It runs each morning at 1AM.

Following the instructions in the orange IMPORTANT note in the table above, export the streetbook data to a new file, name it streetbook_import.txt and copy it to the D:\wwwcob\publicworks\streetbook folder on ZPCOBWEB01.

The file contents should copied over the existing data in the database overnight, so check in the morning to see.

Immediate run:

If you can't wait for the overnight run, you can open this page in a browser https://cityofboston.gov/publicworks/streetbook/loader.asp at any time ,but, see warning below:

WARNING: The loader.asp page is intended for automated use. Opening the loader.asp page in a browser will cause an immediate execution of the update script, and may overwrite the current data with the data in the file streetbook_import.txt. This may or may not be your intention, so take care opening the page !

Check for errors

If you think the file has not been imported, then check loader_output.txt for info and streetbook.log for errors. Note: If the log file is large, then you can safely delete it, the script will create a new file as needed.

Force a run

If you think the script is not determining that the file has new records (e.g. the loader_output.txt reports nothing done, but you know the file has changed) - then you can delete the checksum.txt file. This will cause the import to occur the next time loader.asp is opened (manually or by the overnight process)

Under the hood

When loader.asp runs,

1. It reads the streetbook_import.txt file and splits the records based on either UNIX (LF) or Windows (CRLF) line breaks

2. It deletes the existing data in the streetbook table

3. it imports the new data into the streetbook table - expecting it to be in the column order [PRIVATEOPEN],[STREET],[SFX],[DISTRICT],[PUBLICPRIVATE],[FROM],[TO],[ADDRESSES],[WARD],[PRCNT],[DIST],[PWDDIST],[ZIP],[NOTES]

4. It expects the first row of the file to be the column names, so the import starts on row2.

5. It expects the last row of the file to be a blank line so it skips the last row.

6. It checksums the file contents and only imports if the checksum of the contents of the current file is different to the checksum from the last import. The checksum from the last import is saved in the file checksum.txt. Tip: Deleting the checksum.txt file will force the import next time the script is run.

7. Each time an import is attempted, it is logged to streetbook.log

8. Any import issues are logged to the file streetbook.log

Updating as a content editor

1. A Google Script is attached to a Streetbook spreadsheet in our Boston Digital Service Team Drive. When you get the new data from Public Works, overwrite the data in that existing spreadsheet.

2. Run the script attached to the spreadsheet. This will create a new streetbook_import.txt file in the existing Streetbook files folder in our Team Drive. That is the file you need to use to update the search on cityofboston.gov.

3. Download that folder to your desktop. At this point, you will need to map a network drive to the D:\wwwcob\publicworks\streetbook folder on ZPCOBWEB01.

4. Drag the copy of the streetbook_import.txt file into that folder. The search will automatically update the next time loader.asp runs.

   1. If you need to force an immedaite update, follow the steps outlined above in the "Immediate Run" section.