And a new location:
INSERT INTO occupation (occupation_description, occupation_code) VALUES ('Pool Romm Attendant', '59990'), ('Setter', '81275'), ('Lamp Fitter', '87330'), ('General Finisher', '99999');
INSERT INTO "location" VALUES (84, 'BC', 'British Columbia', '13', 'Victoria City', '5', 'Central Park', NULL, NULL, NULL, NULL, NULL, '1911', NULL, NULL);
Received and added some new and updated page copy from PD. Added to both the development and live sites.
Received the latest set of 1911 census data from PD - subdistrict, #1, Vic West. Imported into the development database smoothly and without incident.
Also inserted six new occupations:
INSERT INTO occupation (occupation_description, occupation_code) VALUES ('Pianist', 17140); INSERT INTO occupation (occupation_description, occupation_code) VALUES ('Show Woman', 17225); INSERT INTO occupation (occupation_description, occupation_code) VALUES ('Butter Maker', 77530); INSERT INTO occupation (occupation_description, occupation_code) VALUES ('Gas Fitter', 87120); INSERT INTO occupation (occupation_description, occupation_code) VALUES ('Cement Worker', 95200); INSERT INTO occupation (occupation_description, occupation_code) VALUES ('Road Maker', 97415);
And a new location for the sub district:
INSERT INTO "location" VALUES (83, 'BC', 'British Columbia', '13', 'Victoria City', '1', 'Victoria West', NULL, NULL, NULL, NULL, NULL, '1911', NULL, NULL);
That likely means that the primary key sequence in the table you're working with has somehow become out of sync, likely because of a mass import process (or something along those lines). Call it a "bug by design", but it seems that you have to manually reset the a primary key index after restoring from a dump file. At any rate, to see if your values are out of sync, run these two commands:
ERROR: duplicate key violates unique constraint
If the first value is higher than the second value, your sequence is out of sync. Back up your PG database (just in case), then run thisL
SELECT MAX(the_primary_key) FROM the_table; SELECT nextval('the_primary_key_sequence');
That will set the sequence to the next available value that's higher than any existing primary key in the sequence.
SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);
Added more views to the PostgreSQL database to generate lists of values that are present in the census data, for the advanced search form.
vOriginsUsed - used for the "Birthplace", "Birthplace of Father", and "Birthplace of Mother" fields:
CREATE VIEW "vOriginsUsed" AS SELECT origin_id, origin FROM "origins" WHERE origin IS NOT NULL AND origins.origin_id IN ( SELECT "vCensus1881".birthplace_id FROM "vCensus1881" UNION SELECT "vCensus1881".birthplace_father_id FROM "vCensus1881" UNION SELECT "vCensus1891".birthplace_id FROM "vCensus1891" UNION SELECT "vCensus1891".birthplace_father_id FROM "vCensus1891" UNION SELECT "vCensus1891".birthplace_mother_id FROM "vCensus1891" UNION SELECT "vCensus1901".birthplace_id FROM "vCensus1901" UNION SELECT "vCensus1911".birthplace_id FROM "vCensus1911" ) ORDER BY upper(origin)
The origins view is pretty slow because of the large number of SELECT subqueries.
vNationalitiesUsed - used for the "Nationality" search field:
CREATE VIEW "vNationalitiesUsed" AS SELECT nationality_id, nationality FROM "nationalities" WHERE nationality IS NOT NULL AND nationalities.nationality_id IN ( SELECT "vCensus1901".nationality_id FROM "vCensus1901" UNION SELECT "vCensus1911".nationality_id FROM "vCensus1911" ) ORDER BY upper(nationality)
vRacesUsed - used for the "Race/Ethnic Origin" search field:
CREATE VIEW "vRacesUsed" AS SELECT race_id, race_name FROM "races" WHERE race_name IS NOT NULL AND races.race_id IN ( SELECT "vCensus1901".race_id FROM "vCensus1901" UNION SELECT "vCensus1911".race_id FROM "vCensus1911" ) ORDER BY upper(race_name)
vBandsUsed - used for the "First Nations Band" search field:
CREATE VIEW "vBandsUsed" AS SELECT band_id, band FROM "band" WHERE band IS NOT NULL AND band.band_id IN ( SELECT "vCensus1881".band_id FROM "vCensus1881" UNION SELECT "vCensus1891".band_id FROM "vCensus1891" ) ORDER BY upper(band)
vMotherTonguesUsed - used for the "Mother Tongue", "First Language Spoken", and "Second Language Spoken" search fields:
CREATE VIEW "vMotherTonguesUsed" AS SELECT mother_tongue_id, mother_tongue FROM "mother_tongues" WHERE mother_tongue IS NOT NULL AND mother_tongues.mother_tongue_id IN ( SELECT "vCensus1901".mother_tongue_id FROM "vCensus1901" UNION SELECT "vCensus1911".first_language_id FROM "vCensus1911" UNION SELECT "vCensus1911".second_language_id FROM "vCensus1911" ) ORDER BY upper(mother_tongue)
CREATE VIEW "vOccupationsUsed" AS SELECT DISTINCT ON (upper(occupation_description)) occupation_description AS occupation_description, occupation_id, occupation_code FROM "occupation" WHERE occupation_description IS NOT NULL AND ( (occupation.occupation_code IN (SELECT "vOccupations1881".code FROM "vOccupations1881" WHERE "vOccupations1881".count1881 > 0)) OR (occupation.occupation_code IN (SELECT "vOccupations1891".code FROM "vOccupations1891" WHERE "vOccupations1891".count1891 > 0)) OR (occupation.occupation_code IN (SELECT "vOccupations1901".code FROM "vOccupations1901" WHERE "vOccupations1901".count1901 > 0)) OR (occupation.occupation_code IN (SELECT "vOccupations1911".code FROM "vOccupations1911" WHERE "vOccupations1911".count1911 > 0)) ) ORDER BY upper(occupation_description)
Since location_id isn't part of the 1911 records insert, but sub_district_id is, use this SQL to update the location_id after you've put a row into the location table corresponding to the new sub-district:
UPDATE census_1911 SET location_id = (SELECT location.location_id FROM location WHERE location_02_id = '13' AND location_03_id = CAST(census_1911.sub_district_id AS character varying(50)) AND census_year = '1911')
Met with PD today to discuss the integration of the 1911 data thus far, and to go over any changes that need to be made. My task list from the meeting:
pg_update(), depending on whether any conditions exist. Aside from allowing for condition support, this change also keeps the data more organized and takes out the guesswork involved in building raw SQL queries via a
forloop. I also had to edit the
_fix_field()function in inc/lib.inc to stop the automatic escaping of data values into single quotes (e.g. 'data').
pg_update()escape automatically, so the code in
_fix_field()was resulting in double escaping.
PD recently sent me an updated version of the 1901 data with expanded address information, namely street number and street name separated into two fields. This will allow us to have street searching for both 1901 and 1911. However, the loader application didn't support updating existing rows, only importing new ones. So, I had to build update capabilities.
The loader now supports "conditionKey" lines, which can be used to specify conditions that need to be met in order to perform an update. The format of the line is:
For example, this is the condition key line that I specified when importing the new address data for 1901:
The field on the left of the colon tells the loader to look at the value of the
census_record_id column in the source data CSV, while the field on the right side of the colon tells the loader to check that value against the
census_record_id column in the database table.
Internally, adding one or more conditions causes the loader to use the
pg_update() function instead of the
pg_insert function for the row in question.
Multiple conditionKey lines may be specified.
With help from Greg I've put a development site for VIHistory online at: http://tapor.uvic.ca/~vihdev/
Access is currently restricted by Netlink ID to associated parties (myself, GN, MH, SA, PD, JL). The dev site runs its own database. We put this site online so that PD and JL could "beta test" the new 1911 data once it's ready for them. This also allows me to periodically commit my development changes to the SVN repository, rather than having them sit on my machine for months, in case I get hit by a bus (or suffer another similar calamity).
PD has sent a list of changes to be done to the advanced census search form, in preparation for the 1911 data:
viHistory is a web site that is a teaching, learning and research tool. It's principally about the history of Vancouver Island in British Columbia, but it is also a vehicle for exploring the larger field of Canadian history during the late 19th and early part of the 20th century. It allows census, directory and tax assessment roll data from the late 19th and early 20th centuries to be searched in many ways. It also incorporates IMaP to display historical maps. The project director is Dr. Patrick A. Dunae.
|<< <||Current||> >>|