Наборы примеров SQL-запросов

В данном разделе приведены примеры SQL-запросов для создания виртуальных слоёв с определённой выборкой данных.

Взаимодействие между слоями

В результате выполнения запросов создаётся новый слой с результатми взаимодействия.

По наличию пересечений геометрии объектов в указанных слоях:

SELECT 
     l1.geom
FROM layer1 l1 INNER JOIN layer2 l2
ON ST_Intersects(l1.geom, l2.geom);


По отсутствию пересечений геометрии объектов в указанных слоях:

SELECT 
 l1.geom
FROM layer1 l1 INNER JOIN layer2 l2
ON not ST_Intersects(l1.geom, l2.geom);


Выборка объектов в определённых пределах (радиусе)

SELECT row_number() OVER () AS orbis_id, 
        l1.geom
 FROM layer1 l1 INNER JOIN layer3 l3 
   ON ST_DWithin(l1.geom, l3.geom, 100000);


Выборка объектов вне определённых пределов

    SELECT row_number() OVER () AS orbis_id, 
            l1.geom
     FROM layer1 l1 INNER JOIN layer3 l3 
       ON not ST_DWithin(l1.geom, l3.geom, 100000);


Выборка объектов по совпадению (включению) каких-либо значений

    SELECT 
     l1.geom
FROM layer1 l1 INNER JOIN layer2 l2
ON ST_Contains(l1.geom, l2.geom);


Выборка объектов по отсутствию совпадения (включения) каких-либо значений

    SELECT 
     l1.geom
FROM layer1 l1 INNER JOIN layer2 l2
ON not ST_Contains(l1.geom, l2.geom);


Выборка, обратная выборке совпадения

    SELECT 
     l1.geom
FROM layer1 l1 INNER JOIN layer2 l2
ON ST_Within(l1.geom, l2.geom);


Выборка, обратная выборке отсутствия совпадения

    SELECT 
     l1.geom
FROM layer1 l1 INNER JOIN layer2 l2
ON not ST_Within(l1.geom, l2.geom);

Объединение объектов в коллекцию (слияние) по какому-либо параметру:

    SELECT 
        ST_Union(geom) geom     
FROM layer1
WHERE 
“type” = ‘subject’

Включение одного слоя в другой с объединением значений колонок.

Позволяет объединять (включать) слои, рассчитывать и записывать значения по выбранным колонкам по различным параметрам. Значения объединяемых колонок должны быть числовыми. Текстовые поля и поля других типов не поддерживаются.

Суммирование значений среди всех объектов указанной колонки в выбранных слоях:

    SELECT 
     l2.geom,
     SUM(l1.price) price
FROM layer1 l1 INNER JOIN layer2 l2
ON ST_Within(l1.geom, l2.geom)
group by l2.geom;


Среднее значение среди всех объектов указанной колонки в выбранных слоях:

    SELECT 
     l2.geom,
     avg(l1.price) price
FROM layer1 l1 INNER JOIN layer2 l2
ON ST_Within(l1.geom, l2.geom)
group by l2.geom;


Минимальное значение среди всех объектов указанной колонки в выбранных слоях:

    SELECT 
     l2.geom,
     min(l1.price) price
FROM layer1 l1 INNER JOIN layer2 l2
ON ST_Within(l1.geom, l2.geom)
group by l2.geom;


Максимальное значение среди всех объектов указанной колонки в выбранных слоях:

    SELECT 
     l2.geom,
     max(l1.price) price
FROM layer1 l1 INNER JOIN layer2 l2
ON ST_Within(l1.geom, l2.geom)
group by l2.geom;


Стандартное отклонение

    SELECT 
     l2.geom,
     stddev(l1.price) price
FROM layer1 l1 INNER JOIN layer2 l2
ON ST_Within(l1.geom, l2.geom)
group by l2.geom;

Включение одного слоя в другой в заданном пределе с объединением значений колонок.

Позволяет группировать объекты в заданном радиусе. В остальном, полностью повторяет функциональность предыдущего пункта.

Стандартное отклонение

    SELECT row_number() OVER () AS orbis_id, 
     l2.geom,
     stddev(l3.price) price
FROM layer3 l3 INNER JOIN layer2 l2
ON ST_Within(ST_Buffer(l3.geom, 60000), l2.geom)
group by l2.geom;