Skip to content
Studio 3T - The professional GUI, IDE and client for MongoDB
  • Tools
    • Aggregation Editor
    • IntelliShell
    • Visual Query Builder
    • Export Wizard
    • Import Wizard
    • Query Code
    • SQL Query
    • Connect
    • Schema Explorer
    • Compare
    • SQL ⇔ MongoDB Migration
    • Data Masking
    • Task Scheduler
    • Reschema
    • More Tools and Features
  • Solutions
  • Resources
    • Knowledge Base
    • MongoDB Tutorials & Courses
    • Tool/Feature Documentation
    • Reports
    • Case Studies
    • Whitepapers
    • Blog
    • Testimonials
    • Community
  • Contact us
    • Contact
    • Sales Support
    • Feedback and Support
    • Career
    • About Us
  • Store
    • Buy Now
    • Preferred Resellers
    • Team Pricing
  • My License
  • Download
search

Studio 3T Knowledge Base

  • Documentation
  • Tutorials
  • Workshops
Take the fastest route to learning MongoDB. Cover the basics in two hours with MongoDB 101, no registration required.
Start the free course

From Data to Maps: Rendering MongoDB GeoJSON with Studio 3T – Part 1

Posted on: 22/07/2021 (last updated: 04/08/2021) by DJ Walker-Morgan

In this workshop, we’re going to take a dataset and turn it into an interactive map that you can host pretty much anywhere. On the way, we’ll show how you can use MongoDB aggregations to filter the data, how Studio 3T helps compose those aggregations and export the results, ready to view. By the time we are finished, we’ll see Great Britain like this:

Getting our data

What are we going to map? Well, everyone loves a pub and these days pubs need all the love they can get. If only there was a dataset which could tell us where all the good pubs were in all the various locales of Great Britain. Turns out there is; open-pubs. This is curated by the fine people at GetTheData who take public data sets and make them available with postcode information.

We’ll create a database for this exercise, ‘pubs’ and then download the open-pubs data.

Now, the open-pubs data is in CSV format, so let’s head to Studio 3T’s Import tool and select CSV import. The raw data lacks field names, but that is not a problem. Select Target Options on the import and you can assign field names and field types. The names come from the open-pubs page and the types are all strings, apart from easting and northing (integers) and latitude and longitude (decimal).

Configuring CSV Import for our Pubs data

We’re importing it into a collection called ‘open_pubs’. Running that import should get you around 51,167 records.

Learn more about importing data into MongoDB with Studio 3T

Rating our data

Our data set would, ideally, have a rating with each pub, but unfortunately it doesn’t. There’s a couple of options to fix this. First, create some random dummy ratings, second, gather ratings from around the web. Thirdly, visit all 51,167 pubs and rate them. For convenience and speed, we are going to use the first option. The third option is left as an exercise for the reader.

Open the ‘open_pubs’ in a collection view and click the update dialog button. We’re going to want to apply this change to all the documents, so select All Documents and then go to the update tab, select Update with Aggregation Pipeline and copy this short pipeline in:

[
  {
    $set: {
      rating: { $floor: 
                 { $multiply: [ { $rand: {} }, 10 ] } 
              }
      }
  }
]

So it looks like this:

This is the aggregation update that puts a random rating on every pub

Click on Update and all documents will now get a random rating from 0.0 to 9.0.

Aggregating the pubs

We aren’t going to display 51,167 pubs. That would be a very very crowded map. What we want to do is find the highest rated pub in an area and map that. And for the area, we’ll use the local_authority field. For this, we need aggregation. Go to the Aggregation tool and start building a pipeline. We’ll step through the stages now:

Learn more about creating a MongoDB aggregation in Studio 3T

1: $sort the pubs by local authority and rating

In a $sort stage, sort by local_authority, then by descending pub rating:

{ 
    "local_authority" : 1.0, 
    "rating" : -1.0
}
Sorting the Pubs by local authority and rating

This makes our data ready to be grouped by local authority and the pubs will already be sorted. So our next step is…

2: $group up the results by local authority

Next, we $group the results up by local authority and put all the pubs, in sorted order, into an array for each authority. While we do that, use $sum to count the number of pubs into pub_count:

{ 
    "_id" : "$local_authority",
    "pub_count" : {
        "$sum" : 1
    },
    "local_pubs" : {
        "$push" : "$ROOT"
    }
}

What’s that “$$ROOT” you may ask? It’s an aggregation variable, which refers to the entire document currently passing through the pipeline. So this “$push” takes the incoming pub document and pushes it, in its entirety, into the local_pubs array. If you look at the Stage Output below, you can see the local_pubs array is full of pub documents:

The Group Aggregation stage which pushes a local authorities pubs into an array and counts them at the same time

3: $project only the top pub in the area

Now. for every local authority (now in the id field), we will just $project the 0th element on the pub array (using “$arrayElemAt”) and the count of pubs:

{ 
    "_id" : "$_id", 
    "pub" : { 
        "$arrayElemAt" : [
            "$local_pubs", 
            0.0
        ]
    }, 
    "pub_count" : "$pub_count"
}

This will leave us with a collection which has, for each local authority, the authority’s name as an id, a field called pub which has the details of the top rated pub in that authority and a count of pubs in pub_count.

A Project Stage that leaves only the top rated pubs in local authorities

4: $match only “good” locations

When we were building this example, I found that there were some bad coordinates in the geographical data. So, here we are only going to $match with documents where the pub’s latitude is a number:

{ 
    "pub.latitude" : { 
        "$type" : 1.0
    }
}

“$type” matches when the type of the value in the document matches the type of the value specified. 1.0 is a number, so it only matches numbers.

A Match Aggregation stage to eliminate pubs with bad location data

A Pause

And we’ll pause here for a moment. We have a set of pubs, one per local authority, with geographical data to locate them and an address. In the second part of this workshop, we’ll take this carefully aggregated data and create some MongoDB GeoJSON and make that into an interactive map you can host anywhere. Head there now.


How helpful was this article?
This article was hideous
This article was bad
This article was ok
This article was good
This article was great
Thank you for your feedback!

About The Author

DJ Walker-Morgan

Dj has been around since Commodore had Pets and Apples grew everywhere. With a background in Unix and development, he's been around the technology business writing code or writing content ever since.

Article navigation

Related articles

  • From Data to Maps: Rendering MongoDB GeoJSON with Studio 3T – Part 2
  • GeoJSON Mapping with MongoDB: Studio 3T Knowledge Updates
  • Working with MongoDB Data? Use These Data Masking Techniques
  • What’s New in Studio 3T 2020.10 | Data Masking, Reschema & The Fastest Studio 3T Version Yet
  • Simplifying Data Migrations From Legacy SQL To MongoDB Atlas With Studio 3T And Hackolade

Studio 3T

MongoDB Enterprise Certified Technology PartnerSince 2014, 3T has been helping thousands of MongoDB developers and administrators with their everyday jobs by providing the finest MongoDB tools on the market. We guarantee the best compatibility with current and legacy releases of MongoDB, continue to deliver new features with every new software release, and provide high quality support.

Find us on FacebookFind us on TwitterFind us on YouTubeFind us on LinkedIn

Education

  • Free MongoDB Tutorials
  • Connect to MongoDB
  • Connect to MongoDB Atlas
  • Import Data to MongoDB
  • Export MongoDB Data
  • Build Aggregation Queries
  • Query MongoDB with SQL
  • Migrate from SQL to MongoDB

Resources

  • Feedback and Support
  • Sales Support
  • Knowledge Base
  • FAQ
  • Reports
  • Case Studies
  • White Papers
  • Testimonials
  • Discounts

Company

  • About Us
  • Blog
  • Careers
  • Legal
  • Press
  • Privacy Policy
  • EULA

© 2022 3T Software Labs GmbH. All rights reserved.

  • Privacy Policy
  • Cookie settings
  • Impressum
When you click "Accept", you are agreeing to cookies being on your device. They may improve site navigation, site usage analysis, or the relevance of messages. It is up to you which cookies are enabled. Read our Privacy Policy.
Manage cookies
Accept
✕

Privacy Preference Center

A cookie is a small file of letters and numbers that is downloaded on to your computer when you visit a website. Cookies are used by many websites and can do a number of things, eg remembering your preferences, recording what you have put in your shopping basket, and counting the number of people looking at a website. In the form below you can choose which cookies, except for essential cookies, to allow or disable.

Accept all cookies

Manage consent preferences

Essential cookies are strictly necessary to provide an online service such as our website or a service on our website which you have requested. The website or service will not work without them.

Performance cookies allow us to collect information such as number of visits and sources of traffic. This information is used in aggregate form to help us understand how our websites are being used, allowing us to improve both our website’s performance and your experience.

Functional cookies collect information about your preferences and choices and make using the website a lot easier and more relevant. Without these cookies, some of the site functionality may not work as intended.

Social media cookies are cookies used to share user behaviour information with a third-party social media platform. They may consequently effect how social media sites present you with information in the future.

Accept selected