Separate a polygon in different polygons depending of the distance to another polygon I have a reference polygon "R", and others polygons (in grey).

I want to separate each grey polygon in multiple polygons, depending on the distance to R with different Buffers :

• The red line is R with a 5 meters buffer
• The green line is R with a 20 meters buffer
• The purple line is R with a 50 meters buffer

I don't know how to achieve this simply, because in this case, the smaller grey polygon should be split into 3 polygons, and I'm able to split it in only 2 in one query.

If I have 3 buffer, should I do 3 different queries to fill my table, or is there a way to do it in one time ?

The criteria for the splitting is the distance to R.

The small grey polygon can be split in 3 polygons, determined by : - The area before the red line - The area between red and green line - The area after the green line

My full query is : -- 5m zone

``````create table splited_polygons as
select p.id, p.nom, 5 as distance, ST_Intersection(ST_MakeValid(ST_Buffer(h.geom::geography, 5)::geometry), ST_MakeValid(p.geom)) as geom
from polygons p, hydro h;
``````

-- 5m-20m zone

``````insert into splited_polygons (id, nom, distance, geom)
select p.id, p.nom, 20 as distance, ST_Intersection(ST_MakeValid(p.geom), ST_Difference(ST_Buffer(h.geom::geography, 20)::geometry, ST_Buffer(h.geom::geography, 5)::geometry)) as geom
from polygons p, hydro h;
``````

-- 20m-50m zone

``````insert into splited_polygons (id, nom, distance, geom)
select p.id, p.nom, 50 as distance, ST_Intersection(ST_MakeValid(p.geom), ST_Difference(ST_Buffer(h.geom::geography, 50)::geometry, ST_Buffer(h.geom::geography, 20)::geometry)) as geom
from polygons p, hydro h;
``````

-- > 50m zone

``````insert into splited_polygons (id, nom, distance, geom)
select p.id, p.nom, 100 as distance, ST_Difference(ST_MakeValid(p.geom), ST_Buffer(h.geom::geography, 50)::geometry) as geom
from polygons p, hydro h;
``````
• Can't you use another software such as qgis or are you under the obligation to do it with SQL queries? – julien Nov 20 '13 at 16:08
• The solution is probably a rCTE. – Jakub Kania Nov 21 '13 at 13:43

This is how you can easily create rings around the polygon:

``````WITH start AS(
SELECT ST_MAKEPOLYGON(ST_GEOMFROMTEXT('LINESTRING(0 0, 2 0, 1 5, 0 0)')) as point)
,ranges AS(
VALUES (1),(5),(10),(25))
,buffers AS(
SELECT r.column1 as id, ST_BUFFER(s.point,r.column1) as buffer
FROM start AS s
CROSS JOIN ranges AS r),
rings AS(
SELECT DISTINCT ON (a.id) a.id,CASE WHEN b.id IS NULL THEN a.buffer ELSE ST_DIFFERENCE(a.buffer,b.buffer) END
FROM buffers a
LEFT JOIN buffers b
ON a.id>b.id
ORDER BY a.id,b.id DESC)
SELECT * FROM rings;
``````

Once you have them you can use `ST_DIFFRENCE` to get the polygons you need. So it can be all done in one query no matter how many zones you have.

Probably the answer from Jakub Kania is more elegant but your approach it's correct. I really don't understand why are you using st_difference but I try to make one query from your approach (not tested).

``````create table splited_polygons as
SELECT
p.id,
p.nom,
foo.distance,
(ST_DUMP(st_intersection(foo.geom, p.geom))).geom::geometry (Polygon, YOUR_EPSG) as geom
FROM
(select
5 as distance,
(ST_DUMP(ST_Buffer(h.geom, 5))).geom::geometry (Polygon, YOUR_EPSG) as geom
from
hydro h
UNION
select
20 as distance,
(ST_DUMP(ST_Buffer(h.geom, 20))).geom::geometry (Polygon, YOUR_EPSG) as geom
from
hydro h
UNION
select
50 as distance,
(ST_DUMP(ST_Buffer(h.geom, 50))).geom::geometry (Polygon, YOUR_EPSG) as geom
from
hydro h) AS foo,
polygons AS p

WHERE
st_intersects(foo.geom,p.geom);
``````