Im currently working on a system to track user interaction on mapwidgets. Now i need to visualize the bounds that users are looking at on the map. Since were using a whole lot of data i need to find a way of aggregating the data on the Server.
Im really just beginning to actually user the Postgis extension so im completely clueless how to do the following:
Now i thought id create a table:
ID (PK) | NE (Point) | SW (Point) | Diagonal (int or float) | Diagonal-1 (int or float)
So id save the northeastern Point the Southwestern Point, die Distance of Both of these Points (Diagonal) and the Distance of two ne/sw Points of the next smaller Raster. And do this for every Rectangle Starting at x:y then x/2:y/2 and so on until i have grids spanning the world of a few hundred meters. ( I guess creating a table will not serve me well since there will be a huuge amount of rows in the table. )
Now lets say the Server recieves an event of someone viewing the map.
bounds: ne,sw, center: point, diagonal: 3 kilometers
Id like to query for the ID of the Raster representing the bounds of the event.
so id check the grid table for the rows:
grid.diagonal >= events.diagonal > grid.diagonal-1
So an Event with a diagonal smaller then raster of size X but bigger then the next smaller raster will potentially get saved into a Raster of size X
then id like to check: event.center within which raster, and save the id of the raster in my event table. When querying the event table i could just do a count to get the number of events within that raster.
My guess is that I am looking at the problem in the wrong angle, has someone some ideas for me or done something similar like this?
edit, Well scratch the above lets make it a bit more simple
In essence i need the following:
I need a Grid spanning the World, and if custom viewbounds are supplied i need to check which Grid these viewbounds touch and save the bounds of that grid.