Point clustering

-- Table: public.strength3

-- DROP TABLE public.strength3;

CREATE TABLE public.strength3
(
  gpstime timestamp with time zone,
  latitude double precision,
  longitude double precision,
  strength integer,
  uniqueid integer,
  clusterid integer,
  buffered geometry
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.strength3
  OWNER TO oru;

-- Index: public.strength3_buffered_idx

-- DROP INDEX public.strength3_buffered_idx;

CREATE INDEX strength3_buffered_idx
  ON public.strength3
  USING gist
  (buffered);

-- Index: public.strength3_clusterid_idx

-- DROP INDEX public.strength3_clusterid_idx;

CREATE INDEX strength3_clusterid_idx
  ON public.strength3
  USING btree
  (clusterid);

-- Index: public.strength3_current_idx

-- DROP INDEX public.strength3_current_idx;

CREATE INDEX strength3_current_idx
  ON public.strength3
  USING gist
  (current);

-- Index: public.strength3_uniqueid_idx

-- DROP INDEX public.strength3_uniqueid_idx;

CREATE INDEX strength3_uniqueid_idx
  ON public.strength3
  USING btree
  (uniqueid, buffered);


-- Table: public.strengthpoly

-- DROP TABLE public.strengthpoly;

CREATE TABLE public.strengthpoly
(
  strength integer,
  clusterid integer,
  geom geometry
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.strengthpoly
  OWNER TO oru;
-- Sequence: public.cities1000_id_seq

-- DROP SEQUENCE public.cities1000_id_seq;

CREATE SEQUENCE public.cities1000_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE public.cities1000_id_seq
  OWNER TO postgres;

-- Function: public.cluster_points_buffer(integer, text)

-- DROP FUNCTION public.cluster_points_buffer(integer, text);

CREATE OR REPLACE FUNCTION public.cluster_points_buffer(srcuniqueid integer, tablename text)
  RETURNS void AS
$BODY$
DECLARE
    sql text;
    clusterId_temp int:= null;
    rec_dest record;
    count int;
BEGIN
    count :=0;
    FOR rec_dest IN SELEct s2.uniqueid, s2.clusterid
        from strength3 s1, strength3 s2
        where s1.uniqueid =srcuniqueid
        and st_intersects(s1.buffered, s2.buffered)
        LOOP
        count := count+1;
        if 0 = count%1000 then
raise notice 'Processing iteration % at %.',count, clock_timestamp();
        end if;
        IF ( clusterid_temp is null and rec_dest.clusterId IS NULL ) THEN
            clusterId_temp = nextval('seq_pts_clusterId');
            sql := 'UPDATE ' || tablename || ' SET clusterId = '
            || clusterId_temp || ' WHERE uniqueid = ' || srcUniqueid; --CURRENT OF curs_src;
--raise notice '(%)sql1 %',count,sql;
            execute sql;
            sql := 'UPDATE ' || tablename || ' SET clusterId = '
            || clusterId_temp || ' WHERE uniqueid = ' ||rec_dest.uniqueid; --CURRENT OF curs_dest;
--raise notice '(%)sql2 %',count,sql;
            execute sql;
--raise notice 'updating both src (%) and dest (%) with clusterid %', srcuniqueid, rec_dest.uniqueid, clusterid_temp;
        ELSIF (clusterid_temp is null and rec_dest.clusterId IS NOT NULL) THEN
            clusterId_temp = rec_dest.clusterId;
--raise notice 'updating src (%) with clusterid %', uniqueid, clusterid_temp;
            sql := 'UPDATE ' || tablename || ' SET clusterId = '
            || clusterId_temp || ' WHERE uniqueid = ' || srcUniqueId; --CURRENT OF curs_src;
--raise notice '(%)sql3 %',count,sql;
            execute sql;
        ELSIF (clusterId_temp IS NOT NULL AND rec_dest.clusterId IS NULL) THEN
--raise notice 'updating dest (%) with clusterid %', rec_dest.id, clusterid_temp;
            sql := 'UPDATE ' || tablename || ' SET clusterId = '
            || clusterId_temp || ' WHERE uniqueid = ' || rec_dest.uniqueid; --CURRENT OF curs_dest;
--raise notice '(%)sql4 %',count,sql;
            execute sql;
        ELSIF (clusterId_temp != rec_dest.clusterId) THEN -- implicit: clusterId_src IS NOT NULL AND rec_dest.clusterId IS NOT NULL
            sql := 'UPDATE ' || tablename || ' SET clusterId = '
            || rec_dest.clusterid || ' WHERE clusterId = ' || clusterId_temp;
--raise notice '(%) % - sql5 %',count,rec_dest.uniqueid, sql;
            clusterId_temp := rec_dest.clusterId;
            EXECUTe sql;
        END IF;
    end loop;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.cluster_points_buffer(integer, text)
  OWNER TO postgres;


Python script:
import pg
import datetime
con = pg.connect(dbname='signalstrength')
while True:
        idList = con.query("select uniqueid from strength3 where latitude > 0 and clusterid is null limit 250;").getresult()
        if len(idList) == 0:
                break
        for row in idList:
                s = "select * from cluster_points_buffer(%s, 'strength3')" % row[0]
                now = datetime.datetime.now()
                print '({0}) {1}'.format(now.strftime("%H:%M:%S") , s)
                id = con.query( s )
        print 'Going to vacuum at %s' % now.strftime("%H:%M:%S %d-%m-%Y")
        con.query( "vacuum strength3" )
print "Closing db and ending"
con.close()

--sql
insert into strengthpoly
select 3, clusterid, st_astext((st_union(buffered))) as area
from strength3
group by clusterid