<\/div>
<\/div>
<\/div><\/div>
select cercle.id<\/em> as id1,rectangles.id<\/em> as id2, st_intersection(cercle.geom<\/em>,rectangles.geom<\/em>) as the_geom<\/strong>
from cercle,rectangles<\/em><\/strong>
where st_intersects(cercle.geom,rectangles.geom<\/em>)<\/strong>
group by cercle.id,rectangles.id<\/em><\/strong><\/div><\/div><\/em><\/strong><\/p>\r\n\r\n\r\n\r\n
Vous remarquerez que le select est construit pour non seulement r\u00e9cup\u00e9rer les g\u00e9om\u00e9tries intersect\u00e9es, mais aussi les identifiants des entit\u00e9s d’origine (cercle.id<\/em>\u00a0 et rectangles.id)<\/em><\/strong> . <\/em>Bien s\u00fbr, vous pourrez r\u00e9cup\u00e9rer d’autres attributs, ou tous, en modifiant cette partie de la requ\u00eate.<\/p>\r\n\r\n\r\n\r\n
A ces polygones, nous allons ajouter le reste des surfaces occup\u00e9es par la couche rectangles (en jaune sur l’image suivante)<\/p>\r\n\r\n\r\n\r\n
$\"\"$
<\/div>
<\/div>
<\/div><\/div>
\r\n
select 0 as id1, rectangles.id as id2<\/em>, st_difference(rectangles.geom,<\/em><\/strong><\/p>\r\n\r\n\r\n\r\n
(select st_multi(st_union(the_geom) ) as the_geom from (<\/strong>
select st_intersection(cercle.geom,rectangles.geom<\/em>) as the_geom<\/strong>
from cercle,rectangles<\/em><\/strong>
where st_intersects(cercle.geom,rectangles.geom<\/em>) ) <\/strong>
as the_geom)) as the_geom<\/strong>
from rectangles<\/strong><\/p>\r\n<\/div><\/div><\/figure>\r\n\r\n\r\n\r\n\r\n\r\n
Ce que nous faisons avec cette requ\u00eate est d’extraire toutes les zones d’intersection (comme dans la requ\u00eate pr\u00e9c\u00e9dente) et les convertir en un seul et unique multi-polygone. La requ\u00eate extrait apr\u00e8s la diff\u00e9rence de ce multi-polygone avec la couche de rectangles.<\/p>\r\n\r\n\r\n\r\n
Pour finir, \u00e0 ces polygones, nous allons ajouter le reste des surfaces occup\u00e9es par la couche cercles (en jaune sur l’image suivante)<\/p>\r\n\r\n\r\n\r\n
$\"\"$ <\/figure>\r\n\r\n\r\n\r\n
<\/p>\r\n\r\n\r\n\r\n
Nous proc\u00e9dons de la m\u00eame mani\u00e8re que pour les rectangles, en utilisant un multi-polygone des zones d’intersection et en calculant la diff\u00e9rence avec la couche cercles:<\/p>\r\n\r\n\r\n\r\n
<\/div>
<\/div>
<\/div><\/div>
select cercle.id as id1, 0 as id2,<\/em>st_difference(cercle.geom<\/em>,(<\/strong>
select st_multi(st_union(the_geom) ) as the_geom from (<\/strong>
select st_intersection(cercle.geom,rectangles.geom<\/em>) as the_geom<\/strong>
from cercle,rectangles<\/em><\/strong>
where st_intersects(cercle.geom,rectangles.geom<\/em>) ) <\/strong>
as the_geom)) as the_geom<\/strong>
from cercle<\/strong><\/div><\/div><\/strong><\/p>\r\n\r\n\r\n\r\n
L’union de ces trois requ\u00eates produit la couche r\u00e9sultat que nous attendions:<\/p>\r\n\r\n\r\n\r\n
<\/div>
<\/div>
<\/div><\/div>
select * <\/strong>
from<\/strong>
(select cercle.id as id1,rectangles.id as\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 id2,st_intersection(cercle.geom,rectangles.geom) as the_geom<\/strong>
from cercle,rectangles<\/strong>
where st_intersects(cercle.geom,rectangles.geom)<\/strong>
group by cercle.id,rectangles.id<\/strong>
union all<\/strong>
select 0 as id1, rectangles.id as id2,st_difference(rectangles.geom,(<\/strong>
select st_multi(st_union(the_geom) ) as the_geom from (<\/strong>
select st_intersection(cercle.geom,rectangles.geom) as the_geom<\/strong>
from cercle,rectangles<\/strong>
where st_intersects(cercle.geom,rectangles.geom) ) <\/strong>
as the_geom)) as the_geom<\/strong>
from rectangles<\/strong>
union all<\/strong>
select cercle.id as id1, 0 as id2,st_difference(cercle.geom,(<\/strong>
select st_multi(st_union(the_geom) ) as the_geom from (<\/strong>
select st_intersection(cercle.geom,rectangles.geom) as the_geom<\/strong>
from cercle,rectangles<\/strong>
where st_intersects(cercle.geom,rectangles.geom) ) <\/strong>
as the_geom)) as the_geom<\/strong>
from cercle<\/strong>
)as T1<\/strong><\/div><\/div><\/strong><\/p>\r\n\r\n\r\n\r\n
$\"\"$ <\/figure>\r\n\r\n\r\n\r\n
<\/p>\r\n","protected":false},"excerpt":{"rendered":"
Dans l’article pr\u00e9c\u00e9dent nous avons certaines les fonctions SQL de traitement spatial. Dans cet article nous verrons une fonction un peu plus compliqu\u00e9e \u00e0 mettre en \u0153uvre: la fonction d’agr\u00e9gation de deux couches de polygones. Cette…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"give_campaign_id":0,"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"sfsi_plus_gutenberg_text_before_share":"","sfsi_plus_gutenberg_show_text_before_share":"","sfsi_plus_gutenberg_icon_type":"","sfsi_plus_gutenberg_icon_alignemt":"","sfsi_plus_gutenburg_max_per_row":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[311],"tags":[242,377,227,312,58,1343,1441],"class_list":["post-6180","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-agregation","tag-analyse-spatiale","tag-postgis","tag-postgresql","tag-qgis","tag-sql","tag-union"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p6XU0A-1BG","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/6180","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/comments?post=6180"}],"version-history":[{"count":0,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/6180\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/media?parent=6180"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/categories?post=6180"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/tags?post=6180"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}