Revisions to Spatial clustering with PostGIS?

added 398 characters in body

Source Link

edited Jul 5, 2011 at 9:19

6.9k
2
30
49

CREATE OR REPLACE FUNCTION get_domains_n(lid_inlname integervarchar, geom varchar, gid varchar, radius numeric)
    RETURNS integerSETOF record AS
$$
DECLARE
    lid_new    integer;
    dmn_number integer := 1;
    outr       record;
    innr       record;
    r          record;
BEGIN

    DROP TABLE IF EXISTS tmp;
    CREATEEXECUTE 'CREATE TEMPORARY TABLE tmp AS SELECT lgid'||gid||', geom'||geom||' FROM g_lgeom WHERE lid = lid_in;'||lname;
    ALTER TABLE tmp ADD COLUMN dmn integer;
    ALTER TABLE tmp ADD COLUMN chk boolean DEFAULT FALSE;
    UPDATEEXECUTE 'UPDATE tmp SET dmn = dmn_number'||dmn_number||', chk = FALSE WHERE lgid'||gid||' = (SELECT MIN(lgid'||gid||') FROM tmp);';

    LOOP
        LOOP
            FOR outr IN SELECTEXECUTE lgid'SELECT '||gid||' AS gid, '||geom||' AS geom FROM tmp WHERE dmn = dmn_number'||dmn_number||' AND NOT chkchk' LOOP
                FOR innr IN SELECTEXECUTE lgid'SELECT '||gid||' AS gid, '||geom||' AS geom FROM tmp WHERE dmn IS NULLNULL' LOOP
                    --IF ST_DWithin(ST_Transform(ST_SetSRID(outr.geom, 4326), 3785), ST_Transform(ST_SetSRID(innr.geom, 4326), 3785), radius) THEN
                    --IF ST_DWithin(outr.geom, innr.geom, radius) THEN
                        UPDATEEXECUTE 'UPDATE tmp SET dmn = dmn_number'||dmn_number||', chk = FALSE WHERE lgid'||gid||' = innr'||innr.lgid;gid;
                    END IF;
                END LOOP;
                UPDATEEXECUTE 'UPDATE tmp SET chk = TRUE WHERE lgid'||gid||' = outr'||outr.lgid;gid;
            END LOOP;
            SELECT INTO r dmn FROM tmp WHERE dmn = dmn_number AND NOT chk LIMIT 1;
            EXIT WHEN NOT FOUND;
       END LOOP;
       SELECT INTO r dmn FROM tmp WHERE dmn IS NULL LIMIT 1;
       IF FOUND THEN
           dmn_number := dmn_number + 1;
           UPDATEEXECUTE 'UPDATE tmp SET dmn = dmn_number'||dmn_number||', chk = FALSE WHERE lgid'||gid||' = (SELECT MIN(lgid'||gid||') FROM tmp WHERE dmn IS NULL LIMIT 1);';
       ELSE
           EXIT;
       END IF;
    END LOOP;

    SELECT nextval ('g_layer_lid_seq') INTO lid_new;

    INSERT INTO g_lgeom(lid,RETURN geom)QUERY SELECTEXECUTE lid_new,'SELECT ST_ConvexHull(ST_Collect(geom'||geom||')) FROM tmp GROUP BYby dmn;dmn';

    RETURN lid_new;
    RETURN;
END
$$
LANGUAGE plpgsql;

Example of using this function:

SELECT * FROM get_domains_n('poi', 'wkb_geometry', 'ogc_fid', 14000) AS g(gm geometry)

'poi' - name of layer, 'wkb_geometry' - name of geometry column, 'ogc_fid' - primary key of table, 14000 - cluster distance.

CREATE OR REPLACE FUNCTION get_domains_n(lid_in integer, radius numeric)
RETURNS integer AS
$$
DECLARE
    lid_new    integer;
    dmn_number integer := 1;
    outr       record;
    innr       record;
    r          record;
BEGIN

    DROP TABLE IF EXISTS tmp;
    CREATE TEMPORARY TABLE tmp AS SELECT lgid, geom FROM g_lgeom WHERE lid = lid_in;
    ALTER TABLE tmp ADD COLUMN dmn integer;
    ALTER TABLE tmp ADD COLUMN chk boolean DEFAULT FALSE;
    UPDATE tmp SET dmn = dmn_number, chk = FALSE WHERE lgid = (SELECT MIN(lgid) FROM tmp);

    LOOP
        LOOP
            FOR outr IN SELECT lgid, geom FROM tmp WHERE dmn = dmn_number AND NOT chk LOOP
                FOR innr IN SELECT lgid, geom FROM tmp WHERE dmn IS NULL LOOP
                    --IF ST_DWithin(ST_Transform(ST_SetSRID(outr.geom, 4326), 3785), ST_Transform(ST_SetSRID(innr.geom, 4326), 3785), radius) THEN
                    IF ST_DWithin(outr.geom, innr.geom, radius) THEN
                        UPDATE tmp SET dmn = dmn_number, chk = FALSE WHERE lgid = innr.lgid;
                    END IF;
                END LOOP;
                UPDATE tmp SET chk = TRUE WHERE lgid = outr.lgid;
            END LOOP;
            SELECT INTO r dmn FROM tmp WHERE dmn = dmn_number AND NOT chk LIMIT 1;
            EXIT WHEN NOT FOUND;
       END LOOP;
       SELECT INTO r dmn FROM tmp WHERE dmn IS NULL LIMIT 1;
       IF FOUND THEN
           dmn_number := dmn_number + 1;
           UPDATE tmp SET dmn = dmn_number, chk = FALSE WHERE lgid = (SELECT MIN(lgid) FROM tmp WHERE dmn IS NULL LIMIT 1);
       ELSE
           EXIT;
       END IF;
    END LOOP;

    SELECT nextval ('g_layer_lid_seq') INTO lid_new;

    INSERT INTO g_lgeom(lid, geom) SELECT lid_new, ST_ConvexHull(ST_Collect(geom)) FROM tmp GROUP BY dmn;

    RETURN lid_new;
    
END
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_domains_n(lname varchar, geom varchar, gid varchar, radius numeric)
    RETURNS SETOF record AS
$$
DECLARE
    lid_new    integer;
    dmn_number integer := 1;
    outr       record;
    innr       record;
    r          record;
BEGIN

    DROP TABLE IF EXISTS tmp;
    EXECUTE 'CREATE TEMPORARY TABLE tmp AS SELECT '||gid||', '||geom||' FROM '||lname;
    ALTER TABLE tmp ADD COLUMN dmn integer;
    ALTER TABLE tmp ADD COLUMN chk boolean DEFAULT FALSE;
    EXECUTE 'UPDATE tmp SET dmn = '||dmn_number||', chk = FALSE WHERE '||gid||' = (SELECT MIN('||gid||') FROM tmp)';

    LOOP
        LOOP
            FOR outr IN EXECUTE 'SELECT '||gid||' AS gid, '||geom||' AS geom FROM tmp WHERE dmn = '||dmn_number||' AND NOT chk' LOOP
                FOR innr IN EXECUTE 'SELECT '||gid||' AS gid, '||geom||' AS geom FROM tmp WHERE dmn IS NULL' LOOP
                    IF ST_DWithin(ST_Transform(ST_SetSRID(outr.geom, 4326), 3785), ST_Transform(ST_SetSRID(innr.geom, 4326), 3785), radius) THEN
                    --IF ST_DWithin(outr.geom, innr.geom, radius) THEN
                        EXECUTE 'UPDATE tmp SET dmn = '||dmn_number||', chk = FALSE WHERE '||gid||' = '||innr.gid;
                    END IF;
                END LOOP;
                EXECUTE 'UPDATE tmp SET chk = TRUE WHERE '||gid||' = '||outr.gid;
            END LOOP;
            SELECT INTO r dmn FROM tmp WHERE dmn = dmn_number AND NOT chk LIMIT 1;
            EXIT WHEN NOT FOUND;
       END LOOP;
       SELECT INTO r dmn FROM tmp WHERE dmn IS NULL LIMIT 1;
       IF FOUND THEN
           dmn_number := dmn_number + 1;
           EXECUTE 'UPDATE tmp SET dmn = '||dmn_number||', chk = FALSE WHERE '||gid||' = (SELECT MIN('||gid||') FROM tmp WHERE dmn IS NULL LIMIT 1)';
       ELSE
           EXIT;
       END IF;
    END LOOP;

    RETURN QUERY EXECUTE 'SELECT ST_ConvexHull(ST_Collect('||geom||')) FROM tmp GROUP by dmn';

    RETURN;
END
$$
LANGUAGE plpgsql;

Example of using this function:

SELECT * FROM get_domains_n('poi', 'wkb_geometry', 'ogc_fid', 14000) AS g(gm geometry)

'poi' - name of layer, 'wkb_geometry' - name of geometry column, 'ogc_fid' - primary key of table, 14000 - cluster distance.

added 160 characters in body

Source Link

edited Jul 5, 2011 at 4:54

drnextgis

6.9k
2
30
49

CREATE OR REPLACE FUNCTION get_domains_n(lid_in integer, radius numeric)
RETURNS integer AS
$$
DECLARE
    lid_new    integer;
    dmn_number integer := 1;
    outr       record;
    innr       record;
    r          record;
BEGIN

    DROP TABLE IF EXISTS tmp;
    CREATE TEMPORARY TABLE tmp AS SELECT lgid, geom FROM g_lgeom WHERE lid = lid_in;
    ALTER TABLE tmp ADD COLUMN dmn integer;
    ALTER TABLE tmp ADD COLUMN chk boolean DEFAULT FALSE;
    UPDATE tmp SET dmn = dmn_number, chk = FALSE WHERE lgid = (SELECT MIN(lgid) FROM tmp);

    LOOP
        LOOP
            FOR outr IN SELECT lgid, geom FROM tmp WHERE dmn = dmn_number AND NOT chk LOOP
                FOR innr IN SELECT lgid, geom FROM tmp WHERE dmn IS NULL LOOP
                    --IF ST_DWithin(ST_Transform(ST_SetSRID(outr.geom, 4326), 3785), ST_Transform(ST_SetSRID(innr.geom, 4326), 3785), radius) THEN
                    IF ST_DWithin(outr.geom, innr.geom, radius) THEN
                        UPDATE tmp SET dmn = dmn_number, chk = FALSE WHERE lgid = innr.lgid;
                    END IF;
                END LOOP;
                UPDATE tmp SET chk = TRUE WHERE lgid = outr.lgid;
            END LOOP;
            SELECT INTO r dmn FROM tmp WHERE dmn = dmn_number AND NOT chk LIMIT 1;
            EXIT WHEN NOT FOUND;
       END LOOP;
       SELECT INTO r dmn FROM tmp WHERE dmn IS NULL LIMIT 1;
       IF FOUND THEN
           dmn_number := dmn_number + 1;
           UPDATE tmp SET dmn = dmn_number, chk = FALSE WHERE lgid = (SELECT MIN(lgid) FROM tmp WHERE dmn IS NULL LIMIT 1);
       ELSE
           EXIT;
       END IF;
    END LOOP;

    SELECT nextval ('g_layer_lid_seq') INTO lid_new;

    INSERT INTO g_lgeom(lid, geom) SELECT lid_new, ST_ConvexHull(ST_Collect(geom)) FROM tmp GROUP BY dmn;

    RETURN lid_new;
    
END
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_domains_n(lid_in integer, radius numeric)
RETURNS integer AS
$$
DECLARE
    lid_new    integer;
    dmn_number integer := 1;
    outr       record;
    innr       record;
    r          record;
BEGIN

DROP TABLE IF EXISTS tmp;
CREATE TEMPORARY TABLE tmp AS SELECT lgid, geom FROM g_lgeom WHERE lid = lid_in;
ALTER TABLE tmp ADD COLUMN dmn integer;
ALTER TABLE tmp ADD COLUMN chk boolean DEFAULT FALSE;
UPDATE tmp SET dmn = dmn_number, chk = FALSE WHERE lgid = (SELECT MIN(lgid) FROM tmp);

LOOP
    LOOP
        FOR outr IN SELECT lgid, geom FROM tmp WHERE dmn = dmn_number AND NOT chk LOOP
            FOR innr IN SELECT lgid, geom FROM tmp WHERE dmn IS NULL LOOP
                --IF ST_DWithin(ST_Transform(ST_SetSRID(outr.geom, 4326), 3785), ST_Transform(ST_SetSRID(innr.geom, 4326), 3785), radius) THEN
                IF ST_DWithin(outr.geom, innr.geom, radius) THEN
                    UPDATE tmp SET dmn = dmn_number, chk = FALSE WHERE lgid = innr.lgid;
                END IF;
        END LOOP;
            UPDATE tmp SET chk = TRUE WHERE lgid = outr.lgid;
    END LOOP;
    SELECT INTO r dmn FROM tmp WHERE dmn = dmn_number AND NOT chk LIMIT 1;
    EXIT WHEN NOT FOUND;
END LOOP;
SELECT INTO r dmn FROM tmp WHERE dmn IS NULL LIMIT 1;
IF FOUND THEN
    dmn_number := dmn_number + 1;
    UPDATE tmp SET dmn = dmn_number, chk = FALSE WHERE lgid = (SELECT MIN(lgid) FROM tmp WHERE dmn IS NULL LIMIT 1);
ELSE
    EXIT;
END IF;
END LOOP;

SELECT nextval ('g_layer_lid_seq') INTO lid_new;

INSERT INTO g_lgeom(lid, geom) SELECT lid_new, ST_ConvexHull(ST_Collect(geom)) FROM tmp GROUP BY dmn;

RETURN lid_new;
    
END
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_domains_n(lid_in integer, radius numeric)
RETURNS integer AS
$$
DECLARE
    lid_new    integer;
    dmn_number integer := 1;
    outr       record;
    innr       record;
    r          record;
BEGIN

    DROP TABLE IF EXISTS tmp;
    CREATE TEMPORARY TABLE tmp AS SELECT lgid, geom FROM g_lgeom WHERE lid = lid_in;
    ALTER TABLE tmp ADD COLUMN dmn integer;
    ALTER TABLE tmp ADD COLUMN chk boolean DEFAULT FALSE;
    UPDATE tmp SET dmn = dmn_number, chk = FALSE WHERE lgid = (SELECT MIN(lgid) FROM tmp);

    LOOP
        LOOP
            FOR outr IN SELECT lgid, geom FROM tmp WHERE dmn = dmn_number AND NOT chk LOOP
                FOR innr IN SELECT lgid, geom FROM tmp WHERE dmn IS NULL LOOP
                    --IF ST_DWithin(ST_Transform(ST_SetSRID(outr.geom, 4326), 3785), ST_Transform(ST_SetSRID(innr.geom, 4326), 3785), radius) THEN
                    IF ST_DWithin(outr.geom, innr.geom, radius) THEN
                        UPDATE tmp SET dmn = dmn_number, chk = FALSE WHERE lgid = innr.lgid;
                    END IF;
                END LOOP;
                UPDATE tmp SET chk = TRUE WHERE lgid = outr.lgid;
            END LOOP;
            SELECT INTO r dmn FROM tmp WHERE dmn = dmn_number AND NOT chk LIMIT 1;
            EXIT WHEN NOT FOUND;
       END LOOP;
       SELECT INTO r dmn FROM tmp WHERE dmn IS NULL LIMIT 1;
       IF FOUND THEN
           dmn_number := dmn_number + 1;
           UPDATE tmp SET dmn = dmn_number, chk = FALSE WHERE lgid = (SELECT MIN(lgid) FROM tmp WHERE dmn IS NULL LIMIT 1);
       ELSE
           EXIT;
       END IF;
    END LOOP;

    SELECT nextval ('g_layer_lid_seq') INTO lid_new;

    INSERT INTO g_lgeom(lid, geom) SELECT lid_new, ST_ConvexHull(ST_Collect(geom)) FROM tmp GROUP BY dmn;

    RETURN lid_new;
    
END
$$
LANGUAGE plpgsql;

Source Link

created Jul 5, 2011 at 4:40

drnextgis

6.9k
2
30
49

I've written function that calculates clusters of features based on distance between them and build convex hull over this features:

CREATE OR REPLACE FUNCTION get_domains_n(lid_in integer, radius numeric)
RETURNS integer AS
$$
DECLARE
    lid_new    integer;
    dmn_number integer := 1;
    outr       record;
    innr       record;
    r          record;
BEGIN

DROP TABLE IF EXISTS tmp;
CREATE TEMPORARY TABLE tmp AS SELECT lgid, geom FROM g_lgeom WHERE lid = lid_in;
ALTER TABLE tmp ADD COLUMN dmn integer;
ALTER TABLE tmp ADD COLUMN chk boolean DEFAULT FALSE;
UPDATE tmp SET dmn = dmn_number, chk = FALSE WHERE lgid = (SELECT MIN(lgid) FROM tmp);

LOOP
    LOOP
        FOR outr IN SELECT lgid, geom FROM tmp WHERE dmn = dmn_number AND NOT chk LOOP
            FOR innr IN SELECT lgid, geom FROM tmp WHERE dmn IS NULL LOOP
                --IF ST_DWithin(ST_Transform(ST_SetSRID(outr.geom, 4326), 3785), ST_Transform(ST_SetSRID(innr.geom, 4326), 3785), radius) THEN
                IF ST_DWithin(outr.geom, innr.geom, radius) THEN
                    UPDATE tmp SET dmn = dmn_number, chk = FALSE WHERE lgid = innr.lgid;
                END IF;
        END LOOP;
            UPDATE tmp SET chk = TRUE WHERE lgid = outr.lgid;
    END LOOP;
    SELECT INTO r dmn FROM tmp WHERE dmn = dmn_number AND NOT chk LIMIT 1;
    EXIT WHEN NOT FOUND;
END LOOP;
SELECT INTO r dmn FROM tmp WHERE dmn IS NULL LIMIT 1;
IF FOUND THEN
    dmn_number := dmn_number + 1;
    UPDATE tmp SET dmn = dmn_number, chk = FALSE WHERE lgid = (SELECT MIN(lgid) FROM tmp WHERE dmn IS NULL LIMIT 1);
ELSE
    EXIT;
END IF;
END LOOP;

SELECT nextval ('g_layer_lid_seq') INTO lid_new;

INSERT INTO g_lgeom(lid, geom) SELECT lid_new, ST_ConvexHull(ST_Collect(geom)) FROM tmp GROUP BY dmn;

RETURN lid_new;
    
END
$$
LANGUAGE plpgsql;

The result of using this function:

enter image description here

Stack Exchange Network

Return to Answer