CODEWITHSUNDEEP
sqlgispostgisspatialite
old greg
old greg

Reputation: 897

Equal distance between points in spatial SQL query with limit

I have an application which displays POIs on a map. I have a database with all the POIs in it using Spatialite which I query to find POIs within the viewable map area.

Performance is fine with small-medium numbers of POIs (up to 1000 works perfectly), however if the user zooms too far out in my map view, there are potentially hundreds of thousands of POIs to be loaded from the database.

I'd like to limit the number of results somehow, so that I can constrain this to an optimal level.

My naïve approach is to just add LIMIT 1000 at the end of my query, however with this there's no way to prevent the POIs in the results being clustered in the same area. The ideal solution would ensure that the POIs are distributed equally (or near enough to) across the viewable region.

Is there some way to achieve this?

As stated, I am using Spatialite, but am open to solutions for Postgis or other GIS solutions as I may be able to learn from them.

My query at present is something like this:

SELECT
    b.name,
    Y(Transform(b.geom, 4326)) "y",
    X(Transform(b.geom, 4326)) "x"
FROM buildings b
WHERE Within(b.geom, Transform(PolygonFromText('POLYGON((
    -0.1381030196154711 51.51132617405723,
    -0.12929698038450965 51.51132617405723,
    -0.12929698038450965 51.50863378616471,
    -0.1381030196154711 51.50863378616471,
    -0.1381030196154711 51.51132617405723
))', 4326), 27700)) > 0

Upvotes: 0

Views: 115

Answers (0)

Related Questions