3

polygons

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
1

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.

0

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.