Changeset 33814 for other-projects/maori-lang-detection/hdfs-cc-work/GS_README.TXT

Timestamp:

2019-12-19T17:13:26+13:00 (4 years ago)

Author:

ak19

Message:

Put the important mongodb queries and results into hdfs-cc-work/GS_README.txt, except for the aggregate queries, where the results are in mongodb-data/counts*.json files committed to svn yesterday and from which geojson maps were produced.

File:

• other-projects/maori-lang-detection/hdfs-cc-work/GS_README.TXT (modified) (1 diff)

other-projects/maori-lang-detection/hdfs-cc-work/GS_README.TXT

@@ -693 +693 @@
 6. Launch the Robo 3T (version 1.3 is one we tested) MongoDB client. Use it to connect to MongoDB's "ateacrawldata" database.
 Now you can run queries.
+
+
+Here are most of the important MongoDB queries I ran, and the shorter answers.
+# Num websites
+db.getCollection('Websites').find({}).count()
+1445 
+
+# Num webpages
+db.getCollection('Webpages').find({}).count()
+117496
+
+# Find number of websites that have 1 or more pages detected as being in Maori (a positive numPagesInMRI)
+db.getCollection('Websites').find({numPagesInMRI: { $gt: 0}}).count()
+361
+
+# Number of sites containing at least one sentence for which OpenNLP detected the best language = MRI
+db.getCollection('Websites').find({numPagesContainingMRI: {$gt: 0}}).count()
+868
+
+# Obviously, the union of the above two will be identical to numPagesContainingMRI:
+db.getCollection('Websites').find({ $or: [ { numPagesInMRI: { $gt: 0 } }, { numPagesContainingMRI: {$gt: 0} } ] } ).count()
+868
+
+# Find number of webpages that are deemed to be overall in MRI (pages where isMRI=true)
+db.getCollection('Webpages').find({isMRI:true}).count()
+7818
+
+# Number of pages that contain any number of MRI sentences
+db.getCollection('Webpages').find({containsMRI: true}).count()
+20371
+
+
+# Number of sites with URLs containing /mi(/)
+db.getCollection('Websites').find({urlContainsLangCodeInPath:true}).count()
+X 153
+# Number of sites with URLs containing /mi(/) OR http(s)://mi.*
+db.getCollection('Websites').find({urlContainsLangCodeInPath:true}).count()
+670
+
+# Number of websites that are outside NZ that contain /mi(/) in any of its sub-urls
+db.getCollection('Websites').find({urlContainsLangCodeInPath:true, geoLocationCountryCode: {$ne : "NZ"} }).count()
+X 147
+# Number of websites that are outside NZ that contain /mi(/) OR http(s)://mi.* in any of its sub-urls
+db.getCollection('Websites').find({urlContainsLangCodeInPath:true, geoLocationCountryCode: {$ne : "NZ"} }).count()
+656
+
+# 6 sites with URLs containing /mi(/) that are in NZ
+db.getCollection('Websites').find({urlContainsLangCodeInPath:true, geoLocationCountryCode: "NZ"}).count()
+X 6
+# 14 sites with URLs containing /mi(/) OR http(s)://mi.* that are in NZ
+14
+
+PROJECTION QUERIES:
+# For all the sites that do not originate in NZ, list their country codes (geoLocationCountryCode
+# field) and the urlContainsLangCodeInPath field
+
+db.getCollection('Websites').find({geoLocationCountryCode: {$ne:"nz"}}, {geoLocationCountryCode:1, urlContainsLangCodeInPath: 1})
+
+
+AGGREGATION QUERIES - the results of important aggregate queries here
+can be found in the associated mongodb-data/counts*.json files.
+
+# count of country codes for all sites
+db.Websites.aggregate([
+   
+    { $unwind: "$geoLocationCountryCode" },
+    {
+        $group: {
+            _id: "$geoLocationCountryCode",
+            count: { $sum: 1 }
+        }
+    },
+    { $sort : { count : -1} }
+]);
+
+# count of country codes for sites that have at least one page detected as MRI 
+
+db.Websites.aggregate([
+    {
+        $match: {
+            numPagesInMRI: {$gt: 0}
+        }
+    },
+    { $unwind: "$geoLocationCountryCode" },
+    {
+        $group: {
+            _id: {$toLower: '$geoLocationCountryCode'},
+            count: { $sum: 1 }
+        }
+    },
+    { $sort : { count : -1} }
+]);
+
+# count of country codes for sites that have at least one page containing at least one sentence detected as MRI 
+db.Websites.aggregate([
+    {
+        $match: {
+            numPagesContainingMRI: {$gt: 0}
+        }
+    },
+    { $unwind: "$geoLocationCountryCode" },
+    {
+        $group: {
+            _id: {$toLower: '$geoLocationCountryCode'},
+            count: { $sum: 1 }
+        }
+    },
+    { $sort : { count : -1} }
+]);
+
+
+# ATTEMPT TO FILTER OUT LIKELY AUTO-TRANSLATED SITES
+# Get a count of all non-NZ (or .nz TLD) sites that don't have /mi(/) or http(s)://mi.*
+# in the URL path of any crawled web pages of the site
+db.getCollection('Websites').find(
+{$and: [
+    {numPagesContainingMRI: {$gt: 0}},
+    {geoLocationCountryCode: {$ne: "NZ"}},
+    {domain: {$not: /.nz$/}},
+    {urlContainsLangCodeInPath: {$ne: true}}
+]}).count()
+
+220
+
+# Aggregate: count by country codes of non-NZ related sites that
+# don't have the language code in the URL path on any crawled pages of the site
+
+db.Websites.aggregate([
+    {
+        $match: {
+            $and: [
+            {numPagesContainingMRI: {$gt: 0}},
+            {geoLocationCountryCode: {$ne: "NZ"}},
+            {domain: {$not: /.nz$/}},
+            {urlContainsLangCodeInPath: {$ne: true}}
+            ]
+        }
+    },
+    { $unwind: "$geoLocationCountryCode" },
+    {
+        $group: {
+            _id: {$toLower: '$geoLocationCountryCode'},
+            count: { $sum: 1 },
+            domain: { $addToSet: '$domain' }
+        }
+    },
+    { $sort : { count : -1} }
+]);
+
+The above query contains "domain: { $addToSet: '$domain' }"
+which adds the list of matching domains for each country code
+to the output of the aggregate result list.
+This is useful as I'll be inspecting these manually to ensure they're not
+auto-translated to further reduce the list if necessary.
+
+For each resulting domain, I can then inspect that website's pages in the Webpages
+mongodb collection for whether those pages are relevant or auto-translated with a query
+of the following form. This example works with the sample site URL https://www.lexilogos.com
+
+    db.getCollection('Webpages').find({URL:/lexilogos\.com/, mriSentenceCount: {$gt: 0}})
+
+
+In inspecting Australian sites in the result list, I noticed that one that should not be
+excluded from the output was https://www.kiwiproperty.com. The TLD is not .nz,
+and the site originates in Australia, not NZ, but it's still a site of NZ content.
+This will be an important consideration when constructing some aggregate queries further below.
+
+
+# Count of websites that have at least 1 page containing at least one sentence detected as MRI
+# AND which websites have mi in the URL path:
+
+db.getCollection('Websites').find({$and: [{numPagesContainingMRI: {$gt: 0}},{urlContainsLangCodeInPath: true}]}).count()
+
+491
+
+
+# The websites that have some MRI detected AND which are either in NZ or with NZ TLD
+# or (so if they're from overseas) don't contain /mi or mi.* in URL path:
+
+db.getCollection('Websites').find({$and: [{numPagesContainingMRI: {$gt: 0}},{$or: [{geoLocationCountryCode: "NZ"}, {domain: /\.nz$/}, {urlContainsLangCodeInPath: false}]}]}).count()
+396
+
+Include Australia, to get the valid "kiwiproperty.com" website included in the result list:
+
+db.getCollection('Websites').find({$and: [
+                {numPagesContainingMRI: {$gt: 0}},
+                {$or: [{geoLocationCountryCode: /(NZ|AU)/}, {domain: /\.nz$/}, {urlContainsLangCodeInPath: false}]}
+            ]}).count()
+
+397
+
+# aggregate results by a count of country codes
+db.Websites.aggregate([
+    {
+        $match: {
+            $and: [
+                {numPagesContainingMRI: {$gt: 0}},
+                {$or: [{geoLocationCountryCode: /(NZ|AU)/}, {domain: /\.nz$/}, {urlContainsLangCodeInPath: false}]}
+            ]
+        }
+    },
+    { $unwind: "$geoLocationCountryCode" },
+    {
+        $group: {
+            _id: {$toLower: '$geoLocationCountryCode'},
+            count: { $sum: 1 }
+        }
+    },
+    { $sort : { count : -1} }
+]);
+
+
+# Just considering those sites outside NZ or not with .nz TLD:
+
+db.getCollection('Websites').find({$and: [
+                {geoLocationCountryCode: {$ne: "NZ"}},
+                {domain: {$not: /\.nz/}},
+                {numPagesContainingMRI: {$gt: 0}},
+                {$or: [{geoLocationCountryCode: "AU"}, {urlContainsLangCodeInPath: false}]}
+            ]}).count()
+
+221 websites
+
+# counts by country code excluding NZ related sites
+db.Websites.aggregate([
+    {
+        $match: {
+            $and: [
+                {geoLocationCountryCode: {$ne: "NZ"}},
+                {domain: {$not: /\.nz/}},
+                {numPagesContainingMRI: {$gt: 0}},
+                {$or: [{geoLocationCountryCode: "AU"}, {urlContainsLangCodeInPath: false}]}            
+            ]
+        }
+    },
+    { $unwind: "$geoLocationCountryCode" },
+    {
+        $group: {
+            _id: {$toLower: '$geoLocationCountryCode'},
+            count: { $sum: 1 },
+            domain: { $addToSet: '$domain' }
+        }
+    },
+    { $sort : { count : -1} }
+]);
+
+
+# But to produce the tentative non-product sites, we also want the aggregate for all NZ sites (from NZ or with .nz tld):
+db.getCollection('Websites').find({$and: [
+                {numPagesContainingMRI: {$gt: 0}},
+                {$or: [{geoLocationCountryCode:"NZ"},{domain: /\.nz/}]}
+            ]}).count()
+
+176
+
+(Total is 221+176 = 397, which adds up).
+
+# Get the count (and domain listing) output put under a hardcoded  _id of "nz":
+db.Websites.aggregate([
+    {
+        $match: {
+            $and: [
+                {numPagesContainingMRI: {$gt: 0}},
+                {$or: [{geoLocationCountryCode:"NZ"},{domain: /\.nz/}]}
+            ]
+        }
+    },
+    { $unwind: "$geoLocationCountryCode" },
+    {
+        $group: {
+            _id: "nz",
+            count: { $sum: 1 },
+            domain: { $addToSet: '$domain' }
+        }
+    },
+    { $sort : { count : -1} }
+]);
 
 --------------------------------------------------------