How to choose between MongoDB and PostgreSQL for GIS?
I'm trying to find some information on this topic but most articles I'm finding are 3+ years old. I am also fairly new to this side of things and not sure who to ask.
For my particular use case, I'm not a database expert at all but I've used MongoDB a few times before. I have only used PostgreSQL once for a small project (no GIS stuff involved). For what its worth, I have a small amount of data right now that I've stored into dynamoDB. There's a geo library for it, but it is only available in Java and I don't know Java very well.
My use case is fairly simple - besides user profile data, I want to be able to query markers added by users. Markers within X miles of a authenticated user's current latLng, etc. Markers have other data besides latLng associated with them, such as the user who added it, title, description, etc.
My goal is to be able to return results to the client side with markers within X miles in their current location from nearest to farthest using a web API. For this, between Mongo and PostgreSQL, what would be better to start with?
Answer
PostgreSQL can give you full (geo)spatial functionalities via PostGis, although you may not need that much in your application.
If you need just to quickly find users within a certain radius of a (lat, lng) point, you can use the earthdistance module, together with GIST indexes.
Check How can I speed-up my query on geo-location processes for detailed explanations.
Check also Searching in a Radius using Postgres.
In MongoDB, you would use geoNear functionality and 2dsphere Indexes
Querys in PostgreSQL are very fast and very flexible, and they scale very well. You need a little bit more of work to set it up than you would with MongoDB.
Anyhow, the basic functionality is available in both, and you should decide considering all other factors:
- Is your data well structured, and with few variations within this structure? (more on SQL side)
- Do you need ACID (atomicity, consistency, isolation, durability) guarantees? (more on SQL side)
- Do you need huge horizontal scaling? (more on MongoDB side)
- Do you need to query several tables at once (and join)? (SQL side)
- Do you feel more comfortable with JavaScript and JSON than in any other languages? (MongoDB +)
- (etc., etc.)
Check also Postgres Outperforms MongoDB and Ushers in New Developer Reality and System Properties Comparison MongoDB vs. PostgreSQL, among many others.