It seems that lots of us have some SQL database or other in the background, acting as the datastore for Earthlight, Aurora and the rest. Despite the power of StatMap’s products to do amazing things sometimes we can give them a helping hand by shifting some of the hard work to the database.
This topic is for sharing SQL tips and code with each other.
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?
Hello, John.
Thanks very much for this. Will try this out in PostGIS - then see if it can be translated to T-SQL (though that’s probably being a little too adventurous.)
Regards,
Gordon