{"id":370,"date":"2012-10-19T23:57:52","date_gmt":"2012-10-20T03:57:52","guid":{"rendered":"http:\/\/www.zhtoolkit.com\/posts\/?p=370"},"modified":"2022-08-14T22:26:54","modified_gmt":"2022-08-15T02:26:54","slug":"lcmc-as-sql-database","status":"publish","type":"post","link":"https:\/\/www.zhtoolkit.com\/posts\/2012\/10\/lcmc-as-sql-database\/","title":{"rendered":"The Lancaster Corpus of Mandarin Chinese as an SQL Database"},"content":{"rendered":"

In my Chinese studies, the Lancaster Corpus of Mandarin Chinese (LCMC)<\/a> has been a useful source of data—word and character frequencies, collocations, phrase usage, parts of speech, etc. The corpus is freely available for non-commercial and research use. However, the native form of its data is in a set of XML files, which is not an easy format to work with. In addition, the XML data is slow to read data from, because all those XML tags and the entire data structure needs to be parsed. A much better format for the data is an SQL database. Stored in a database, many kinds queries and reports can be executed very efficiently. Depending on the software, these queries and reports can return results very quickly, much faster than in the XML format.<\/p>\n

I have made available a Perl script and some other related tools to assist with extracting the LCMC files into a SQLite database. SQLite is a lightweight relational database management system intended for portability and ease of use. Because it functions as a standalone program (not client-server), it is easy to install and use. It’s more ubiquitous than you might think. It’s how the Firefox and Chrome browsers stores its history, cookies, and preferences. But it’s also used, for example, by the Anki program as the storage format for flashcard data, and by the Calibre e-reader program to store information on installed e-books.<\/p>\n

<\/p>\n

Obtaining the Corpus<\/h3>\n

The home page for the LCMC is at http:\/\/www.lancs.ac.uk\/fass\/projects\/corpus\/LCMC\/<\/a>. Of the two links on the page for the distribution locations, the Oxford Text Archive<\/a> link is the only one that allows for direct downloading. Follow the link to the Oxford Text Archive, browse the archives, and search for Lancaster Corpus of Mandarin Chinese<\/em> in the “Corpora” tab. The LCMC is ID 2474<\/a>. The download link<\/a> is directly on the detail page. After reading the Terms and Conditions, enter your email to receive the link to the actual download location. Clicking on the link in the email message will immediately download the file, 2474.zip (6.5MB).<\/p>\n

Creating the SQL Database<\/h3>\n

The scripts can be downloaded as a zip file from here<\/a>, or as individual files in the archive<\/a>. You will need Perl installed on your machine, along with a few necessary modules that may need to be downloaded separately. Full information on the required steps can be found in the README file<\/a>.<\/p>\n

If the data conversion was successful, you will now have a 297 MB file called lcmc.db3 in the current directory.<\/p>\n

\"\"<\/a>
Entity-Relation diagram for the LCMC database<\/figcaption><\/figure>\n

As for client access to the newly created SQLite database, you will need software that can handle Unicode, so that the Chinese characters can be displayed. For Windows, SQLiteSpy<\/a> is my personal choice. Queries are fast, the interface is intuitive, and it has Unicode and support for regular expressions built in. For Linux, both Sqliteman<\/a> and SQLite Manager<\/a> (a Firefox plug-in) are adequate. One common software package for Linux, SQLite Database Browser<\/a> is not<\/strong> suitable, because the LCMC database is large, and the program works by trying to load it all into memory at once.<\/p>\n

\"\"<\/a>
Screenshot of SQLiteSpy for Windows<\/figcaption><\/figure>\n

Some Sample Queries<\/h3>\n

In a relational database, information is organized into separate tables of data and linked by unique keys for each record. This arrangement makes it efficient to perform many kinds of queries. Below are just a few examples of the kind of reports that be done.<\/p>\n

Raw frequency counts of all Chinese words in the corpus<\/h4>\n
SELECT characters, COUNT('x')\r\n  FROM words\r\n WHERE token_type = 'w'\r\n   AND is_cjk = 'Y'\r\nGROUP BY characters ORDER BY count('x') DESC, characters ASC;\r\n<\/code><\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
characters<\/th>\nCOUNT(‘x’)<\/th>\n<\/tr>\n<\/thead>\n
\u7684<\/td>\n51141<\/td>\n<\/tr>\n
\u4e86<\/td>\n12787<\/td>\n<\/tr>\n
\u662f<\/td>\n11601<\/td>\n<\/tr>\n
\u5728<\/td>\n10420<\/td>\n<\/tr>\n
\u4e00<\/td>\n8380<\/td>\n<\/tr>\n
\u548c<\/td>\n7297<\/td>\n<\/tr>\n
\u4ed6<\/td>\n5898<\/td>\n<\/tr>\n
\u4e0d<\/td>\n5687<\/td>\n<\/tr>\n
\u6211<\/td>\n5576<\/td>\n<\/tr>\n
…<\/td>\n…<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
List all words tagged as prepositions<\/h4>\n
SELECT W.characters, P.id, P.description, COUNT(*) from words W\r\n  JOIN pos P on W.part_of_speech = P.id\r\n WHERE P.description = 'preposition'\r\n GROUP BY W.characters, P.id, P.description\r\n ORDER BY COUNT(*) DESC;\r\n<\/code><\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
characters<\/th>\nid<\/th>\nP.description<\/th>\nCOUNT(*)<\/th>\n<\/tr>\n<\/thead>\n
\u5728<\/td>\np<\/td>\npreposition<\/td>\n9899<\/td>\n<\/tr>\n
\u5bf9<\/td>\np<\/td>\npreposition<\/td>\n3155<\/td>\n<\/tr>\n
\u628a<\/td>\np<\/td>\npreposition<\/td>\n1989<\/td>\n<\/tr>\n
\u4ece<\/td>\np<\/td>\npreposition<\/td>\n1782<\/td>\n<\/tr>\n
\u4e3a<\/td>\np<\/td>\npreposition<\/td>\n1702<\/td>\n<\/tr>\n
\u4e0e<\/td>\np<\/td>\npreposition<\/td>\n1432<\/td>\n<\/tr>\n
\u4ee5<\/td>\np<\/td>\npreposition<\/td>\n1355<\/td>\n<\/tr>\n
\u88ab<\/td>\np<\/td>\npreposition<\/td>\n1245<\/td>\n<\/tr>\n
…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
List the average sentence length per text category<\/h4>\n
SELECT T.id, T.type, ROUND(AVG(LENGTH(F.characters)), 1) as avg_sentence_length\r\n  FROM full_sentences F\r\n  JOIN texts T on F.text_id = T.id \r\n GROUP BY T.id, T.type;\r\n<\/code><\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
id<\/th>\ntype<\/th>\navg_sentence_length<\/th>\n<\/tr>\n<\/thead>\n
A<\/td>\nPress reportage<\/td>\n34.0<\/td>\n<\/tr>\n
B<\/td>\nPress editorial<\/td>\n36.6<\/td>\n<\/tr>\n
C<\/td>\nPress review<\/td>\n42.5<\/td>\n<\/tr>\n
D<\/td>\nReligion<\/td>\n41.2<\/td>\n<\/tr>\n
E<\/td>\nSkills, trades and hobbies<\/td>\n32.5<\/td>\n<\/tr>\n
F<\/td>\nPopular lore<\/td>\n33.0<\/td>\n<\/tr>\n
G<\/td>\nBiographies and essays<\/td>\n31.0<\/td>\n<\/tr>\n
H<\/td>\nMiscellaneous (reports, official documents)<\/td>\n42.3<\/td>\n<\/tr>\n
J<\/td>\nScience (academic prose)<\/td>\n40.3<\/td>\n<\/tr>\n
K<\/td>\nGeneral fiction<\/td>\n24.3<\/td>\n<\/tr>\n
L<\/td>\nMystery and detective fiction<\/td>\n28.4<\/td>\n<\/tr>\n
M<\/td>\nScience fiction<\/td>\n33.4<\/td>\n<\/tr>\n
N<\/td>\nMartial art fiction<\/td>\n28.6<\/td>\n<\/tr>\n
P<\/td>\nRomantic fiction<\/td>\n25.8<\/td>\n<\/tr>\n
R<\/td>\nHumour<\/td>\n20.8<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
List usage of words containing the character  \u7ed9<\/h4>\n
SELECT W.characters, P.id, P.description, count(*) from words W\r\n  JOIN pos P on W.part_of_speech = P.id\r\nWHERE W.characters LIKE '%\u7ed9%'\r\n GROUP BY W.characters, P.id, P.description\r\n ORDER BY COUNT(*) DESC;\r\n<\/code><\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
characters<\/th>\nid<\/th>\ndescription<\/th>\ncount(*)<\/th>\n<\/tr>\n<\/thead>\n
\u7ed9<\/td>\np<\/td>\npreposition<\/td>\n985<\/td>\n<\/tr>\n
\u7ed9<\/td>\nv<\/td>\nverb<\/td>\n149<\/td>\n<\/tr>\n
\u7ed9\u4e88<\/td>\nv<\/td>\nverb<\/td>\n92<\/td>\n<\/tr>\n
\u4ea4\u7ed9<\/td>\nv<\/td>\nverb<\/td>\n54<\/td>\n<\/tr>\n
\u9001\u7ed9<\/td>\nv<\/td>\nverb<\/td>\n37<\/td>\n<\/tr>\n
\u9012\u7ed9<\/td>\nv<\/td>\nverb<\/td>\n19<\/td>\n<\/tr>\n
\u4f9b\u7ed9<\/td>\nvn<\/td>\nverb with nominal function<\/td>\n16<\/td>\n<\/tr>\n
\u53d1\u7ed9<\/td>\nv<\/td>\nverb<\/td>\n15<\/td>\n<\/tr>\n
…<\/td>\n…<\/td>\n…<\/td>\n\n<\/tr>\n<\/tbody>\n<\/table>\n
All words and corresponding pinyin<\/h4>\n
select W.file_id, W.sentence_id, W.word_num, W.characters, P.characters AS pinyin from words W\r\nleft join pinyin_words P USING (file_id, sentence_id, word_num);\r\n<\/code><\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
file_id<\/th>\nsentence_id<\/th>\nword_num<\/th>\ncharacters<\/th>\npinyin<\/th>\n<\/tr>\n<\/thead>\n
A01<\/td>\n0001<\/td>\n1<\/td>\n\u5927<\/td>\nda4<\/td>\n<\/tr>\n
A01<\/td>\n0001<\/td>\n2<\/td>\n\u5899<\/td>\nqiang2<\/td>\n<\/tr>\n
A01<\/td>\n0001<\/td>\n3<\/td>\n\u5185\u5916<\/td>\nnei4wai4<\/td>\n<\/tr>\n
A01<\/td>\n0001<\/td>\n4<\/td>\n\uff0d\uff0d<\/td>\n\uff0d\uff0d<\/td>\n<\/tr>\n
A01<\/td>\n0001<\/td>\n5<\/td>\n\u5317\u4eac\u5e02<\/td>\nbei3jing1shi4<\/td>\n<\/tr>\n
A01<\/td>\n0001<\/td>\n6<\/td>\n\u76d1\u72f1<\/td>\njian1yu4<\/td>\n<\/tr>\n
A01<\/td>\n0001<\/td>\n7<\/td>\n\u7eaa\u5b9e<\/td>\nji4shi2<\/td>\n<\/tr>\n
A01<\/td>\n0001<\/td>\n8<\/td>\n\uff08<\/td>\n\uff08<\/td>\n<\/tr>\n
A01<\/td>\n0001<\/td>\n9<\/td>\n\u4e09<\/td>\nsan1<\/td>\n<\/tr>\n
A01<\/td>\n0001<\/td>\n10<\/td>\n\uff09<\/td>\n\uff09<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Pinyin usage of character \u4e50<\/h4>\n
  select W.character, P.character AS pinyin, count(*)\r\n    from characters W\r\n    join pinyin_characters P USING (file_id, sentence_id, word_num, char_num)\r\n    where W.character = '\u4e50'\r\ngroup by W.character, P.character;\r\n<\/code><\/pre>\n\n\n\n\n\n\n
character<\/th>\npinyin<\/th>\ncount(*)<\/th>\n<\/tr>\n<\/thead>\n
\u4e50<\/td>\nle4<\/td>\n270<\/td>\n<\/tr>\n
\u4e50<\/td>\nyue4<\/td>\n104<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
These are just a few examples of what you can do with the corpus data in the SQL database. Have fun!<\/p>\n
http:\/\/www.zhtoolkit.com\/apps\/small_utilities\/lcmc_xml_to_sqlite.zip<\/a> (69Kb)<\/p>\n","protected":false},"excerpt":{"rendered":"
In my Chinese studies, the Lancaster Corpus of Mandarin Chinese (LCMC) has been a useful source of data—word and character frequencies, collocations, phrase usage, parts of speech, etc. The corpus is freely available for non-commercial and research use. However, the native form of its data is in a set of XML files, which is not […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"ocean_post_layout":"","ocean_both_sidebars_style":"","ocean_both_sidebars_content_width":0,"ocean_both_sidebars_sidebars_width":0,"ocean_sidebar":"","ocean_second_sidebar":"","ocean_disable_margins":"enable","ocean_add_body_class":"","ocean_shortcode_before_top_bar":"","ocean_shortcode_after_top_bar":"","ocean_shortcode_before_header":"","ocean_shortcode_after_header":"","ocean_has_shortcode":"","ocean_shortcode_after_title":"","ocean_shortcode_before_footer_widgets":"","ocean_shortcode_after_footer_widgets":"","ocean_shortcode_before_footer_bottom":"","ocean_shortcode_after_footer_bottom":"","ocean_display_top_bar":"default","ocean_display_header":"default","ocean_header_style":"","ocean_center_header_left_menu":"","ocean_custom_header_template":"","ocean_custom_logo":0,"ocean_custom_retina_logo":0,"ocean_custom_logo_max_width":0,"ocean_custom_logo_tablet_max_width":0,"ocean_custom_logo_mobile_max_width":0,"ocean_custom_logo_max_height":0,"ocean_custom_logo_tablet_max_height":0,"ocean_custom_logo_mobile_max_height":0,"ocean_header_custom_menu":"","ocean_menu_typo_font_family":"","ocean_menu_typo_font_subset":"","ocean_menu_typo_font_size":0,"ocean_menu_typo_font_size_tablet":0,"ocean_menu_typo_font_size_mobile":0,"ocean_menu_typo_font_size_unit":"px","ocean_menu_typo_font_weight":"","ocean_menu_typo_font_weight_tablet":"","ocean_menu_typo_font_weight_mobile":"","ocean_menu_typo_transform":"","ocean_menu_typo_transform_tablet":"","ocean_menu_typo_transform_mobile":"","ocean_menu_typo_line_height":0,"ocean_menu_typo_line_height_tablet":0,"ocean_menu_typo_line_height_mobile":0,"ocean_menu_typo_line_height_unit":"","ocean_menu_typo_spacing":0,"ocean_menu_typo_spacing_tablet":0,"ocean_menu_typo_spacing_mobile":0,"ocean_menu_typo_spacing_unit":"","ocean_menu_link_color":"","ocean_menu_link_color_hover":"","ocean_menu_link_color_active":"","ocean_menu_link_background":"","ocean_menu_link_hover_background":"","ocean_menu_link_active_background":"","ocean_menu_social_links_bg":"","ocean_menu_social_hover_links_bg":"","ocean_menu_social_links_color":"","ocean_menu_social_hover_links_color":"","ocean_disable_title":"default","ocean_disable_heading":"default","ocean_post_title":"","ocean_post_subheading":"","ocean_post_title_style":"","ocean_post_title_background_color":"","ocean_post_title_background":0,"ocean_post_title_bg_image_position":"","ocean_post_title_bg_image_attachment":"","ocean_post_title_bg_image_repeat":"","ocean_post_title_bg_image_size":"","ocean_post_title_height":0,"ocean_post_title_bg_overlay":0.5,"ocean_post_title_bg_overlay_color":"","ocean_disable_breadcrumbs":"default","ocean_breadcrumbs_color":"","ocean_breadcrumbs_separator_color":"","ocean_breadcrumbs_links_color":"","ocean_breadcrumbs_links_hover_color":"","ocean_display_footer_widgets":"default","ocean_display_footer_bottom":"default","ocean_custom_footer_template":"","ocean_post_oembed":"","ocean_post_self_hosted_media":"","ocean_post_video_embed":"","ocean_link_format":"","ocean_link_format_target":"self","ocean_quote_format":"","ocean_quote_format_link":"post","ocean_gallery_link_images":"on","ocean_gallery_id":[],"footnotes":""},"categories":[4,5],"tags":[11,9,13,33,28,27,12,14],"class_list":["post-370","post","type-post","status-publish","format-standard","hentry","category-guides","category-tools","tag-corpus","tag-howto","tag-lcmc","tag-software","tag-sql","tag-tools-2","tag-word-frequency","tag-words","entry"],"_links":{"self":[{"href":"https:\/\/www.zhtoolkit.com\/posts\/wp-json\/wp\/v2\/posts\/370","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.zhtoolkit.com\/posts\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.zhtoolkit.com\/posts\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.zhtoolkit.com\/posts\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.zhtoolkit.com\/posts\/wp-json\/wp\/v2\/comments?post=370"}],"version-history":[{"count":12,"href":"https:\/\/www.zhtoolkit.com\/posts\/wp-json\/wp\/v2\/posts\/370\/revisions"}],"predecessor-version":[{"id":436,"href":"https:\/\/www.zhtoolkit.com\/posts\/wp-json\/wp\/v2\/posts\/370\/revisions\/436"}],"wp:attachment":[{"href":"https:\/\/www.zhtoolkit.com\/posts\/wp-json\/wp\/v2\/media?parent=370"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.zhtoolkit.com\/posts\/wp-json\/wp\/v2\/categories?post=370"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.zhtoolkit.com\/posts\/wp-json\/wp\/v2\/tags?post=370"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}