Index for a MongoDB query made up of GeoJson and _id
I have the following query:
db.getCollection('spawnlocations').find({'location':{'$nearSphere':{'$geometry':{'type':'Point', 'coordinates':[-73.94075,40.789138]},'$maxDistance':5000}}, "expirationtimems":{"$gte":1445600123}, "_id":{"$gte":"2a920240836c40d8b374203a798a27fa.16"}}).sort({"_id":1}).limit(50)
It filters on a GeoJson location, the _id of the rows and an expiration time. I tried different indexes but I can't really find a way to make this query fly.
I thought that something like:
{
"location" : "2dsphere"
}
could work but it doesn't really look like it.
Any suggestions?
Update
Here's the output of explain(): https://gist.github.com/tanis2000/d790c063c60cf3d531972ff52df804b7
index mongodb geojson
edited 5 hours ago
Evan Carroll
31.6k965213
asked Aug 10 '16 at 20:19
Valerio SantinelliValerio Santinelli
1366
add a comment |
I have the following query:
db.getCollection('spawnlocations').find({'location':{'$nearSphere':{'$geometry':{'type':'Point', 'coordinates':[-73.94075,40.789138]},'$maxDistance':5000}}, "expirationtimems":{"$gte":1445600123}, "_id":{"$gte":"2a920240836c40d8b374203a798a27fa.16"}}).sort({"_id":1}).limit(50)
It filters on a GeoJson location, the _id of the rows and an expiration time. I tried different indexes but I can't really find a way to make this query fly.
I thought that something like:
{
"location" : "2dsphere"
}
could work but it doesn't really look like it.
Any suggestions?
Update
Here's the output of explain(): https://gist.github.com/tanis2000/d790c063c60cf3d531972ff52df804b7
index mongodb geojson
edited 5 hours ago
Evan Carroll
31.6k965213
asked Aug 10 '16 at 20:19
Valerio SantinelliValerio Santinelli
1366
What exactly you mean by 'fly', also can you post the output of .explain()?
– TarunG
Aug 20 '16 at 10:40
1
@TarunG 'fly' as in "it should be extremely quick". Anyway I solved the problem at its root. Instead of using the coordinates as lookup I'm using a Googles2cell id which is just an uint64 that can be indexed as needed.
– Valerio Santinelli
Aug 20 '16 at 17:31
add a comment |
I have the following query:
db.getCollection('spawnlocations').find({'location':{'$nearSphere':{'$geometry':{'type':'Point', 'coordinates':[-73.94075,40.789138]},'$maxDistance':5000}}, "expirationtimems":{"$gte":1445600123}, "_id":{"$gte":"2a920240836c40d8b374203a798a27fa.16"}}).sort({"_id":1}).limit(50)
It filters on a GeoJson location, the _id of the rows and an expiration time. I tried different indexes but I can't really find a way to make this query fly.
I thought that something like:
{
"location" : "2dsphere"
}
could work but it doesn't really look like it.
Any suggestions?
Update
Here's the output of explain(): https://gist.github.com/tanis2000/d790c063c60cf3d531972ff52df804b7
index mongodb geojson
edited 5 hours ago
Evan Carroll
31.6k965213
asked Aug 10 '16 at 20:19
Valerio SantinelliValerio Santinelli
1366
I have the following query:
db.getCollection('spawnlocations').find({'location':{'$nearSphere':{'$geometry':{'type':'Point', 'coordinates':[-73.94075,40.789138]},'$maxDistance':5000}}, "expirationtimems":{"$gte":1445600123}, "_id":{"$gte":"2a920240836c40d8b374203a798a27fa.16"}}).sort({"_id":1}).limit(50)
It filters on a GeoJson location, the _id of the rows and an expiration time. I tried different indexes but I can't really find a way to make this query fly.
I thought that something like:
{
"location" : "2dsphere"
}
could work but it doesn't really look like it.
Any suggestions?
Update
Here's the output of explain(): https://gist.github.com/tanis2000/d790c063c60cf3d531972ff52df804b7
index mongodb geojson
index mongodb geojson
edited 5 hours ago
Evan Carroll
31.6k965213
asked Aug 10 '16 at 20:19
Valerio SantinelliValerio Santinelli
1366
edited 5 hours ago
Evan Carroll
31.6k965213
asked Aug 10 '16 at 20:19
Valerio SantinelliValerio Santinelli
1366
edited 5 hours ago
Evan Carroll
31.6k965213
edited 5 hours ago
Evan Carroll
31.6k965213
edited 5 hours ago
Evan Carroll
31.6k965213
31.6k965213
asked Aug 10 '16 at 20:19
Valerio SantinelliValerio Santinelli
1366
asked Aug 10 '16 at 20:19
Valerio SantinelliValerio Santinelli
1366
asked Aug 10 '16 at 20:19
Valerio SantinelliValerio Santinelli
1366
1366
What exactly you mean by 'fly', also can you post the output of .explain()?
– TarunG
Aug 20 '16 at 10:40
1
@TarunG 'fly' as in "it should be extremely quick". Anyway I solved the problem at its root. Instead of using the coordinates as lookup I'm using a Googles2cell id which is just an uint64 that can be indexed as needed.
– Valerio Santinelli
Aug 20 '16 at 17:31
add a comment |
What exactly you mean by 'fly', also can you post the output of .explain()?
– TarunG
Aug 20 '16 at 10:40
1
@TarunG 'fly' as in "it should be extremely quick". Anyway I solved the problem at its root. Instead of using the coordinates as lookup I'm using a Googles2cell id which is just an uint64 that can be indexed as needed.
– Valerio Santinelli
Aug 20 '16 at 17:31
What exactly you mean by 'fly', also can you post the output of .explain()?
– TarunG
Aug 20 '16 at 10:40
What exactly you mean by 'fly', also can you post the output of .explain()?
– TarunG
Aug 20 '16 at 10:40
1
1
@TarunG 'fly' as in "it should be extremely quick". Anyway I solved the problem at its root. Instead of using the coordinates as lookup I'm using a Google
s2 cell id which is just an uint64 that can be indexed as needed.– Valerio Santinelli
Aug 20 '16 at 17:31
@TarunG 'fly' as in "it should be extremely quick". Anyway I solved the problem at its root. Instead of using the coordinates as lookup I'm using a Google
s2 cell id which is just an uint64 that can be indexed as needed.– Valerio Santinelli
Aug 20 '16 at 17:31
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f146439%2findex-for-a-mongodb-query-made-up-of-geojson-and-id%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f146439%2findex-for-a-mongodb-query-made-up-of-geojson-and-id%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What exactly you mean by 'fly', also can you post the output of .explain()?
– TarunG
Aug 20 '16 at 10:40
1
@TarunG 'fly' as in "it should be extremely quick". Anyway I solved the problem at its root. Instead of using the coordinates as lookup I'm using a Google
s2cell id which is just an uint64 that can be indexed as needed.– Valerio Santinelli
Aug 20 '16 at 17:31