CODEWITHSUNDEEP
mongodbgeospatialpostgis
Chaoran
Chaoran

Reputation: 321

Retrive a limited number of records that locates near a geo spatial point

I have a use case where I need to retrieve a limited number of records that has a location hear a geospatial point. Something similar to:

SELECT * FROM table WHERE location is near (x, y) LIMIT 200

I want to use PostGIS. But I don't know whether it supports this kind of query. Because I don't want to retrieve records within a bounding box, but a limit number of records that are sorted according to their distance to the specified point. I didn't find information about it in PostGIS's documentation.

If PostGIS supports it, does it scale? This is the most frequently used query in my app. And the number of records is very large.

I know MongoDB has the $near query. Does it scale?

Upvotes: 0

Views: 124

Answers (2)

Mike T
Mike T

Reputation: 43612

PostGIS 2 introduced KNN GiST index distance operators (such as <-> centroid operator), which was nicely described in a blog post from 2011. This distance operators were designed for speed.

SELECT * FROM table WHERE location <-> ST_MakePoint(x, y) LIMIT 200;

There are many other distance filters and operators available.

Upvotes: 0

shelman
shelman

Reputation: 2699

I can't speak to postgis, but MongoDB's $near operator is designed to scale well, with the appropriate geospatial index in place, and the query is thoroughly tested for correctness as well as speed. Additionally, specifying the number of results you would like is very easy with the limit cursor option, which lightens the load on both the server and the network.

Upvotes: 1

Related Questions