1

I have huge database of lat/lon GPS coordinate points. For every point, I have several other variables (height, surface type, temperature, pressure...).

Also, data will be changed every day and old ones drop, no history.

I want to be able for given point find N-closest points in my database. Should I use PostGIS for this, or classic PostreSQL or MySQL is enough? I dont need any other data transformations, only finding closest (and even not by real distance in km). This is supported also by classic database systems and I am not sure if PostGIS is not like going with cannon to pigeon.

4

Postgis is an extension to Postgres, rather than a stand alone application, that provides a spatial data type to Postgres, and provides numerous spatial functions that operate on geometry(ies). Spatial indexing, which you will surely need to find n closest points efficiently, is implemented as an extended R-tree, but the indexing mechanism comes from Postgres's Gist indexing. So, as such, by using Postgis you get the best of both worlds -- all of the ACID features of an RDBMS, plus very efficient storing and indexing of spatial types. So really, you face no choice at all, choosing Postgis, is implicitly choosing Postgres and its many features.

As far as finding nearest features are concerned, you can either use ST_DWithin or if one of the points is a constant, you can use the new operators in Postgres, <-> and <#>, for very fast index lookup of nearest points. Here is a good post on the latter. Any of these will require you to have a spatial index.

You could do the above directly without any use of spatial types, but you are likely to find that it won't scale well without an index that works in two dimensions. Also, but using Postgis, you are future proofing in that if you suddenly have a need to buffer points, or convert to another coordinate system (or countless other things) you will immediately have the capability.

As an aside, I have worked a lot on MySQL spatial, before moving to Postgres/Postgis, and I can tell you without any prejudice that in terms of functionality, speed and community, it comes a very poor second. If you are already using MySQL, I am sure that finding n-nearest neighbours will not be a problem, but if you are looking into choosing a spatially enabled RDBMS in general, I would whole heartedly recommend the Postgres/Postgis route. There is a Stack Overflow question about different spatial DBs -- this is also a shameless plug for an answer of mine :-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.