Changeset 33710 for other-projects

Timestamp:

2019-11-20T23:23:29+13:00 (4 years ago)

Author:

ak19

Message:

Working queries and map coords for geojson.tools (ironically, Lat and Lng are different from Google maps).

File:

• other-projects/maori-lang-detection/MoreReading/mongodb.txt (modified) (2 diffs)

other-projects/maori-lang-detection/MoreReading/mongodb.txt

@@ -417 +417 @@
 
 https://docs.mongodb.com/manual/aggregation/
+EXAMPLE:
 db.orders.aggregate([
    { $match: { status: "A" } },
    { $group: { _id: "$cust_id", total: { $sum: "$amount" } } }
 ])
-db.Websites.aggregate({ $match:{urlContainsLangCodeInpath:true}}, $group: {geoLocationCountryCode:1, total: $count})
+
+X db.Websites.aggregate([{ $match:{urlContainsLangCodeInPath:true}}, $group: {geoLocationCountryCode:1, total: $count}])
+
+
+X db.Websites.aggregate([
+    { $match:{urlContainsLangCodeInPath:true}},
+    {$group: {geoLocationCountryCode:1}}
+])
+
+WORKS (but an "unwind" will get rid of "null"):
+db.Websites.aggregate([
+    { $match:{urlContainsLangCodeInPath:true}},
+    {$group: {_id: "$geoLocationCountryCode", count: {$sum: 1}}},
+        { $sort : { count : -1} }
+])
+
+
+# COUNT OF ALL GEOLOCATION COUNTRIES
+#https://stackoverflow.com/questions/14924495/mongodb-count-num-of-distinct-values-per-field-key
+    # LIST
+    db.Websites.distinct('geoLocationCountryCode');
+    
+    # COUNT
+    db.Websites.distinct('geoLocationCountryCode').length;
+
+    # A COUNT WITH QUERY - https://docs.mongodb.com/manual/reference/command/distinct/#dbcmd.distinct
+
+    db.runCommand ( { distinct: "Websites", key: "geoLocationCountryCode", query: { "urlContainsLangCodeInPath": true} } );
+
+    # DISTINCT WITH QUERY WITHOUT COUNT - https://docs.mongodb.com/manual/reference/method/db.collection.distinct/
+    db.Websites.distinct('geoLocationCountryCode', {"urlContainsLangCodeInPath": true});
+
+        #SORTED - https://stackoverflow.com/questions/4759437/get-distinct-values-with-sorted-data
+        db.Websites.distinct('geoLocationCountryCode', {"urlContainsLangCodeInPath": true}).sort();
+
+
+# AGGREGATION QUERIES THAT WORK:
+#https://stackoverflow.com/questions/14924495/mongodb-count-num-of-distinct-values-per-field-key
+
+db.Websites.aggregate([
+    {
+        $match: {
+            urlContainsLangCodeInPath: true
+        }
+    },
+    { $unwind: "$geoLocationCountryCode" },
+    {
+        $group: {
+            _id: {$toLower: '$geoLocationCountryCode'},
+            count: { $sum: 1 }
+        }
+    },
+    { $sort : { count : -1} },
+    { $limit : 100 }
+]);
+
+
+WORKS:
+db.Websites.aggregate([
+    {
+        $match: {
+            geoLocationCountryCode: {$ne : "UNKNOWN"}
+        }
+    },
+    { $unwind: "$geoLocationCountryCode" },
+    {
+        $group: {
+            _id: "$geoLocationCountryCode",
+            count: { $sum: 1 }
+        }
+    },
+    { $sort : { count : -1} },
+    { $limit : 100 }
+]);
+
+WORKS:
+db.Websites.aggregate([
+    {
+        $match: {
+            "urlContainsLangCodeInPath": true
+        }
+    },
+    { $unwind: "$geoLocationCountryCode" },
+    {
+        $group: {
+            _id: "$geoLocationCountryCode",
+            count: { $sum: 1 }
+        }
+    },
+    { $sort : { count : -1} },
+    { $limit : 100 }
+]);
+
+
+KEEP ADDITIONAL FIELDS - https://stackoverflow.com/questions/16662405/mongo-group-query-how-to-keep-fields:
+    a. KEEPS ONLY FIRST DOMAIN URL FOR EACH COUNTED COUNTRY CODE:
+
+    db.Websites.aggregate([
+        {
+        $match: {
+            "urlContainsLangCodeInPath": true
+        }
+        },
+        { $unwind: "$geoLocationCountryCode" },
+        {
+        $group: {
+            _id: "$geoLocationCountryCode", count: { $sum: 1 },
+            domain: {$first: '$domain'}
+        }
+        },
+        { $sort : { count : -1} }
+    ]);
+
+    b. KEEP ALL DOMAIN URLS:
+    db.Websites.aggregate([
+        {
+        $match: {
+            "urlContainsLangCodeInPath": true
+        }
+        },
+        { $unwind: "$geoLocationCountryCode" },
+        {
+        $group: {
+            _id: "$geoLocationCountryCode",
+            count: { $sum: 1 },
+            domain: { $addToSet: '$domain' }
+        }
+        },
+        { $sort : { count : -1} }
+    ]);
+
+
+# WANT TO GET THE ABOVE INTO WORLD MAP, use geojson.tools found by Dr Bainbridge
+geojson.tools
+USAGE: https://www.here.xyz/viewer-tool/
 
 
@@ -435 +570 @@
 docs
 
+
+
+
+/* 1 */
+{
+    "_id" : "US",
+    "count" : 93.0,
+    -95.8,40.33
+}
+
+/* 2 */
+{
+    "_id" : "AU",
+    "count" : 7.0,
+    135.8,-25.33
+}
+
+/* 3 */
+{
+    "_id" : "CN",
+    "count" : 7.0,
+ 100.8,
+      32.33
+}
+
+/* 4 */
+{
+    "_id" : "NZ",
+    "count" : 5.0,
+175.8,
+      -40.33
+}
+
+/* 5 */
+{
+    "_id" : "DE",
+    "count" : 5.0,
+10.8,
+      50.33
+}
+
+/* 6 */
+{
+    "_id" : "HK",
+    "count" : 5.0,
+114,
+      22.33
+}
+
+/* 7 */
+{
+    "_id" : "RU",
+    "count" : 4.0,
+38.4,
+      55.5
+}
+
+/* 8 */
+{
+    "_id" : "JP",
+    "count" : 3.0,
+      137.8,
+      36
+}
+
+/* 9 */
+{
+    "_id" : "GB",
+    "count" : 3.0,
+-2,
+      53.33
+}
+
+/* 10 */
+{
+    "_id" : "CA",
+    "count" : 2.0,
+      -105.8,
+      55.33
+}
+
+/* 11 */
+{
+    "_id" : "FR",
+    "count" : 2.0,
+      3,
+      47.33
+}
+
+/* 12 */
+{
+    "_id" : "DK",
+    "count" : 2.0,
+ 9.5,
+      55.33
+}
+
+/* 13 British Virgin Islands */
+{
+    "_id" : "VG",
+    "count" : 2.0,
+ -64.8,
+      18.35
+}
+
+/* 14 Ukraine */
+{
+    "_id" : "UA",
+    "count" : 1.0,
+      31.5,
+      48.5
+}
+
+/* 15 */
+{
+    "_id" : "CZ",
+    "count" : 1.0,
+      16.2,
+      49.7
+}
+
+/* 16 Switzerland */
+{
+    "_id" : "CH",
+    "count" : 1.0,
+      8.5,
+      47
+}
+
+/* 17 Zuid-Afrika */
+{
+    "_id" : "ZA",
+    "count" : 1.0,
+      24.2,
+      -30.7
+}
+
+/* 18 */
+{
+    "_id" : "NL",
+    "count" : 1.0,
+5.8,
+      52.33
+}
+
+/* 19 */
+{
+    "_id" : "KR",
+    "count" : 1.0,
+      127.8,
+      36.8
+}
+
+
+/** http://geojson.tools/
+
+
+{
+  "type": "MultiPoint",
+  "coordinates": [
+    [
+      -95.8,
+      40.33
+    ],
+    [
+      135.8,
+      -25.33
+    ],
+    [
+      100.8,
+      32.33
+    ],
+    [
+      175.8,
+      -40.33
+    ],
+    [
+      10.8,
+      50.33
+    ],
+    [
+      10.8,
+      50.33
+    ],
+    [
+      114,
+      22.33
+    ],
+    [
+      38.4,
+      55.5
+    ],
+    [
+      -2,
+      53.33
+    ],
+    [
+      137.8,
+      36
+    ],
+    [
+      -105.8,
+      55.33
+    ],
+    [
+      3,
+      47.33
+    ],
+    [
+      9.5,
+      55.33
+    ],
+    [
+      -64.8,
+      18.35
+    ],
+    [
+      31.5,
+      48.5
+    ],
+    [
+      16.2,
+      49.7
+    ],
+    [
+      8.5,
+      47
+    ],
+    [
+      24.2,
+      -30.7
+    ],
+    [
+      5.8,
+      52.33
+    ],
+    [
+      127.8,
+      36.8
+    ]
+  ]
+}
+
+*/
+
+/* 1 */
+{
+    "_id" : "US",
+    "count" : 93.0,
+    -95.8,40.33
+}
+
+/* 2 */
+{
+    "_id" : "AU",
+    "count" : 7.0,
+    135.8,-25.33
+}
+
+/* 3 */
+{
+    "_id" : "CN",
+    "count" : 7.0,
+ 100.8,
+      32.33
+}
+
+/* 4 */
+{
+    "_id" : "NZ",
+    "count" : 5.0,
+175.8,
+      -40.33
+}
+
+/* 5 */
+{
+    "_id" : "DE",
+    "count" : 5.0,
+10.8,
+      50.33
+}
+
+/* 6 */
+{
+    "_id" : "HK",
+    "count" : 5.0,
+114,
+      22.33
+}
+
+/* 7 */
+{
+    "_id" : "RU",
+    "count" : 4.0,
+38.4,
+      55.5
+}
+
+/* 8 */
+{
+    "_id" : "JP",
+    "count" : 3.0,
+      137.8,
+      36
+}
+
+/* 9 */
+{
+    "_id" : "GB",
+    "count" : 3.0,
+-2,
+      53.33
+}
+
+/* 10 */
+{
+    "_id" : "CA",
+    "count" : 2.0,
+      -105.8,
+      55.33
+}
+
+/* 11 */
+{
+    "_id" : "FR",
+    "count" : 2.0,
+      3,
+      47.33
+}
+
+/* 12 */
+{
+    "_id" : "DK",
+    "count" : 2.0,
+ 9.5,
+      55.33
+}
+
+/* 13 British Virgin Islands */
+{
+    "_id" : "VG",
+    "count" : 2.0,
+ -64.8,
+      18.35
+}
+
+/* 14 Ukraine */
+{
+    "_id" : "UA",
+    "count" : 1.0,
+      31.5,
+      48.5
+}
+
+/* 15 */
+{
+    "_id" : "CZ",
+    "count" : 1.0,
+      16.2,
+      49.7
+}
+
+/* 16 Switzerland */
+{
+    "_id" : "CH",
+    "count" : 1.0,
+      8.5,
+      47
+}
+
+/* 17 Zuid-Afrika */
+{
+    "_id" : "ZA",
+    "count" : 1.0,
+      24.2,
+      -30.7
+}
+
+/* 18 */
+{
+    "_id" : "NL",
+    "count" : 1.0,
+5.8,
+      52.33
+}
+
+/* 19 */
+{
+    "_id" : "KR",
+    "count" : 1.0,
+      127.8,
+      36.8
+}
+
+
+/** http://geojson.tools/
+
+
+{
+  "type": "MultiPoint",
+  "coordinates": [
+    [
+      -95.8,
+      40.33
+    ],
+    [
+      135.8,
+      -25.33
+    ],
+    [
+      100.8,
+      32.33
+    ],
+    [
+      175.8,
+      -40.33
+    ],
+    [
+      10.8,
+      50.33
+    ],
+    [
+      10.8,
+      50.33
+    ],
+    [
+      114,
+      22.33
+    ],
+    [
+      38.4,
+      55.5
+    ],
+    [
+      -2,
+      53.33
+    ],
+    [
+      137.8,
+      36
+    ],
+    [
+      -105.8,
+      55.33
+    ],
+    [
+      3,
+      47.33
+    ],
+    [
+      9.5,
+      55.33
+    ],
+    [
+      -64.8,
+      18.35
+    ],
+    [
+      31.5,
+      48.5
+    ],
+    [
+      16.2,
+      49.7
+    ],
+    [
+      8.5,
+      47
+    ],
+    [
+      24.2,
+      -30.7
+    ],
+    [
+      5.8,
+      52.33
+    ],
+    [
+      127.8,
+      36.8
+    ]
+  ]
+}
+
+*/