I'm using Postgres 9.4.4 with Postgis 2.2.1., my issues is that i have about 3 million of points i want to fetch and display on a map with leaflet. The problem is that those are too many points, what is the best aproach to tackle the problem since fetching all the rows from the database is slow?

i have considered taking a bunch of random points and group the most closest one to those points but it might not be best.

Other solution i thougth is paginate all the points and fetching them by groups, since this would be faster that query all of them in one time, but not sure how to do this with Geoserver.

Any hints/ideas about this?

  • 1
    The real question is why do you want to display that many points? No one will be able to distinguish them or see even see most of them. best solution is to wait until the user has zoomed (a long way) in and then turn layer on
    – Ian Turton
    Jan 22 '16 at 17:22
  • @iant ok, that sounds reasonable, still, fetching those points from database would be slow
    – Progs
    Jan 22 '16 at 17:30
  • 3
    fortunately GeoServer only loads the points it needs for the requested map, which will be fast assuming you have a spatial index on them
    – Ian Turton
    Jan 22 '16 at 17:32
  • Could we get some additional details on your use case? I don't think anyone would want to page through 3 million records. Are there other properties associated with each point that would be worth filtering? Also, are the points better represented in aggregate form? Jan 22 '16 at 19:05

While I can't help you on the database io speed issue, you can display 3 millions points easily if you rasterize. Bin points into pixels (aggregate), then decide how contents of bins translate into a color. I'm currently involved in creating a python library to do exactly this: https://github.com/bokeh/datashader

This example below uses that technique to plot 320 million points. The map is re-computed on each pan/zoom event from points stored in memory on the server.

enter image description here

A bit of a tangent, but this video shows the app below which can give you a sense of the performance: https://www.youtube.com/watch?v=YAls_da1cF4

  • But why? I can't see most of your pixels and why recalculate on each pan?
    – Ian Turton
    Jan 22 '16 at 19:14
  • 3
    Why = so you can take an arbitrarily large dataset (> 1 billion points) and visualize it without the saturation issues that 300 million points on a screen would create. In this case, I color the pixels by the max count of each race/ethnicity found in a pixel. Since the image is generated server-side, you aggregate and colormap based on current extent / viewport dimension. If you zoom in, or pan, the image is recalculated just like WMS. 3 million points is very small, unless you are stuck in the svg-rendered-geojson world.
    – bcollins
    Jan 22 '16 at 19:20
  • @bcollins Interesting - how customizable is that? And - are the pixels generated by analyzing the data on the client? Jan 22 '16 at 22:12
  • 1
    image is generated server-side, example above is akin to WMS or an arcgis dynamic map sevice
    – bcollins
    Jan 22 '16 at 22:34
  • 1
    I'm using a local file containing data, reading it into memory, then computing using python. From a RDMS, you would want to aggregate points within the database and return a 2d array ( height x width ) of the aggregates, then proceed with color mapping.
    – bcollins
    Jan 25 '16 at 23:33

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.