{"id":5898,"date":"2018-08-07T00:19:25","date_gmt":"2018-08-06T22:19:25","guid":{"rendered":"http:\/\/www.sigterritoires.fr\/?p=5898"},"modified":"2018-10-17T12:07:41","modified_gmt":"2018-10-17T10:07:41","slug":"spatial-analysis-with-sql-4-spatial-processing-functions","status":"publish","type":"post","link":"https:\/\/www.sigterritoires.fr\/index.php\/en\/spatial-analysis-with-sql-4-spatial-processing-functions\/","title":{"rendered":"Spatial Analysis with SQL: 4-Spatial Processing Functions"},"content":{"rendered":"

In the previous article we discussed the SQL functions of spatial relationship<\/a> . Let’s now discuss the SQL spatial processing functions. Let\u2019s start with the difference between the notion of spatial relation and spatial treatment.<\/p>\n

The simplest way is to use an example. We have geometries that share the occupation of space<\/p>\n

A spatial relationship function, such as st_intersects<\/a><\/em><\/strong> , will answer the question \u201cdo these geometries intersect ?\u201d\u00a0 TRUE or FALSE?.<\/p>\n

A spatial processing function, such as ST_Intersection,<\/em><\/strong> will extract the common part of the geometries returning as result a geometry :<\/p>\n

$\"\"$ <\/p>\n

Let\u2019s suppose that we have two tables: rectangles and circles, and that we want to retrieve the result from this image. How does one design the adequate SQL query ?<\/p>\n

By adopting the same approach as described in the second article of this<\/a> series<\/a> :<\/p>\n

\n
which is the data I want to obtain ?: I want the resulting geometries originating in the intersection of circles and rectangles-> SELECT clause:<\/li>\n<\/ul>\n
SELECT<\/em><\/strong> st_<\/em><\/strong> intersection<\/em><\/strong> (<\/em><\/strong> circles.geometry, rectangles.geometry<\/em><\/strong> ) as geometry<\/em><\/strong><\/p>\n
\n
where are they stored ? : in the circles and rectangles tables-> FROM clause:<\/li>\n<\/ul>\n
FROM<\/em><\/strong> circles<\/em><\/strong>, rectangles<\/em><\/strong><\/p>\n
\n
which are the conditions I want to apply , spatial or not? \u00a0select only entities that occupy common space -> WHERE clause:<\/li>\n<\/ul>\n
\u00a0<\/em><\/strong> WHERE<\/em><\/strong> st_<\/em><\/strong> intersects<\/em><\/strong> (<\/em><\/strong> circles.geometry, rectangles.geometry<\/em><\/strong> )<\/em><\/strong><\/p>\n
What makes the request :<\/p>\n
SELECT<\/em><\/strong> st_<\/em><\/strong> intersection<\/em><\/strong> (<\/em><\/strong> circles.geometry, rectangles.geometry<\/em><\/strong> ) as geometry<\/em><\/strong>
\nFROM<\/em><\/strong> circles, rectangles<\/em><\/strong>
\nWHERE<\/em><\/strong> st_intersects<\/em><\/strong> (<\/em><\/strong> circles.geometry, rectangles.geometry<\/em><\/strong> )<\/em><\/strong><\/p>\n
You can omit the WHERE clause and get the same result, except that it will take a lot more time. It is logical to limit the operation intersection of st_intersection to places where the geometries intersect.<\/p>\n
The<\/strong> batch<\/strong> and interactive<\/strong> tools<\/strong><\/h2>\n
Before discussing the spatial processing functions in detail, let\u2019s do a little parenthesis. In office GIS, such as ArcGis, you, usually, work with two groups of different tools according to what you are going to process (a layer as a whole or a specific entity).<\/p>\n
When processing the layer as a whole, you use the tools in the Toolbox. When processing the specific entities you use the ArcMap interactive tools.<\/p>\n
An advantage of SQL is that you work with only one tool. Let’s return to the previous query to apply it at the intersections between a layer map_ risks ( in beige) \u00a0 and flooding areas ( in blue)<\/p>\n
$\"\"$ <\/p>\n
We can use the query :<\/p>\n
SELECT<\/em><\/strong> st_<\/em><\/strong> intersection<\/em><\/strong> (<\/em><\/strong> map_risk.geometry, flooding_zones.geometry<\/em><\/strong> ) as geometry<\/em><\/strong>
\nFROM<\/em><\/strong> map_risk, flooding_zones<\/em><\/strong>
\nWHERE<\/em><\/strong> st_intersects<\/em><\/strong> (<\/em><\/strong> map_risk.geometry, flooding_zones.geometry<\/em><\/strong> )<\/em><\/strong><\/p>\n
To intersect all the present entities in the risk map with flooding areas:<\/p>\n
$\"\"$ <\/p>\n
Where only the area with a Maximum risk for flood areas<\/p>\n
SELECT<\/em><\/strong> st_<\/em><\/strong> intersection<\/em><\/strong> (<\/em><\/strong> map_risk.geometry, flooding_zones.geometry<\/em><\/strong> ) as geometry<\/em><\/strong>
\nFROM<\/em><\/strong> map_risk, flooding_zones<\/em><\/strong>
\nWHERE<\/em><\/strong> st_intersects<\/em><\/strong> (<\/em><\/strong> map_risk.geometry, flooding_zones.geometry<\/em><\/strong> )<\/em><\/strong> AND
\nmap_risk.zone = ‘max’<\/p>\n
$\"\"$ <\/p>\n
The only difference is in the WHERE<\/strong> clause where we add \u00a0\u00bb \u00a0 AND<\/em><\/strong>
\nmap_risk.zone<\/em><\/strong> = ‘max’<\/em><\/strong> \u00ab<\/p>\n
Spatial<\/strong> processing<\/strong> functions<\/strong><\/h2>\nIf you take a look to the help page of ArcGis Overlay tools ( https:\/\/pro.arcgis.com\/fr\/pro-app\/tool-reference\/analysis\/an-overview-of-the-overlay-toolset.htm<\/a> ) you will find a description of a series of spatial processing functions:<\/p>\n
$\"\"$ $\"\"$ <\/p>\n
<\/p>\n
They are seven: Erase, Identity, Intersection, Spatial joint, Symmetric difference, Aggregation and Update.<\/p>\n
In SQL we will use only three functions: st_difference , st_intersection and ST_Union to build the equivalent queries.<\/p>\n
Firstly, let\u2019s discuss these three functions and their equivalent in the ArcGis Toolset , then we will discuss how to get the equivalence with others tools from the Toolset combining the three basic functions .<\/p>\n
st_Difference<\/strong> = Erase<\/strong><\/h3>\n
It bears the form st_ difference (geometry A, geometry B). This function returns the A geometry part that does not intersect the B geometry.<\/p>\n
$\"\"$ <\/p>\n
In the risk mapping and flood zones example we can wish, for example, to see the part of the classified areas as maximum risk that are not floodable:<\/p>\n
SELECT ST_ Difference (map_risk.geometry, flooding_zones.geometry) as geometry
\nFROM flooding_zones, map_risk
\nWHERE map_risk.zone = ‘max’<\/p>\n
The result of the query appears in yellow.<\/p>\n
$\"\"$ <\/p>\n
st_intersection<\/strong> = Intersection<\/strong><\/h3>\n
It bears the form ST_ intersection geometry, B geometry). This function returns the part of the A geometry that intersects \u00a0 the B geometry.<\/p>\n
$\"\"$ <\/p>\n
We have already detailed the query<\/p>\n
SELECT<\/em><\/strong> st_<\/em><\/strong> intersection<\/em><\/strong> (<\/em><\/strong> map_risk. geometry, flooding_zones.geometry<\/em><\/strong> ) as geometry<\/em><\/strong>
\nFROM<\/em><\/strong> map_risk, flooding_zones<\/em><\/strong>
\nWHERE<\/em><\/strong> st_intersects<\/em><\/strong> (<\/em><\/strong> map_risk.geometry, flooding_zones.geometry<\/em><\/strong> )<\/em><\/strong><\/p>\n
in the previous paragraph dealing with batch and interactive functions. But we will complete the use of functions with another element. In the examples used so far we get as result of the query only geometries. But most of the time we, also, need to recover the attributes of the concerned entities.<\/p>\n
For example, in the resulting entities of the query we want to know the version of \u00a0 \u201cflooding zones\u201d as well as the risk level. We add these fields in the SELECT part of the query<\/p>\n
SELECT<\/strong> flooding_zones.version , map_risk.zone , st_ intersection ( map_risk.geometry , flooding_zones.geometry ) as geometry
\nFROM<\/strong> map_risk, flooding_zones
\nWHERE<\/strong> st_intersects (map_risk.geometry , flooding_zones.geometry )<\/p>\n
and the result will be:<\/p>\n
$\"\"$ <\/p>\n
Attributes fields are added in the resulting table. Each geometry will have the value of the attribute of the geometries that intersect at that place.<\/p>\n
ST_Union<\/strong><\/h3>\n
It bears the form st_ union ( A geometry, B geometry). This function take each A geometry and merging with the B geometry\/es by merging the points that compose them.<\/p>\n
$\"\"$ <\/p>\n
If you have x geometries in A, you will have the same number of geometries in the result. It\u2019s not the number that changes but the geometry of each entity of A that associates with the space occupied with the B geometry (ies).<\/p>\n
This SQL function is confusing, especially in English, with the operation Union (in French Aggregation) of the ArcGis Toolset. Let’s see immediately this one to avoid any misunderstanding.<\/p>\n
Union<\/strong> (Aggregation in French<\/strong>)<\/h3>\n
This function allows recovering all the space that is occupied by the A and B geometries:<\/p>\n
\n
areas in which the geometries intersect<\/li>\n
areas where only A geometries are present<\/li>\n
areas where only B geometries are present<\/li>\n<\/ul>\n
The following scheme shows, by imputing a layer A with two rectangles, and a layer B with a circle .<\/p>\n
$\"\"$ <\/p>\n
The result of the union of these two layers includes 5 geometries:<\/p>\n
\n
the two parts of the rectangles that do not intersect with B<\/li>\n
the two parts of the rectangles that intersect with the circle<\/li>\n
the part of the circle that does not intersect with the rectangles<\/li>\n<\/ul>\n
We have seen that the function ST_Union keeps the same number of A geometries A in the result. With Union Toolset, there is always more geometries in the result than in the layer A, the number depending on the intersection areas of the two layers.<\/p>\n
The sequel\u2026<\/strong><\/h3>\n
We have discussed \u00a0 the three \u00a0SQL basic functions for spatial processing. In the next article we will see how to combine these basic functions to get functions such as Identity , Aggregation and Update .<\/p>\n","protected":false},"excerpt":{"rendered":"
In the previous article we discussed the SQL functions of spatial relationship . Let’s now discuss the SQL spatial processing functions. Let\u2019s start with the difference between the notion of spatial relation and spatial treatment. The…<\/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":[1260],"tags":[],"class_list":["post-5898","post","type-post","status-publish","format-standard","hentry","category-non-classe-en"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p6XU0A-1x8","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/5898","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=5898"}],"version-history":[{"count":0,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/5898\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/media?parent=5898"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/categories?post=5898"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/tags?post=5898"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}