1

I have a postgis database with over 50 million rows. Every row consists of a POINT corresponding to lon,lat and a value (number 1-100)

I'm trying to create tiles for zoom levels 5-20 that I want to represent on google maps with the help of GeoServer.

Depending on the value of the point I want to paint it in a different color. As many of points are close to each other I want to paint based on their combined average value. Obviously which points are combined is depending on the current zoom level. Does Geoserver/PostGIS provide a good solution to this problem?

My current solution is to use ST_SnapToGrid and use different tables for every zoom level with different grid sizes. A solution I'm not very happy with.

1

Instead of using vectors to represent this (as you are aggregating to a grid), why not use a raster? You could generate rasters at different resolutions reflecting your grid size.

You can do this in PostGIS. I would do it using the following approach:

  • create a grid of polygons at each of your zoom scales
  • aggregate the points per grid using the criteria that you describe above
  • convert the grid to a raster

If this process is not something dynamic, and you only need to do it once or twice you could also perform these steps in QGIS, and generate the rasters there.

  • Can I do this with Postgis? Do you know of any helpful resources that describe the process? – ascu Nov 26 '14 at 12:49
  • I expanded my answer to describe the general process – djq Nov 26 '14 at 15:08

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.