st_clusterwithin on postgis 2

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

st_clusterwithin on postgis 2

Paolo Importuni
Hi all,
I need to run a query on my postgres/postgis (9.3/2.0) that uses ST_CLUSTERWITHIN function. This function is available since postgis 2.2.0 so I should do a soft upgrade on my ubuntu server.  Since we have a kind of customer demo in  a few days and have no much time left, I am not willing to change our setup right now, and I'd rather do it after that meeting.
The question is: is there a way to aggregate geometries like ST_ClusterWIthin does? Anybody can provide any working examples?

Thanks and regards

Paolo I.

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: st_clusterwithin on postgis 2

Birgit Laggner-2
Hi Paolo,

here an example how it could work (mind that you have to replace the ??). You could also recode the DO block into a function if you would like that better.

DO $$
DECLARE distance numeric; t_schema varchar; input_table varchar; output_table varchar; r record; gid integer; geom geometry; c record; c_gid integer[]; c_geom geometry;
BEGIN

distance := ??;
t_schema := '??';
input_table := '??';
output_table := '??';


CREATE TEMP TABLE clustered (gid integer) ON COMMIT DROP;

EXECUTE 'SELECT gid, geom FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table) INTO r;

gid := r.gid;
geom := r.geom;

EXECUTE 'INSERT INTO clustered SELECT '||gid;

EXECUTE 'SELECT array_agg(gid) as gid, ST_ForceCollection(ST_Collect(ST_Collect(geom),$2)) as geom FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table)||' WHERE gid != $1 AND ST_DWithin($2, geom, $3)' INTO c USING gid, geom, distance;

c_gid := c.gid;
c_geom := c.geom;

EXECUTE 'INSERT INTO '||quote_ident(t_schema)||'.'||quote_ident(output_table)||' VALUES($1)' USING c_geom;

EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING c_gid;

LOOP
    BEGIN
    EXECUTE 'SELECT gid, geom FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table)||'
             WHERE gid NOT IN (SELECT gid FROM clustered)' INTO r;

    gid := r.gid;
    geom := r.geom;

    EXECUTE 'INSERT INTO clustered SELECT '||gid;

    EXECUTE 'SELECT array_agg(gid) as gid, ST_ForceCollection(ST_Collect(ST_Collect(geom),$1)) as geom
         FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table)||'
         WHERE gid NOT IN (SELECT gid FROM clustered) AND ST_DWithin($1, geom, $2)'
         INTO c USING geom, distance;

    c_gid := c.gid;
    c_geom := c.geom;

    EXECUTE 'INSERT INTO '||quote_ident(t_schema)||'.'||quote_ident(output_table)||'
             VALUES ($1)' USING c_geom;

    EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING c_gid;

    EXCEPTION WHEN SQLSTATE '22004' THEN EXIT;
    END;
END LOOP;

END $$

Regards,

Birgit

    
Am 21.09.2016 um 07:47 schrieb Paolo Importuni:
Hi all,
I need to run a query on my postgres/postgis (9.3/2.0) that uses ST_CLUSTERWITHIN function. This function is available since postgis 2.2.0 so I should do a soft upgrade on my ubuntu server.  Since we have a kind of customer demo in  a few days and have no much time left, I am not willing to change our setup right now, and I'd rather do it after that meeting.
The question is: is there a way to aggregate geometries like ST_ClusterWIthin does? Anybody can provide any working examples?

Thanks and regards

Paolo I.


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: st_clusterwithin on postgis 2

Birgit Laggner-2
Hi Paolo,

sorry, I did not really think it through, but now the changed and tested example:

DO $$
DECLARE distance numeric; t_schema varchar; input_table varchar; output_table varchar; r record; gid integer; geom geometry; c record; c_gid integer[]; c_geom geometry[]; i record;
BEGIN

distance := 200;
t_schema := 'p_sam';
input_table := 'cluster_test_input';
output_table := 'cluster_test_output';


CREATE TEMP TABLE clustered (gid integer) ON COMMIT DROP;

EXECUTE 'SELECT gid, geom FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table) INTO r;

gid := r.gid;
geom := r.geom;

EXECUTE 'INSERT INTO clustered SELECT '||gid;

LOOP

    EXECUTE 'SELECT array_agg(gid) as gid, array_agg(geom) as geom
             FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table)||'
             WHERE gid NOT IN (SELECT gid FROM clustered) AND ST_DWithin($1, geom, $2)'
             INTO c USING geom, distance;

    c_gid := c.gid;
    c_geom := c.geom || array[geom]::geometry[];

    EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING c_gid;

    geom := ST_ForceCollection(ST_Collect(c_geom));
       
    EXIT WHEN c IS NULL;

END LOOP;

EXECUTE 'INSERT INTO '||quote_ident(t_schema)||'.'||quote_ident(output_table)||' VALUES($1)' USING geom;

LOOP
    BEGIN
    EXECUTE 'SELECT gid, geom FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table)||'
             WHERE gid NOT IN (SELECT gid FROM clustered)' INTO r;

    gid := r.gid;
    geom := r.geom;

    EXECUTE 'INSERT INTO clustered SELECT '||gid;

    LOOP

        EXECUTE 'SELECT array_agg(gid) as gid, array_agg(geom) as geom
             FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table)||'
             WHERE gid NOT IN (SELECT gid FROM clustered) AND ST_DWithin($1, geom, $2)'
             INTO c USING geom, distance;

        c_gid := c.gid;
        c_geom := c.geom || array[geom]::geometry[];

        EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING c_gid;

        geom := ST_ForceCollection(ST_Collect(c_geom));
       
        EXIT WHEN c IS NULL;

    END LOOP;

    EXECUTE 'INSERT INTO '||quote_ident(t_schema)||'.'||quote_ident(output_table)||'
             VALUES ($1)' USING geom;

    EXCEPTION WHEN SQLSTATE '22004' THEN EXIT;
    END;
END LOOP;

END $$


Regards,

Birgit


Am 21.09.2016 um 14:53 schrieb Birgit Laggner:
Hi Paolo,

here an example how it could work (mind that you have to replace the ??). You could also recode the DO block into a function if you would like that better.

DO $$
DECLARE distance numeric; t_schema varchar; input_table varchar; output_table varchar; r record; gid integer; geom geometry; c record; c_gid integer[]; c_geom geometry;
BEGIN

distance := ??;
t_schema := '??';
input_table := '??';
output_table := '??';


CREATE TEMP TABLE clustered (gid integer) ON COMMIT DROP;

EXECUTE 'SELECT gid, geom FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table) INTO r;

gid := r.gid;
geom := r.geom;

EXECUTE 'INSERT INTO clustered SELECT '||gid;

EXECUTE 'SELECT array_agg(gid) as gid, ST_ForceCollection(ST_Collect(ST_Collect(geom),$2)) as geom FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table)||' WHERE gid != $1 AND ST_DWithin($2, geom, $3)' INTO c USING gid, geom, distance;

c_gid := c.gid;
c_geom := c.geom;

EXECUTE 'INSERT INTO '||quote_ident(t_schema)||'.'||quote_ident(output_table)||' VALUES($1)' USING c_geom;

EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING c_gid;

LOOP
    BEGIN
    EXECUTE 'SELECT gid, geom FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table)||'
             WHERE gid NOT IN (SELECT gid FROM clustered)' INTO r;

    gid := r.gid;
    geom := r.geom;

    EXECUTE 'INSERT INTO clustered SELECT '||gid;

    EXECUTE 'SELECT array_agg(gid) as gid, ST_ForceCollection(ST_Collect(ST_Collect(geom),$1)) as geom
         FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table)||'
         WHERE gid NOT IN (SELECT gid FROM clustered) AND ST_DWithin($1, geom, $2)'
         INTO c USING geom, distance;

    c_gid := c.gid;
    c_geom := c.geom;

    EXECUTE 'INSERT INTO '||quote_ident(t_schema)||'.'||quote_ident(output_table)||'
             VALUES ($1)' USING c_geom;

    EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING c_gid;

    EXCEPTION WHEN SQLSTATE '22004' THEN EXIT;
    END;
END LOOP;

END $$

Regards,

Birgit
Am 21.09.2016 um 07:47 schrieb Paolo Importuni:
Hi all,
I need to run a query on my postgres/postgis (9.3/2.0) that uses ST_CLUSTERWITHIN function. This function is available since postgis 2.2.0 so I should do a soft upgrade on my ubuntu server.  Since we have a kind of customer demo in  a few days and have no much time left, I am not willing to change our setup right now, and I'd rather do it after that meeting.
The question is: is there a way to aggregate geometries like ST_ClusterWIthin does? Anybody can provide any working examples?

Thanks and regards

Paolo I.


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: st_clusterwithin on postgis 2

Paolo Importuni
Hi Birgit, thank you very much for your reply.
I'm going to test your code asap and let you know about it.
Regards,
Paolo

2016-09-22 9:47 GMT+02:00 Birgit Laggner <[hidden email]>:
Hi Paolo,

sorry, I did not really think it through, but now the changed and tested example:

DO $$
DECLARE distance numeric; t_schema varchar; input_table varchar; output_table varchar; r record; gid integer; geom geometry; c record; c_gid integer[]; c_geom geometry[]; i record;
BEGIN

distance := 200;
t_schema := 'p_sam';
input_table := 'cluster_test_input';
output_table := 'cluster_test_output';


CREATE TEMP TABLE clustered (gid integer) ON COMMIT DROP;

EXECUTE 'SELECT gid, geom FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table) INTO r;

gid := r.gid;
geom := r.geom;

EXECUTE 'INSERT INTO clustered SELECT '||gid;

LOOP

    EXECUTE 'SELECT array_agg(gid) as gid, array_agg(geom) as geom
             FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table)||'
             WHERE gid NOT IN (SELECT gid FROM clustered) AND ST_DWithin($1, geom, $2)'
             INTO c USING geom, distance;

    c_gid := c.gid;
    c_geom := c.geom || array[geom]::geometry[];

    EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING c_gid;

    geom := ST_ForceCollection(ST_Collect(c_geom));
       
    EXIT WHEN c IS NULL;

END LOOP;

EXECUTE 'INSERT INTO '||quote_ident(t_schema)||'.'||quote_ident(output_table)||' VALUES($1)' USING geom;

LOOP
    BEGIN
    EXECUTE 'SELECT gid, geom FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table)||'
             WHERE gid NOT IN (SELECT gid FROM clustered)' INTO r;

    gid := r.gid;
    geom := r.geom;

    EXECUTE 'INSERT INTO clustered SELECT '||gid;

    LOOP

        EXECUTE 'SELECT array_agg(gid) as gid, array_agg(geom) as geom
             FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table)||'
             WHERE gid NOT IN (SELECT gid FROM clustered) AND ST_DWithin($1, geom, $2)'
             INTO c USING geom, distance;

        c_gid := c.gid;
        c_geom := c.geom || array[geom]::geometry[];

        EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING c_gid;

        geom := ST_ForceCollection(ST_Collect(c_geom));
       
        EXIT WHEN c IS NULL;

    END LOOP;

    EXECUTE 'INSERT INTO '||quote_ident(t_schema)||'.'||quote_ident(output_table)||'
             VALUES ($1)' USING geom;

    EXCEPTION WHEN SQLSTATE '22004' THEN EXIT;
    END;
END LOOP;

END $$


Regards,

Birgit

    
Am 21.09.2016 um 14:53 schrieb Birgit Laggner:
Hi Paolo,

here an example how it could work (mind that you have to replace the ??). You could also recode the DO block into a function if you would like that better.

DO $$
DECLARE distance numeric; t_schema varchar; input_table varchar; output_table varchar; r record; gid integer; geom geometry; c record; c_gid integer[]; c_geom geometry;
BEGIN

distance := ??;
t_schema := '??';
input_table := '??';
output_table := '??';


CREATE TEMP TABLE clustered (gid integer) ON COMMIT DROP;

EXECUTE 'SELECT gid, geom FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table) INTO r;

gid := r.gid;
geom := r.geom;

EXECUTE 'INSERT INTO clustered SELECT '||gid;

EXECUTE 'SELECT array_agg(gid) as gid, ST_ForceCollection(ST_Collect(ST_Collect(geom),$2)) as geom FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table)||' WHERE gid != $1 AND ST_DWithin($2, geom, $3)' INTO c USING gid, geom, distance;

c_gid := c.gid;
c_geom := c.geom;

EXECUTE 'INSERT INTO '||quote_ident(t_schema)||'.'||quote_ident(output_table)||' VALUES($1)' USING c_geom;

EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING c_gid;

LOOP
    BEGIN
    EXECUTE 'SELECT gid, geom FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table)||'
             WHERE gid NOT IN (SELECT gid FROM clustered)' INTO r;

    gid := r.gid;
    geom := r.geom;

    EXECUTE 'INSERT INTO clustered SELECT '||gid;

    EXECUTE 'SELECT array_agg(gid) as gid, ST_ForceCollection(ST_Collect(ST_Collect(geom),$1)) as geom
         FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table)||'
         WHERE gid NOT IN (SELECT gid FROM clustered) AND ST_DWithin($1, geom, $2)'
         INTO c USING geom, distance;

    c_gid := c.gid;
    c_geom := c.geom;

    EXECUTE 'INSERT INTO '||quote_ident(t_schema)||'.'||quote_ident(output_table)||'
             VALUES ($1)' USING c_geom;

    EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING c_gid;

    EXCEPTION WHEN SQLSTATE '22004' THEN EXIT;
    END;
END LOOP;

END $$

Regards,

Birgit
Am 21.09.2016 um 07:47 schrieb Paolo Importuni:
Hi all,
I need to run a query on my postgres/postgis (9.3/2.0) that uses ST_CLUSTERWITHIN function. This function is available since postgis 2.2.0 so I should do a soft upgrade on my ubuntu server.  Since we have a kind of customer demo in  a few days and have no much time left, I am not willing to change our setup right now, and I'd rather do it after that meeting.
The question is: is there a way to aggregate geometries like ST_ClusterWIthin does? Anybody can provide any working examples?

Thanks and regards

Paolo I.


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users