Over a million developers have joined DZone.

Creating Geo-Enabled Applications With MongoDB, GeoJSON, and MySQL

DZone's Guide to

Creating Geo-Enabled Applications With MongoDB, GeoJSON, and MySQL

This blog post will discuss creating geo-enabled applications with MongoDB, GeoJSON, and MySQL. Read on for more details.

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

This blog post will discuss creating geo-enabled applications with MongoDB, GeoJSON, and MySQL.


Recently, I published a blog post about the new GIS features in MySQL 5.7. Today, I’ve looked into how to use MongoDB (I’ve tested with 3.0 and 3.2, with 3.2 being much faster) for the same purpose. I will also talk about GIS in MySQL and MongoDB at Percona Live next week (together with my colleague Michael Benshoof).

MongoDB and GIS

MongoDB has a very useful feature called “geoNear.” There are other MongoDB spatial functions available to calculate the distance on a sphere (like the Earth), i.e. $nearSphere$centerSphere$near—but all of them have restrictions. The most important one is that they do not support sharding. The geoNear command in MongodDB, on the other hand, supports sharding. I will usegeoNear in this post.

For this test, I exported Open Street Map data from MySQL to MongoDB (see the “Creating GEO-enabled applications with MySQL 5.6” post for more details on how to load this data to MySQL).

Export the data to JSON. In MySQL 5.7, we can use JSON_OBJECT to generate the JSON file:




INTO OUTFILE'/var/lib/mysql-files/points.json';


Use mongoimport to import JSON into MongoDB (I’m using 24 threads, -j 24, to use parallel import):

mongoimport --db osm --collection points -j 24 --file /var/lib/mysql-files/points.json
2016-04-11T22:38:10.029+0000    connected to: localhost
2016-04-11T22:38:13.026+0000    [........................] osm.points  31.8 MB/2.2 GB (1.4%)
2016-04-11T22:38:16.026+0000    [........................] osm.points  31.8 MB/2.2 GB (1.4%)
2016-04-11T22:38:19.026+0000    [........................] osm.points  31.8 MB/2.2 GB (1.4%)
2016-04-11T23:12:13.447+0000    [########################] osm.points  2.2 GB/2.2 GB (100.0%)
2016-04-11T23:12:15.614+0000    imported 13660667 documents

Create a 2d index:

> use osm
switched to db osm
> db.points.createIndex({ geometry : "2dsphere" } )
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1

Another option would be using the osm2mongo Ruby script, which will convert the osm file and load it directly to MongoDB.

Now, I can use thegeoNear command to find all the restaurants near my location:

















"other_tags":"addr:city=>Durham,addr:country=>US,addr:housenumber=>605,addr:street=>West Main Street,amenity=>restaurant,building=>yes"










"other_tags":"addr:full=>345 West Main Street, Durham, NC 27701, US,amenity=>restaurant,website=>http://toast-fivepoints.com/"


MongoDB 3.0 vs 3.2 With geoNear

MongoDB 3.2 features Geospatial Optimization:

MongoDB 3.2 introduces version 3 of 2dsphere indexes, which index GeoJSON geometries at a finer gradation. The new version improves performance of 2dsphere index queries over smaller regions. In addition, for both 2d indexes and 2dsphere indexes, the performance of geoNear queries has been improved for dense datasets.

I’ve tested the performance of the abovegeoNear query with MongoDB 3.0 and MongoDB 3.2 (both the old and new versions of 2dsphere index). All the results statistics are for a"limit":5 and"maxDistance":10000.

MongoDB 3.0, index version 2:















MongoDB 3.2, index version 2:

MongoDB 3.2, index version 3:


























That is significantly faster, 1ms for five results!

MySQL and GeoJSON Revisited

To compare it to the performance of the above query, I’ve created a similar query in MySQL. First of all, we will need to use the good old bounding rectangle (envelope) trick to only include the points in the 10 miles radius (or so). If we don’t, MySQL will not be able to use spatial (RTREE) index. I’ve created the following function to generate the envelope:












setpoint_text=concat('POINT(',lon,' ',lat,')');






setl=concat('LineString(',rlon1,' ',rlat1,',',rlon2,' ',rlat2,')');
















Then we can use the following query (an update of the GeoJSON query from my previous post):



set@p=ST_GeomFromText(concat('POINT(',@lon,' ',@lat,')'),1);



  "type": "FeatureCollection",

  "features": [


   "type": "Feature",

      "geometry": ',ST_AsGeoJSON(shape),',

      "properties": {"distance":',st_distance_sphere(shape,@p),', "name":"',name,'"}

   }'order byst_distance_sphere(shape,@p)),





andnameisnot null




The time is slower: 40ms in MySQL compared to 1ms – 12ms in MongoDB. The box is AWS EC2 t2.medium.

To recap the difference between MongoDBgeoNear and MySQLst_distance_sphere:

  • MongoDBgeoNear uses 2dsphere index, so it is fast; however, it can’t just calculate the distance between two arbitrary points
  • MySQL st_distance_sphere is a helper function and will only calculate the distance between two points; it will not use an index – we will have to use thecreate_envelope function to restrict the search so MySQL will use an index

Time-wise, this is not an apples-to-apples comparison as the query is quite different and uses a different technique.

Visualizing the Results

Results for GeoJSON for Google Maps API:





"properties":{"distance":87.67869122893659,"name":"Pizzeria Toro"}








"properties":{"distance":174.8300018385443,"name":"Dame's Chicken and Waffles"}


Now, we can add those on a map:

Image title

Back to MongoDB: Pluses and Minuses

MongoDB uses Google’s S2 library to perform GIS calculations. ThegeoNear command is fast and easy to use for finding points of interests near you (which is the most common operation). However, full GIS support does not natively exist.

Another issue I came across when creating a 2dsphere index: MongoDB is very strict when checking the lines and polygons. For example:




"errmsg":"exception: Can't extract geo keys: { _id: ObjectId('570308864f45f7f0d6dfbed2'), name: "75North", geometry: { type: "LineString", coordinates: [ [ -85.808852, 41.245582 ], [ -85.808852, 41.245582 ] ] }, other_tags: "tiger:cfcc=>A41,tiger:county=>Kosciusko,IN,tiger:name_base=>75,tiger:name_direction_suffix=>N,tiger:reviewed=>no" }  GeoJSON LineString must have at least 2 vertices: [ [ -85.808852, 41.245582 ], [ -85.808852, 41.245582 ] ]",



MongoDB complains about this: type: “LineString”, coordinates: [ [ -85.808852, 41.245582 ], [ -85.808852, 41.245582 ] ]

This is a “bad” line string as the starting point and ending point are the same. I had to remove the bad data from my MongoDB imported dataset, which was tricky by itself. MongoDB (as opposed to MySQL) does not have a native way to compare the values inside the JSON, so I had to use $where construct which is slow and acquires a global lock:


$where:"this.geometry.coordinates[0][0] ==  this.geometry.coordinates[1][0] 

      && this.geometry.coordinates[0][1] == this.geometry.coordinates[1][1]"})


After that, I was able to add the 2dsphere index.


MongoDB looks good, is pretty fast and easy for geo-proximity search queries – until you go outside of the one function and need full GIS support (which does not natively exist).  It may be trickier to implement other GIS functions likest_contains orst_within.

Update: as pointed out, MongoDB actually supports $geoWithin and $geoIntersects GIS functions.

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

database ,sql ,mysql ,percona ,mongo db

Published at DZone with permission of Alexander Rubin, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}