Analyzing the intersection of objects from different layers (SQL)
ZuluGIS SQL queries allow you not only to determine whether objects intersect in space but also
to calculate the degree of intersection, for example, which part of an object (its area or length)
overlaps with another. The structures of such queries use the Intersection operator.
For example, let us create 2 layers; they are saved in the ZuluGIS map archive (an example for download is available below, map archives are available in ZuluGIS 2021); we will use them to examine how the Intersection works. The main (Green) layer has 3 green squares with an area of 900 sq.m.; the second (Yellow) layer contains 4 yellow polygons with an area of 200 sq.m.
Example 1. Determine the area of the yellow figures located in the green squares.
The query will contain the GROUP BY clause and the Intersection operator.
SELECT
g.[name],
sum(g.Geometry.Intersection(y.Geometry).area())
FROM [Green] as g, [Yellow]AS y
group by g.[name]
Example 2. Write the area of the yellow figures located in the corresponding green squares into the database of the main layer.
To make a nested area table, the query will also contain the GROUP BY clause, the Intersection operator, and the INNER JOIN clause to match the results of the subquery with the objects of the main layer.
update g1 SET g1.[Yellow area]= t2.y_sum
from [Green] as g1
INNER JOIN
select g2.sys,
sum(g2.Geometry.Intersection(y2.Geometry).area()) as y_sum
FROM [Green] as g2, [Yellow]AS y2
group by g2.sys) as t2
on g1.sys = t2.sys
