{"id":6196,"date":"2018-08-31T07:33:02","date_gmt":"2018-08-31T05:33:02","guid":{"rendered":"http:\/\/www.sigterritoires.fr\/?p=6196"},"modified":"2018-10-17T12:05:09","modified_gmt":"2018-10-17T10:05:09","slug":"spatial-analysis-with-sql-5-aggregation-function-arcgis-union","status":"publish","type":"post","link":"https:\/\/www.sigterritoires.fr\/index.php\/en\/spatial-analysis-with-sql-5-aggregation-function-arcgis-union\/","title":{"rendered":"Spatial analysis with SQL: 5- aggregation function (ArcGis Union)"},"content":{"rendered":"\r\n

In Article previous we have discussed some SQL functions for spatial processing<\/a> . In this article we will see a function a little more complicated to put at work: the aggregation function for two layers of polygons. This operation is the one called Union<\/em><\/strong> in ArcGis. The purpose of the operation is to obtain a layer with all the intersections of the entities of the two original layers, and, also, all non-intersecting entities.<\/p>\r\n\r\n\r\n\r\n

Let\u2019s use the following example. We have a first layer \u201crectangles\u201d<\/p>\r\n\r\n\r\n\r\n

 <\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

A second layer \u201ccircles\u201d<\/p>\r\n\r\n\r\n\r\n

 <\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

That overlaps spatially:<\/p>\r\n\r\n\r\n\r\n

 <\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

What we want to obtain is a layer containing all the polygons of both layers but creating polygons to overlay areas:<\/p>\r\n\r\n\r\n\r\n

 <\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

We will proceed by following the same method used in the previous article, i.e. by developing an SQL query including several sub queries.<\/p>\r\n\r\n\r\n\r\n

In the first place, we will develop a query that will build the polygons of the overlapping areas (the intersections) of both layers. These polygons appear in yellow in the next picture.<\/p>\r\n\r\n\r\n\r\n

 <\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

We will resume the request used in the previous article:<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

select<\/em><\/strong> circle.id as id1, rectangles.id as<\/em><\/strong> id2<\/em><\/strong>, st_intersection<\/em><\/strong> (<\/em><\/strong>circle.geom, rectangles.geom<\/em><\/strong> ) as<\/em><\/strong> the_geom<\/em><\/strong>
from<\/em><\/strong> circle, rectangles<\/em><\/strong>
where<\/em><\/strong> st_intersects<\/em><\/strong> (<\/em><\/strong> circle.geom, rectangles.geom<\/em><\/strong> )<\/em><\/strong>
group by<\/em><\/strong> circle.id, rectangles.id<\/em><\/strong><\/p>\r\n\r\n\r\n\r\n

You will notice that the select is built not just to retrieve intersected geometries, but also the identifiers of the original entities (circle.id<\/em><\/strong> and<\/strong> rectangles.id)<\/em><\/strong>. Of course, you can recover other attributes, or all by changing this part of the request.<\/p>\r\n\r\n\r\n\r\n

To these polygons, we will add the rest of the areas occupied by the rectangle layer (in yellow in the following picture)<\/p>\r\n\r\n\r\n\r\n

 <\/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

\"\"<\/figure>\r\n\r\n\r\n\r\n

select<\/strong> 0 as id1, rectangles.id as<\/em><\/strong> id2<\/strong>, st_difference<\/strong> (<\/strong> rectangles.geom<\/em><\/strong>,<\/p>\r\n\r\n\r\n\r\n

(select<\/strong> multi<\/strong> ST_<\/strong> (ST_Union<\/strong> (the_geom)) as<\/strong> the_geom<\/strong> from (<\/strong>
select<\/strong> st_intersection<\/strong> (<\/strong>circle.geom, rectangles.geom<\/em><\/strong> ) as<\/strong> the_geom<\/strong>
from<\/strong> circle, rectangles<\/em><\/strong>
where<\/strong> st_intersects<\/strong> (<\/strong> circle.geom, rectangles.geom<\/em><\/strong> ))<\/strong>
as<\/strong> the_geom<\/strong>))<\/strong> as<\/strong> the_geom<\/strong>
from rectangles<\/strong><\/p>\r\n\r\n\r\n\r\n

What we get to do with this request is to extract all the intersection areas (as in the previous query) and convert them in a single multi-polygon. The request removes the difference of this multi- polygon with the layer of rectangles.<\/p>\r\n\r\n\r\n\r\n

Finally, these polygons, we are going to add the rest of the areas occupied by the layer circles to these polygons (yellow in the following image)<\/p>\r\n\r\n\r\n\r\n

 <\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

We will proceed likewise the rectangles, by using a multi- polygon of intersection areas and calculating the difference with the layer circles:<\/p>\r\n\r\n\r\n\r\n

 <\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

select<\/strong> circle.id as id1, 0 as<\/em><\/strong> id2<\/strong> ,<\/em><\/strong> st_difference (<\/strong> circle.geom<\/em><\/strong> , (<\/strong>
select<\/strong> st_multi<\/strong> (<\/strong> st_union<\/strong> (<\/strong> the_geom<\/strong> )) as<\/strong> the_geom<\/strong> from (<\/strong>
select<\/strong> st_intersection<\/strong> (<\/strong> circle.geom, rectangles.geom<\/em><\/strong> ) as<\/strong> the_geom<\/strong>
from<\/strong> circle, rectangles<\/em><\/strong>
where<\/strong> st_intersects<\/strong> (<\/strong> circle.geom, rectangles.geom<\/em><\/strong> ))<\/strong>
as<\/strong> the_geom<\/strong> )) as<\/strong> the_geom<\/strong>
from<\/strong> circle<\/strong><\/p>\r\n\r\n\r\n\r\n

The union of these three requests produces the outcome layer we expected :<\/p>\r\n\r\n\r\n\r\n

 <\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

select *<\/strong>
from<\/strong>
(select circle.id as id1, rectangles.id<\/strong> as<\/strong> id2, st_intersection (<\/strong> circle.geom, rectangles.geom<\/strong> ) as<\/strong> the_geom<\/strong>
from<\/strong> circle, rectangles<\/strong>
where<\/strong> st_intersects<\/strong> (<\/strong> circle.geom, rectangles.geom<\/strong> )<\/strong>
group by<\/strong> circle.id, rectangles.id<\/strong>
union all<\/strong>
select 0 as id1, rectangles.id as id2, st_difference (<\/strong> rectangles.geom<\/strong> , (<\/strong>
select<\/strong> st_multi<\/strong> (<\/strong> st_union<\/strong> (<\/strong> the_geom<\/strong> )) as<\/strong> the_geom<\/strong> from (<\/strong>
select<\/strong> st_intersection<\/strong> (<\/strong> circle.geom, rectangles.geom<\/strong> ) as<\/strong> the_geom<\/strong>
from<\/strong> circle, rectangles<\/strong>
where<\/strong> st_intersects<\/strong> (<\/strong> circle.geom, rectangles.geom<\/strong> ))<\/strong>
as<\/strong> the_geom<\/strong> )) as<\/strong> the_geom<\/strong>
from rectangles<\/strong>
union all<\/strong>
select circle.id as id1, 0 as id2, st_difference (<\/strong> circle.geom<\/strong> , (<\/strong>
select<\/strong> st_multi<\/strong> (<\/strong> st_union<\/strong> (<\/strong> the_geom<\/strong> )) as<\/strong> the_geom<\/strong> from (<\/strong>
select<\/strong> st_intersection<\/strong> (<\/strong> circle.geom, rectangles.geom<\/strong> ) as<\/strong> the_geom<\/strong>
from<\/strong> circle, rectangles<\/strong>
where<\/strong> st_intersects<\/strong> (<\/strong> circle.geom, rectangles.geom<\/strong> ))<\/strong>
as<\/strong> the_geom<\/strong> )) as<\/strong> the_geom<\/strong>
from<\/strong> circle<\/strong>
) as T1<\/strong> \u00a0<\/p>\r\n\r\n\r\n\r\n

 <\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n","protected":false},"excerpt":{"rendered":"

In Article previous we have discussed some SQL functions for spatial processing . In this article we will see a function a little more complicated to put at work: the aggregation function for two layers of…<\/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_post_was_ever_published":false,"_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":""},"categories":[1296,1338],"tags":[1443,1351,1386,1404,1353,1445],"class_list":["post-6196","post","type-post","status-publish","format-standard","hentry","category-posts-en","category-postgresql-en","tag-aggregation","tag-postgresql-en","tag-qgis-en","tag-spatial-analyzis","tag-sql-en","tag-union-en"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p6XU0A-1BW","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/6196","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=6196"}],"version-history":[{"count":0,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/6196\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/media?parent=6196"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/categories?post=6196"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/tags?post=6196"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}