SQL, perl und Unix/Linux Schulungen in und um Wien

For my perl Module Geo::Heatmap you need to find a the maximum number of point per tile (or rather per bin). You may achieve this by parsing the the logs, or use the Power of PostGIS:

To group geographically close points you may use the ST_GeoHash function:


with geohash as (
select ST_GeoHash(geom::geometry, 5) st_geohash,
geom
from geodata where
not(St_X(geom) < -180 or St_Y(geom)< -90 or St_X(geom) > 180 or St_Y(geom) > 90)
)
SELECT ST_Extent(geom) as extent FROM geohash
where st_geohash =
(select st_geohash from
(select st_geohash, count(*) c
from geohash
group by st_geohash
order by c desc limit 1) max_geohash)
)

The number 5 used in the function denotes the online casino length of the hash the geom points should have in common. If you group the points and order them by their count you’ll find the most densely populated regions in/of your data.

The function ST_Extent then gives you the lat/long Box containing this Points. Using only the first line of the result may not lead to the absolutly “correct” solution but for the purpose of scaling it is “good enough”