At the User Conference in October 2015 we showed how we had attempted a way to spread out lots of points that would otherwise sit on top of one another onto a closely packed grid. Below is the SQL that we used to generate the view that became an Earthlight layer:

```
SELECT [whatever extra fields you want available in the map],
-- Don't forget to include a suitable Primary Key-esque field!
-- Translate the point location using the grid, centred on
-- the original location, scaled by marker_size
-- Using the max_row ensures that the grid of points is centred on
-- the parts of the grid that were used rather than the whole square
ST_Translate(geom,
marker_size * (grid_column - ((1 + square_size::numeric) / 2)),
marker_size * (grid_row - ((1 + max_row::numeric) / 2))) AS geom
FROM (
-- Calculate the grid column and row for the moved marker,
-- and get the maximum row number for each block to be able to
-- set the centre of the grid by row.
SELECT [whatever extra fields you want available in the map],
square_size, prop_order,
-- Calculate which column of the grid the record sits in
((prop_order % square_size) + 1)::numeric AS grid_column,
-- Calculate which row in the grid the record sits in
ceiling(prop_order / square_size) + 1 AS grid_row,
geom, marker_size,
count,
-- Calculate the maximum number of rows in the grid (as the grid
-- is based on a square base it may not use all the rows)
ceiling(count::numeric / square_size) AS max_row
FROM (
-- Find the size of square needed for each block
SELECT *,
-- |/ is the square root function, and ceiling rounds up to the nearest integer
ceiling(|/ count)::integer AS square_size
FROM (
-- Find the blocks, get the property count and number each
-- property within them. Set the grid size to match the
-- map marker size (in metres)
-- The ST_AsText groups records by the text representation of their location,
-- so minor differences would not be grouped. Rounding the location to some precision would avoid this
SELECT 2::integer AS marker_size,
-- Count how many points are at that location
count(landparcelref) OVER (PARTITION BY ST_AsText(geom)) AS count,
-- Calculate rank within the grid, ordered by the [order fields]
row_number() OVER (PARTITION BY ST_AsText(geom) ORDER BY [order fields]) - 1 AS prop_order,
*
FROM [your originating point table]
) AS c
) AS d
) AS p
;
```

We use PostgreSQL and PostGIS so the exact way of coding the required spatial functions (ST_AsText and ST_Translate) and window functions (count() and row_number) may vary between SQL variants.

Any questions?