deleted 42 characters in body

Source Link

edited Apr 15, 2016 at 3:19

65.4k
29
114
345

I have a postGIS table that looks like this :

id  |  value  | geometry(MultiPolygon)
1      10       ...
1      20       ...
1      30       ...
2      10       ...
2      20       ...
2      30       ...

What I wish to do is to create a new geometry column containing a Multipolygon which is the union of all the other polygons of rows having the same id and a smaller or equal 'value'.

So :

first row 'new_geometry' would be identical to 'geometry'.
Second row 'new_geometry' would be the union of the first and second row 'geometry'.
Third row : union of rows 1,2,3
equal to row 4
etc

I have written something like this:

SELECT t.id, t.val, St_Union(t.geometry)
FROM 
    (SELECT cs.id, cs.value as val, cs.geometry
     FROM
         mytable cs 
         INNER JOIN 
             (SELECT DISTINCT value from myTable) v 
             ON (v.value <= cs.value)
     ) AS t
GROUP BY 
    t.val, t.id;

But the geometry column I get is not aggregated, it is equal to the original geometry column. The aggregation work however if I 'GROUP BY' only by one criteria.

Any help would be greatly appreciated.

I have a postGIS table that looks like this :

id  |  value  | geometry(MultiPolygon)
1      10       ...
1      20       ...
1      30       ...
2      10       ...
2      20       ...
2      30       ...

What I wish to do is to create a new geometry column containing a Multipolygon which is the union of all the other polygons of rows having the same id and a smaller or equal 'value'.

So :

first row 'new_geometry' would be identical to 'geometry'.
Second row 'new_geometry' would be the union of the first and second row 'geometry'.
Third row : union of rows 1,2,3
equal to row 4
etc

I have written something like this:

SELECT t.id, t.val, St_Union(t.geometry)
FROM 
    (SELECT cs.id, cs.value as val, cs.geometry
     FROM
         mytable cs 
         INNER JOIN 
             (SELECT DISTINCT value from myTable) v 
             ON (v.value <= cs.value)
     ) AS t
GROUP BY 
    t.val, t.id;

But the geometry column I get is not aggregated, it is equal to the original geometry column. The aggregation work however if I 'GROUP BY' only by one criteria.

Any help would be greatly appreciated.

I have a postGIS table that looks like this :

id  |  value  | geometry(MultiPolygon)
1      10       ...
1      20       ...
1      30       ...
2      10       ...
2      20       ...
2      30       ...

What I wish to do is to create a new geometry column containing a Multipolygon which is the union of all the other polygons of rows having the same id and a smaller or equal 'value'.

So :

first row 'new_geometry' would be identical to 'geometry'.
Second row 'new_geometry' would be the union of the first and second row 'geometry'.
Third row : union of rows 1,2,3
equal to row 4
etc

I have written something like this:

SELECT t.id, t.val, St_Union(t.geometry)
FROM 
    (SELECT cs.id, cs.value as val, cs.geometry
     FROM
         mytable cs 
         INNER JOIN 
             (SELECT DISTINCT value from myTable) v 
             ON (v.value <= cs.value)
     ) AS t
GROUP BY 
    t.val, t.id;

But the geometry column I get is not aggregated, it is equal to the original geometry column. The aggregation work however if I 'GROUP BY' only by one criteria.

deleted 6 characters in body

Source Link

edited Sep 4, 2015 at 15:29

Istopopoki

277
1
8

I have a postGIS table that looks like this :

id  |  value  | geometry(MultiPolygon)
1      10       ...
1      20       ...
1      30       ...
12      10       ...
12      20       ...
12      30       ...

What I wish to do is to create a new geometry column containing a Multipolygon which is the union of all the other polygons of rows having the same id and a smaller or equal 'value'.

So :

first row 'new_geometry' would be identical to 'geometry'.
Second row 'new_geometry' would be the union of the first and second row 'geometry'.
Third row : union of rows 1,2,3
equal to row 4
etc

I have written something like this:

SELECT t.id, t.val, St_Union(t.geometry)
FROM 
    (SELECT cs.id, cs.value as val, cs.geometry
     FROM
         mytable cs 
         INNER JOIN 
             (SELECT DISTINCT value from myTable) v 
             ON (v.value <= cs.value)
     ) AS t
GROUP BY 
    t.val, t.originid;id;

But the geometry column I get is not aggregated, it is equal to the original geometry column. The aggregation work however if I 'GROUP BY' only by one criteria.

Any help would be greatly appreciated.

I have a postGIS table that looks like this :

id  |  value  | geometry(MultiPolygon)
1      10       ...
1      20       ...
1      30       ...
1      10       ...
1      20       ...
1      30       ...

What I wish to do is to create a new geometry column containing a Multipolygon which is the union of all the other polygons of rows having the same id and a smaller or equal 'value'.

So :

first row 'new_geometry' would be identical to 'geometry'.
Second row 'new_geometry' would be the union of the first and second row 'geometry'.
Third row : union of rows 1,2,3
equal to row 4
etc

I have written something like this:

SELECT t.id, t.val, St_Union(t.geometry)
FROM 
    (SELECT cs.id, cs.value as val, cs.geometry
     FROM
         mytable cs 
         INNER JOIN 
             (SELECT DISTINCT value from myTable) v 
             ON (v.value <= cs.value)
     ) AS t
GROUP BY 
    t.val, t.originid;

But the geometry column I get is not aggregated, it is equal to the original geometry column. The aggregation work however if I 'GROUP BY' only by one criteria.

Any help would be greatly appreciated.

I have a postGIS table that looks like this :

id  |  value  | geometry(MultiPolygon)
1      10       ...
1      20       ...
1      30       ...
2      10       ...
2      20       ...
2      30       ...

What I wish to do is to create a new geometry column containing a Multipolygon which is the union of all the other polygons of rows having the same id and a smaller or equal 'value'.

So :

first row 'new_geometry' would be identical to 'geometry'.
Second row 'new_geometry' would be the union of the first and second row 'geometry'.
Third row : union of rows 1,2,3
equal to row 4
etc

I have written something like this:

SELECT t.id, t.val, St_Union(t.geometry)
FROM 
    (SELECT cs.id, cs.value as val, cs.geometry
     FROM
         mytable cs 
         INNER JOIN 
             (SELECT DISTINCT value from myTable) v 
             ON (v.value <= cs.value)
     ) AS t
GROUP BY 
    t.val, t.id;

But the geometry column I get is not aggregated, it is equal to the original geometry column. The aggregation work however if I 'GROUP BY' only by one criteria.

Any help would be greatly appreciated.

Source Link

asked Sep 4, 2015 at 13:39

Istopopoki

277
1
8

postGIS SQL request with aggregating ST_Union()

I have a postGIS table that looks like this :

id  |  value  | geometry(MultiPolygon)
1      10       ...
1      20       ...
1      30       ...
1      10       ...
1      20       ...
1      30       ...

What I wish to do is to create a new geometry column containing a Multipolygon which is the union of all the other polygons of rows having the same id and a smaller or equal 'value'.

So :

first row 'new_geometry' would be identical to 'geometry'.
Second row 'new_geometry' would be the union of the first and second row 'geometry'.
Third row : union of rows 1,2,3
equal to row 4
etc

I have written something like this:

SELECT t.id, t.val, St_Union(t.geometry)
FROM 
    (SELECT cs.id, cs.value as val, cs.geometry
     FROM
         mytable cs 
         INNER JOIN 
             (SELECT DISTINCT value from myTable) v 
             ON (v.value <= cs.value)
     ) AS t
GROUP BY 
    t.val, t.originid;

But the geometry column I get is not aggregated, it is equal to the original geometry column. The aggregation work however if I 'GROUP BY' only by one criteria.

Any help would be greatly appreciated.

postgis postgis-2.0

Stack Exchange Network

Return to Question

postGIS SQL request with aggregating ST_Union()