Revision [11722]
This is an old revision of BreakTranscript made by LeishaD on 2014-10-28 16:54:29.
Speech Error Queries
This is a list of queries to be run from time to time to analyze the consistency of our data, i.e. to correct errors and standardize formatting. Notice that the SELECT, FROM, and ORDER BY clauses are all roughly the same. The goal is to display specific information in a specific order so that we can systematically identify and correct public statements containing errors.
Bear in mind some of these queries will return intimidatingly large numbers. Beware of false positives. Out of almost 900,000 speeches, errors are to be expected. Depending on the error, it may be possible to give the query to IT who can fix some errors automatically. Others will have to be done manually.
Changes can only be done automatically if there are definitely 0 false positives, otherwise they will be next to impossible to isolate and fix.
Pledges - A query that finds speeches with the type: "pledge":
SELECT s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id) WHERE s.speechtype_id = 11 GROUP BY s.speech_id ORDER BY s.title
Thomas - A query that finds speeches with Page or Time breaks:
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id) WHERE s.speechtext LIKE '%[Page:%' OR s.speechtext LIKE '%[Time:%' ORDER BY c.candidate_id, s.speechdate
Thomas - A query that finds the thomas speeches with incorrect URLs:
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id) WHERE s.url LIKE '%homas.%' AND s.url NOT LIKE '%vote%' AND s.url NOT LIKE '%juan%' AND s.url NOT LIKE '%governor%' AND s.url != 'http://thomas.loc.gov' ORDER BY s.url, s.speechdate
Thomas - A query that finds the speeches with thomas.loc.gov URLs, but do not have the type Floor Speech
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id) WHERE s.url LIKE '%homas.%' AND s.url NOT LIKE '%vote%' AND s.url NOT LIKE '%juan%' AND s.url NOT LIKE '%governor%' AND s.speechtype_id != 14 ORDER BY s.url
Thomas - A query that finds the speeches with thomas.loc.gov URLs, but do not have Washington, DC as the location:
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location FROM speech s JOIN speech_candidate sc USING (speech_id) JOIN candidate c USING (candidate_id) WHERE s.url LIKE '%thomas.loc.gov%' AND s.location != 'Washington, DC' ORDER BY s.location
Hearing - A query that finds speeches of type hearing, but the title does not reflect our formatting rules: Resolved 10/17/2013
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id) WHERE title NOT LIKE '%Hearing of the%' AND speechtype_id = 13 ORDER BY c.candidate_id, s.speechdate
Hearing - A query that finds speeches with Hearing of the in the title, but the type is not hearing:
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id) WHERE title LIKE '%Hearing of the%' AND speechtype_id != 13 ORDER BY c.candidate_id, s.speechdate
Interview - A query that finds the speeches of type "intervie", but the title does not reflect our formatting rules:
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id) WHERE s.title NOT LIKE '%Transcript%' AND s.speechtype_id = 3 ORDER BY c.candidate_id, s.speechdate
Interview - A query that finds the speeches with Transcript in the title, but the type is not interview:
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id) WHERE s.title LIKE '%Transcript%' AND s.speechtype_id != 3 ORDER BY c.candidate_id, s.speechdate
Interview - A query that finds speeches from a long time ago with the location as the news show they appeared on (will have to be done manually), most are marked as speeches as well.:
SELECT speech_id, title, location, speechdate FROM speech WHERE location LIKE '%Meet the Press%' OR location LIKE '%60%' OR location LIKE '%FOX%' OR location LIKE '%Hardball%' OR location LIKE '%Morning%' OR location LIKE '%ABC%' OR location LIKE '%CNN%' OR location LIKE '%Larry King%' OR location LIKE '%Late Edition%' OR location LIKE '%This Week%' OR location LIKE '%Hannity%' OR location LIKE '%MSNBC%' OR location LIKE '%CBS%' //**Letter** - A query that finds speeches of type letter, but the the title does not reflect our formatting rules://
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.title NOT LIKE 'Letter to%' AND s.speechtype_id = 2
ORDER BY c.candidate_id, s.speechdate
//**Letter** - A query that finds speeches where the title includes the words letter to, but the type is not letter://
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.title LIKE 'Letter to%' AND s.speechtype_id != 2
ORDER BY c.candidate_id, s.speechdate
//**Issue Position** - A query that finds speeches of type Issue Position, but the title does not reflect our formatting rules://
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechtype_id = 7 AND s.title NOT LIKE '%Issue Position:%'
ORDER BY c.candidate_id, s.speechdate
//**Issue Position** - A query that finds speeches where the title includes the words Issue Position, but the type is not "Issue Position"://
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechtype_id != 7 AND s.title LIKE '%Issue Position:%'
ORDER BY c.candidate_id, s.speechdate
//**Washington, DC** - A query that finds some of the incorrect variations of Washington, DC in the speeches table://
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.location LIKE '%ashingto%' AND s.location LIKE '%D%' AND s.location != 'Washington, DC'
ORDER BY s.location, c.candidate_id, s.speechdate
//**Spanish** - A query that finds speeches which include the words prensa, declaracion, discurso that is, probably written in Spanish:// **Alex Coffman went through the results and fixed 95% of the errors found by this query.**
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechtext LIKE '%discurso%' OR s.speechtext LIKE '%prensa%' OR s.speechtext LIKE '%declaracion%'
ORDER BY c.candidate_id, s.speechdate
//A query that finds speeches where click here (regardless of capitalization) appears in the speechtext://
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechtext ILIKE '%click here%'
ORDER BY c.candidate_id, s.speechdate
//A query that finds speeches lacking a URL://
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.url =
ORDER BY s.speechdate, c.candidate_id
//A query that finds speeches lacking a URL, but has one mentioned in the speech text (could be the source)://
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.url = AND s.speechtext LIKE '%http:%'
ORDER BY s.speechdate
//A query that finds speeches lacking a speechdate://
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechdate IS NULL AND s.speechtype_id != 7
ORDER BY c.candidate_id
//A query that finds speeches by governors between Jan 01 2013 and March 15 2013 (in search of State of the State speeches)://
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id) JOIN office_candidate oc ON (oc.candidate_id = c.candidate_id) JOIN office o ON (o.office_id = oc.office_id)
WHERE o.officetype_id = 'G' AND s.speechtype_id = 1 AND s.speechdate BETWEEN '2013-01-01' AND '2013-03-15'
ORDER BY s.location
//A query that finds speeches with a certain tag, but not a certain category (originally used to find speeches with 'sanctions-on-x' without Trade as a category)://
FROM
(Select s.speech_id, scan.candidate_id, s.title, s.speechdate
FROM speech_category sc JOIN speech s USING (speech_id) JOIN speech_tag st USING (speech_id) JOIN speech_candidate scan USING (speech_id)
WHERE st.tag_id = Y) a
WHERE a.speech_id NOT IN
(Select s.speech_id
FROM speech_category sc JOIN speech s USING (speech_id) JOIN speech_tag st USING (speech_id)
WHERE sc.category_id = X AND st.tag_id = Y)
ORDER BY a.speech_id%%
SpeechesGuide
Useful Research Queries
Speech Research Queries
