mongodb – Kev's Development Toolbox

December 17, 2019December 17, 2019

Revisiting my spotviz.info webapp: visualizing WSJT-X FT8 spots over time – part 7: Redesigning to take advantage of the Cloud

Update following on from part 6.

I completed some of my planned updates recently, in particular moving the AngularJS static content to AWS S3 to serve as a static website, and then also updated AWS Route 53 to point www.spotviz.info to the S3 bucket for this front end content, and then api.spotviz.info is pointing to a VPS running the REST backend. At the same time moving the frontend to S3, I also spent a crazy amount of time migrating to use Webpack to build the frontend, which I covered here.

I’ve now started to pick up some enhancements to the app. The first enhancement is to add a new heatmap to show spots per hour, as a drilldown feature from the heatmap per day. What surprised me at this point is how crazy the original MongoDB query looks now it’s been a couple of years since I was last playing with this. Luckily the update to do counts per hour is only a minor change from the counts per day, so should have that complete soon.

October 8, 2019October 8, 2019

Revisiting my spotviz.info webapp: visualizing WSJT-X FT8 spots over time – part 5: MongoDB Aggregate Queries

Now I have my http://www.spotviz.info app up and running with some collected spot data for testing, one of the first things I want to enhance is the date range selection for visualization, and specifically, adding more data to the heatmap to help you pick a good date range with available data.

The heatmap display I originally added gives you a visual representation of which days have data to view, but it doesn’t help you pick a time range within that day where there’s data. Here’s what the heatmap looks like right now:

The darker the color the more data there is for that day. The problem as it is right now though is that say for one day you ran WSJT-X for 1 hour between 1pm and 2pm and received spots from 2000 stations. Without knowing that there’s only data between 1pm and 2pm, you could select a range of 9am to 6pm for playback, and you’d get nothing displayed on the map until it animated through the 1pm to 2pm block and then a gap of nothing again.

My first enhancement here is to show the earliest and latest signal received times for a given day, to help you pick a good range.

The original MongoDB query (which I discussed here) retrieves a count of spots per hour for a given day:

db.Spot.aggregate( [   
   {$match: {spotter: "kk6dct"}},
   {"$group": {
     "_id": {
       "$subtract": [
         { "$subtract": [ "$spotReceivedTimestamp", new Date("1970-01-01") ] },
         { "$mod": [ { "$subtract": [ "$spotReceivedTimestamp", new Date("1970-01-01") ] }, 1000 * 60 * 60 * 24 ] }
       ]
     },
     count:{$sum: 1 }
   }
 }
])

There’s only one calculated value returned in the results in this query, count, but in order get the earliest and latest spots per day, this is a simple as adding two more calculated values following the return of

count : { $sum : 1 }

so the last part of the query is now:

    count : { $sum : 1 },
    "firstSpot" : { "$min" : "$spotReceivedTimestamp" },
    "lastSpot" : { "$max" : "$spotReceivedTimestamp" },

Done!

October 24, 2017

Loading the Yelp dataset into MongoDB

In a previous post, I downloaded the Yelp dataset, 5.79GB of json data. My first thought (before I get to experimenting with Apache Spark), was how can I extract some basic stats from this dataset, basics like how many data items are there, and what do the records look like in each of the data files.

Using mongoimport and referring to the docs here, the syntax for the import is:

mongoimport -d database -c collection importfile.json

Here’s the Yelp dataset json file for importing, to get an idea of the size of each file:

kev@esxi-ubuntu-mongodb1:~/data/yelp$ ls -lS

total 5657960

-rwxrwxr-x 1 kev kev 3819730722 Oct 14 16:16 review.json

-rwxrwxr-x 1 kev kev 1572537048 Oct 14 16:22 user.json

-rwxrwxr-x 1 kev kev 184892583 Oct 14 16:16 tip.json

-rwxrwxr-x 1 kev kev 132272455 Oct 14 16:03 business.json

-rwxrwxr-x 1 kev kev 60098185 Oct 14 16:03 checkin.json

-rwxrwxr-x 1 kev kev 24195971 Oct 14 16:03 photos.json

So importing each of the datasets, one at a time:

kev@esxi-ubuntu-mongodb1:~/data/yelp$ mongoimport -d yelp -c checkin checkin.json

2017-10-14T16:49:35.566-0700 connected to: localhost

2017-10-14T16:49:38.564-0700 [#########……………] yelp.checkin 22.6MB/57.3MB (39.5%)

2017-10-14T16:49:44.474-0700 [########################] yelp.checkin 57.3MB/57.3MB (100.0%)

2017-10-14T16:49:44.475-0700 imported 135148 documents

kev@esxi-ubuntu-mongodb1:~/data/yelp$ mongoimport -d yelp -c business business.json

2017-10-14T16:49:59.593-0700 connected to: localhost

2017-10-14T16:50:02.592-0700 [#####……………….] yelp.business 27.9MB/126MB (22.1%)

2017-10-14T16:50:12.873-0700 [########################] yelp.business 126MB/126MB (100.0%)

2017-10-14T16:50:12.873-0700 imported 156639 documents

kev@esxi-ubuntu-mongodb1:~/data/yelp$ mongoimport -d yelp -c tip tip.json

2017-10-14T16:50:38.061-0700 connected to: localhost

2017-10-14T16:50:41.058-0700 [##………………….] yelp.tip 17.5MB/176MB (9.9%)

2017-10-14T16:51:07.381-0700 [########################] yelp.tip 176MB/176MB (100.0%)

2017-10-14T16:51:07.381-0700 imported 1028802 documents

kev@esxi-ubuntu-mongodb1:~/data/yelp$ mongoimport -d yelp -c user user.json

2017-10-14T16:51:28.648-0700 connected to: localhost

2017-10-14T16:51:31.648-0700 [……………………] yelp.user 36.9MB/1.46GB (2.5%)

2017-10-14T16:54:15.907-0700 [########################] yelp.user 1.46GB/1.46GB (100.0%)

2017-10-14T16:54:15.907-0700 imported 1183362 documents

kev@esxi-ubuntu-mongodb1:~/data/yelp$ mongoimport -d yelp -c review review.json

2017-10-14T16:57:01.018-0700 connected to: localhost

2017-10-14T16:57:04.016-0700 [……………………] yelp.review 34.9MB/3.56GB (1.0%)

2017-10-14T17:02:31.967-0700 [########################] yelp.review 3.56GB/3.56GB (100.0%)

2017-10-14T17:02:31.967-0700 imported 4736897 documents

Done! Almost 6GB of data imported to MongoDB. Now, time for some queries!