Pacific Coast Architecture Database (PCAD)

About

The Architect DB includes structure, location, architect, partner, exhibit, publication, website and image search capability. It also provides links to google maps.

This version of the Architect DB was developed for Alan Michelson, Head of the Architecture and Urban Planing Library. Alan, for the past several years, entered data into an access database (California Architectural Database (CAD)) using Cold Fusion forms. The database is hosted at UCLA. There is no publically accessable front end to that version of the database. This version of the database provides that front and some additional functionality. It is hosted by the UW Libraries ITS group. It is not in production as of Dec 2005.

This page was last edited 09 DEC 2005.


Data conversion

The data was supplied in access db format, CAD.mdb. The data was dumped from the file using mdbtools. The schema was edited and used to create a postgresql architect database on prometheus. The major changes to the schema included the addition of:

  • primary keys
  • foreign key constraints
  • sequences
  • indexes
  • entrydate, edit date

A perl script was used to clean up the data so that it could be parsed and loaded. perl.subs.sh

perl -i -p -e 's/\"\"/\"/g' *.sql
perl -i -p -e 's/\"\"/\"/g' *.sql
perl -i -p -e "s/\'/\'\'/g" *.sql
perl -i -p -e "s/, \"/, \'\'/g" *.sql
perl -i -p -e "s/\", /\'\' ,/g" *.sql
perl -i -p -e "s/\(\"/(\'\'/g" *.sql
perl -i -p -e "s/\"\)/\'\')/g" *.sql
perl -i -p -e "s/\"/\\\"/g" *.sql
perl -i -p -e "s/,\"/,\'/g" *.sql
perl -i -p -e "s/\",/\',/g" *.sql
perl -i -p -e "s/\r//g" *.sql
perl -i -p -e "s/^\n//g" *.sql
perl -i -p -e "s/^ \n//g" *.sql
perl -i -p -e "s/^  \n//g" *.sql
perl -i -p -e "s/^   \n//g" *.sql
perl -i -p -e "s/^    \n//g" *.sql
perl -i -p -e "s/^     \n//g" *.sql
perl -i -p -e "s/^      \n//g" *.sql
perl -i -p -e "s/^       \n//g" *.sql
perl -i -p -e "s/htm\n$/htm /ig" *.sql
perl -i -p -e "s/htm \n$/htm /ig" *.sql
perl -i -p -e "s/htm  \n$/htm /ig" *.sql

perl -i -p -e "s/e,\' opin/e,\" opin/ig" *.sql
perl -i -p -e "s/,\' note/,\" note/ig" *.sql
perl -i -p -e "s/acts,\'/acts,\"/ig" *.sql
perl -i -p -e "s/it,\'/it,\"/ig" *.sql
perl -i -p -e "s/tel,\'/tel,\"/ig" *.sql
perl -i -p -e "s/urch!, \'\'/urch!, \'\'\'/ig" *.sql
perl -i -p -e "s/ance,\'/ance,\"/ig" *.sql
perl -i -p -e "s/ttle,\'/ttle,\"/ig" *.sql
perl -i -p -e "s/\)$/\);/g" *.sql
perl -i -p -e "s/\'\'\);$/\'\);/g" *.sql
perl -i -p -e "s/CA, '',/CA\',/ig" *.sql
perl -i -p -e "s/, \'\',\'/\',\'/g" *.sql
perl -i -p -e "s/,\' at/,\'\' at/g" *.sql

perl -i -p -e "s/BASS-ett\'.;/BASS-ett\"/g" tblarchitects.sql
perl -i -p -e "s/Priteca,\'/Priteca,\"/g" tblarchitects.sql
perl -i -p -e "s/Spike,\'/Spike,\"/g" tblarchitects.sql
....................

Data loading

A set of sql scripts was used to load the data into the postgres database.

tblimages.sql 

INSERT INTO tblimages (ImageID, ImageDate, Photographer, Description) VALUES (1,'11/11/1111','Jones'
,'house');

Tables

There is a table for each entity and a set of intersection tables for the relationships between the entities.


Searches

There a a php script for each search and record. Each search and record is different.
Therefore, it is more efficient to keep the search and record logic in separate scripts.
The searches are designed to provide a variety of ways to traverse the database.


Data entry/edit

The data entry/edit/delete forms are designed to enable the curation of records and the relationships between the records.

  • Curation form actions:
    • enter record data
    • add link(s) to other records
    • edit record data
    • remove link(s)
    • delete record - and all of it's links
    • remove link(s)

  • List of admin scripts
    • record
      • add.form.phtml
      • add.phtml
      • delete.form.phtml
      • delete.phtml
      • edit.form.phtml
      • edit.phtml
    • link
      • link.add.form.phtml
      • link.add.list.phtml
      • link.add.phtml
      • link.remove.phtml
    • search
      • admin.search.form.phtml
      • architect.admin.search.phtml
      • exhibit.admin.search.phtml
      • image.admin.search.phtml
      • location.admin.search.phtml
      • partner.admin.search.phtml
      • publication.admin.search.phtml
      • structure.admin.search.phtml
      • website.admin.search.phtml

  • query.phpm functions
    • table data
      • get_fields ($type)
      • get_textarea_fields ($type)
      • get_required_fields ($type)
      • get_noedit_fields ($type)
    • links
      • get_architects ($dbh, $architectid, $linktable, $field, $linkedto, $admin)
      • get_locations ($dbh, $locationid, $linktable, $field, $linkedto, $admin)
      • get_structures ($dbh, $structureid, $linktable, $field, $linkedto, $admin)
      • get_websites ($dbh, $websiteid, $linktable, $field, $linkedto, $admin)
      • get_publications ($dbh, $publicationid, $tlinkable, $field, $linkedto, $admin)
      • get_partners ($dbh, $paid, $linktable, $field, $linkedto, $admin)
      • get_exhibits ($dbh, $exhibitid, $linktable, $field, $linkedto, $admin)
      • get_images ($dbh, $imageid, $linktable, $field, $linkedto, $admin)
    • lists
      • get_architects_list ($dbh, $lname, $architectid, $linkedto)
      • get_locations_list ($dbh, $streetname, $locationid, $linkedto)
      • get_structures_list ($dbh, $structurename, $structureid, $linkedto)
      • get_websites_list ($dbh, $website, $websiteid, $linkedto)
      • get_publications_list ($dbh, $title, $name, $publicationid, $linkedto)
      • get_partners_list ($dbh, $name, $paid, $linkedto)
      • get_exhibits_list ($dbh, $title, $exhibitid, $linkedto)
      • get_images_list ($dbh, $desc, $imageid, $linkedto)


To do

There are a number of things that can be done to improve the database.

  • Use lookup tables for city, state, country, structure type, location type, nationality
  • Do not enter an entity more than once - enter it once and create multiple links to it
  • Create an architect info table that contains biographical data
    • don't use a free text field (ditto for location)
  • clean up the data.....