How to draw a raster over the World using Postgis

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:

I thought about tiling the map into grids, starting at a few Hundred kilometers, then gradually tiling down those grids until i have grids of a few meters (like 110/70).

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.

• Welcome to GIS Stack Exchange! What you try to achieve sounds a little bit confused. You would like to record map usage, and compress the data so that you can quickly retrieve usage stats of a particular area? Many schemes could work, and performance would highly depend on expected traffic. – ghybs Nov 13 '15 at 8:58
• Expected traffic is not that big of a deal at first. This is part of my thesis regarding user behaviour on map widgets. Im just not very familiar with postgis, right now im only saving the exact bounds and center of the "view event" and im using the center on a pointmap / aggegating the data on the clientside using js. I guess i could span a grid on the system tracking the interactions. but there has to be a way to do this using postgis? – Naarf Nov 13 '15 at 13:09