Geolocation, CSV, MongoDB and Compass

Today I discover a tool: MongoDB Compass. Its website says “The Easiest Way to Explore and Manipulate Your MongoDB Data.”.

I decided to try, so I defined a problem to solve.

I have a CSV file with some Point Of Interest. The CSV has the first row with the name of column and 3 column:

  • lng: the longitude;
  • lat: the latitude;
  • name: the name of the point of interest.

Example:

lng,lat,name
12.284440,45.720860,A27 — TREVISO NORD — Casello
12.284600,45.720980,A27 — TREVISO NORD — Casello
12.313390,45.646020,A27 — TREVISO SUD — Casello

To load this CSV file into a MongoDB Collection I execute this command:

$ mongoimport -d motorway -c tollbooth — type csv — file tollbooth.csv — headerline

Where:

  • motorway is the name of database;
  • tollbooth is the name of the collection
  • tollbooth.csv is the name of the CSV file.

Create Geospatial Index

In order to use geospatial function provided by MongoDB, I’m going to create the right field and create the right index.

Importing data from CSV makes a “flat” structure:

{
“_id” : ObjectId(“57e55de57f489266bccd221c”),
“lng” : 11.82476,
“lat” : 42.05791,
“name” : “A12 — CIVITAVECCHIA Sud — Svin”
}

For indexing purpose I need to aggregate lng and lat field into a new structured field named “loc” like this:

{
“_id” : ObjectId(“57e55de57f489266bccd221c”),
“lng” : 11.82476,
“lat” : 42.05791,
“name” : “A12 — CIVITAVECCHIA Sud — Svin”,
“loc” : [
11.82476,
42.05791
]
}

To obtain this new structured field “loc”, I need to execute a MongoDB function like this:

db.tollbooth.find().forEach(function (item) {
loc = [item.lng, item.lat]
item.loc= loc
db.tollbooth.save(item)
})

Now I can create geospatial index:

db.tollbooth.createIndex( { “loc”: “2d” } )
In order to execute queries and functions in mongoDB you can download and install Robomongo a Graphical User Interface.

I would like to add a new calculate field for statistical purpose. In the field “name” the firsts characters indicate the highway name. In the field “name” the highway name are separated from description with a separator “-”.

To obtain new field named “route” with the first substring of the field “name” I need to execute this function:

db.tollbooth.find().forEach(function (item) {
name = item.name
mysplit = name.split(“ — “)
item.route = mysplit[0]
db.tollbooth.save(item)
})

Now I have my collection (tollbooth) ready for data query and analysis.

You can download MongoDB Compass and install it.

When I launch MongoDB Compass I can see on the left the list of databases and collections. I select the right collection and on the right I can see a dashboard with some data aggregation.

For Geospatial field MongoDB Compass shows a Map with points of interest, for string I can see a bar chart with the occurrences.

MongoDB Compass default Dashboard