29

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?

Community
  • 1
  • 1
a person
  • 1,518
  • 3
  • 17
  • 26
  • You don't need a special database for this, though it will be faster in databases with geo extensions. You can just use [the Haversine formula](http://stackoverflow.com/q/27928/472495). There are SQL implementations in which you can add a distance filter in the `WHERE` clause. – halfer Jan 31 '17 at 11:13

1 Answers1

36

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.

joanolo
  • 6,028
  • 1
  • 29
  • 37
  • 1
    Thanks a lot Joanolo. I realize my question was downvoted a lot, but to be honest it is pretty hard to find this kind of information easily and also simple enough to digest. I appreciate your response. – a person Feb 01 '17 at 08:46
  • 2
    If I may ask one more question. At the moment, I don't think I need relational database, and hence am leaning towards MongoDB due to the ease of setting it up and using it. I am expecting a lot of initial writes (new markers created) and lots of reads, but don't expect many updates to existing markers besides deletes. I think my data models are also simple enough that if the need arises (and as mentioned I don't know much about databases, I'm tryign to build a PoC) I can switch to postgres. Do you think that is a valid approach? Thanks – a person Feb 01 '17 at 08:49
  • 1
    When you're on a Proof of Concept, normally your basic need is *make it happen fast*, in that case, choose the tools that let you work fastest. That depends a lot on your previous background; the kind of tools you're accostumed to. But be careful and don't fall into the trap of thinking that what's best for a PoC is the best thing in production... – joanolo Feb 01 '17 at 21:24
  • geoNear, and geoIntersects can be used in regards to MongoDB – Jeryl Cook May 09 '18 at 15:15