Naive dispersal of overlapping points in PostGIS

point-dispersal.sql

drop table if exists inspection_point_buffers;

-- Group identical overlapping points and count how many occupy
-- the space.
create temporary table inspection_overlappoing_points as
  select a.geom as geom,
        count(*)
  from latest_inspections a,
       latest_inspections b
  where st_equals(a.geom, b.geom)
        and a.rest_id <> b.rest_id
  group by 1
  order by 2 desc;

-- Using the number of points occupying a space, create a buffer region which
-- we'll use later to disperse points in. More points equals a larger buffer zone.
create temporary table inspection_point_buffers as
  select st_buffer(geom, 0.0000035 * count, 'quad_segs=8') as geom2
  from inspection_overlappoing_points;

-- Randomly place points that occupy the same space using our buffer zones we
-- created earlier.
update latest_inspections set
  geom = randompoint(geom2)
  from inspection_point_buffers buffers
  where st_contains(geom2, geom);

random-point.sql

-- Generate random point in polygon from https://trac.osgeo.org/postgis/wiki/UserWikiRandomPoint
CREATE OR REPLACE FUNCTION RandomPoint (
                geom Geometry,
                maxiter INTEGER DEFAULT 1000
        )
        RETURNS Geometry
        AS $$
DECLARE
        i INTEGER := 0;
        x0 DOUBLE PRECISION;
        dx DOUBLE PRECISION;
        y0 DOUBLE PRECISION;
        dy DOUBLE PRECISION;
        xp DOUBLE PRECISION;
        yp DOUBLE PRECISION;
        rpoint Geometry;
BEGIN
        -- find envelope
        x0 = ST_XMin(geom);
        dx = (ST_XMax(geom) - x0);
        y0 = ST_YMin(geom);
        dy = (ST_YMax(geom) - y0);

        WHILE i < maxiter LOOP
                i = i + 1;
                xp = x0 + dx * random();
                yp = y0 + dy * random();
                rpoint = ST_SetSRID( ST_MakePoint( xp, yp ), ST_SRID(geom) );
                EXIT WHEN ST_Within( rpoint, geom );
        END LOOP;

        IF i >= maxiter THEN
                RAISE EXCEPTION 'RandomPoint: number of interations exceeded %', maxiter;
        END IF;

        RETURN rpoint;
END;
$$ LANGUAGE plpgsql;