0

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
old greg
  • 799
  • 8
  • 19
  • 1
    If you have more than 1000 buildings in a map, then random sampling is very likely to result in buildings scattered around. So I would suggest ordering them by random numbers. Another approach I could think of is to split the map into sub-regions, say 100 of them and pick 10 from each. – Kota Mori Oct 21 '20 at 12:44
  • Interesting idea! From a cursory glance I don't think it should be too difficult to divide the geometry I am passing to the `Within` function that represents my viewable region into some subregions. I'll try a simple limit with that, and then random sampling if the results aren't satisfactory. Thanks! – old greg Oct 21 '20 at 12:50
  • 1
    You could use the built-in clustering functions in Postgis: https://gis.stackexchange.com/questions/11567/spatial-clustering-with-postgis – mlinth Oct 21 '20 at 14:15

0 Answers0