Mongodb: Create index on array of two fields

Question

I have a MongoDB collection in which location data is being stored as follows:

{
    ...
    longitude: "-73.985679",
    latitude: "40.75003",
    ...
}

I need to create a 2dsphere index on these. Is there a way to combine these two separate fields into an array of the GeoJSON format?

prusswan · Accepted Answer · 2018-03-26 09:27:15Z

7

The only valid forms for a 2dsphere index are either as a GeoJSON object or as legacy coordinate pairs. The latter form being either an array on a single field or a subdocument with "lat" and "lon" keys.

You would be better off converting this information in the document for ongoing usage, and using the GeoJSON format. The best and safest way to do this is using the Bulk Operations API:

var bulk = db.collection.initializeOrderedBulkOp();
var counter = 0;

db.collection.find().forEach(function(doc) {
     bulk.find({ "_id": doc._id }).updateOne({
        "$set": {
            "location": {
                "type": "Point",
                "coordinates": [ doc.longitude, doc.latitude ]
            }
        },
        "$unset": {  "longitude": 1, "latitude": 1 } 
     });

     counter++;
     if ( counter % 1000 == 0 ) {
         bulk.execute();
         bulk = db.collection.initializeOrderedBulkOp();
     }

})

if ( counter % 1000 != 0 )
    bulk.execute();

You can chance that loop with eval() for a one off job, which will run the loop faster but not necessarily "safer".

Once complete it's just a matter of creating the index on the "location" field new in the document:

db.collection.ensureIndex({ "location": "2dsphere" })

Then you can use the index in your queries.

But you will need to change the structure in the document, so do it right.

edited Mar 26, 2018 at 9:27

prusswan

7,1514 gold badges44 silver badges65 bronze badges

answered Jan 3, 2015 at 6:37

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ZeMoon Over a year ago

Thanks! I guess transformation is the only way. The web app which populates this data stores locations in separate fields. Is there a way I can restructure the data from MongoDB itself whenever a new document is created?

Neil Lunn Over a year ago

@ZeMoon Well of course any code that is creating documents with the original structure should be changed, so you will have to address that and it should be a fairly trivial change. Just seems to be one of those things were people need to get used to the structured document approach rather than the "tabular" approach of RDBMS.

ZeMoon Over a year ago

Very well. Thanks again!

Neil Lunn Over a year ago

@ZeMoon You can code this in any language you wish. Every available driver has functions to support the Bulk Operations API or even just basic looping and updates.

fazy Over a year ago

Great solution, thanks. Note "latitute" should be "latitude" on the unset line, and the final check should be if ( counter % 1000 != 0) (sorry I can't edit as apparently I must change 6 characters).

Collectives™ on Stack Overflow

Mongodb: Create index on array of two fields

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related