Использование SQL запросов в ГИС программах. На примере MapInfo и QGIS.

Геоинформационные системы (ГИС) — это программные инструменты для сбора, хранения, анализа, управления и визуализации пространственных данных. Существует множество различных ГИС-программ, которые могут различаться по функционалу, стоимости и целевой аудитории. Вот некоторые из наиболее известных ГИС-программ:

1. Коммерческие ГИС-программы:

• ArcGIS (Esri): Одно из самых популярных и мощных коммерческих решений для анализа и визуализации геоданных. ArcGIS включает в себя множество модулей и инструментов, которые позволяют работать с различными типами данных, от 2D до 3D-карт и даже 4D (с учетом времени). ArcGIS Online предоставляет облачные возможности.
• MapInfo (Pitney Bowes): Долгое время был основным конкурентом ArcGIS. Поддерживает широкий спектр инструментов для анализа пространственных данных и картографирования.
• Bentley Map (Bentley Systems): Предназначен для проектирования инфраструктуры, интеграции инженерных данных и ГИС. Часто используется в строительстве и транспортной инфраструктуре.
• AutoCAD Map 3D (Autodesk): Инструмент для работы с пространственными данными, интегрированный в AutoCAD. Используется для проектирования, анализа и визуализации данных в инженерных и строительных проектах.
• GeoMedia (Hexagon Geospatial): Платформа для управления, анализа и визуализации геопространственных данных. Поддерживает интеграцию с различными источниками данных и имеет мощные аналитические возможности.

2. Открытые и бесплатные ГИС-программы:

• QGIS (Quantum GIS): Одна из самых популярных и мощных бесплатных ГИС-программ с открытым исходным кодом. Поддерживает широкий спектр форматов данных и имеет большое количество плагинов для расширения функциональности. QGIS активно развивается и используется как в академических, так и в профессиональных кругах.
• GRASS GIS: Одна из старейших ГИС-программ с открытым исходным кодом. Обладает мощными инструментами для анализа пространственных данных и моделирования. Часто используется в научных исследованиях.
• gvSIG: Бесплатная ГИС-система с открытым исходным кодом, разработанная в Испании. Поддерживает работу с векторными и растровыми данными, а также имеет широкий набор инструментов для анализа.
• SAGA GIS: Открытая ГИС с большим набором инструментов для анализа геоданных. Особенно популярна среди геологов и геоморфологов благодаря мощным инструментам для анализа рельефа.
• OpenJUMP: Еще одна бесплатная ГИС с открытым исходным кодом, которая поддерживает стандартные ГИС-функции и предоставляет инструменты для работы с векторными и растровыми данными.
• uDig: Открытая и бесплатная ГИС, построенная на Java и Eclipse. uDig поддерживает работу с различными источниками данных, включая WMS, WFS и Shapefile.

3. Веб-ГИС и облачные решения:

• ArcGIS Online: Облачная версия ArcGIS, которая предоставляет инструменты для создания карт и анализа данных через веб-интерфейс. Поддерживает работу с большими объемами данных и совместное использование карт.
• Google Earth Engine: Платформа для анализа спутниковых данных в облаке. Используется для работы с большими массивами данных, в том числе для экологического мониторинга и анализа изменений земной поверхности.
• Carto (ранее CartoDB): Облачная платформа для анализа геоданных и визуализации. Поддерживает создание интерактивных карт и работу с большими данными.
• Mapbox: Облачная платформа для создания пользовательских карт и визуализаций. Часто используется для разработки веб-приложений и мобильных приложений с интеграцией карт.

4. Другие специализированные ГИС:

• ERDAS IMAGINE (Hexagon Geospatial): Специализированное программное обеспечение для обработки и анализа спутниковых изображений и аэрофотосъемки.
• IDRISI (Clark Labs): ГИС-программа для анализа спутниковых данных и моделирования земных процессов. Часто используется в экологии и географии.
• TerrSet: Пакет для геопространственного анализа и моделирования, особенно популярный для экологического моделирования и анализа изменений землепользования.
• Orfeo ToolBox: Свободное программное обеспечение для обработки спутниковых изображений, разработанное CNES (Французское космическое агентство). Поддерживает широкий спектр алгоритмов для анализа изображений.

5. Мобильные ГИС:

• Collector for ArcGIS: Мобильное приложение от Esri для сбора полевых данных и их интеграции в ArcGIS.
• Locus Map: Мобильное приложение для Android, поддерживающее работу с оффлайн-картами и GPS-данными. Часто используется для навигации и полевых работ.
• OsmAnd: Открытое мобильное приложение для работы с картами OpenStreetMap, поддерживающее оффлайн-режим и GPS-навигацию.
• Field Maps (Esri): Мобильное приложение для работы с полевыми данными. Позволяет собирать информацию в реальном времени и синхронизировать с облачными системами.

Как использовать SQL в MapInfo?

SQL (Structured Query Language) в MapInfo, как и в других ГИС-программах, используется для запросов к данным, которые хранятся в табличной форме (атрибутивные данные объектов на карте). В MapInfo есть собственная реализация SQL-запросов, называемая MapBasic SQL, которая позволяет выполнять выборки, фильтрацию, обновление, объединение данных и другие операции для работы с пространственными данными.

Вот основные шаги по использованию SQL в MapInfo:

1. Открытие окна SQL-запросов

Чтобы начать работу с SQL-запросами в MapInfo, выполните следующие шаги:

• Перейдите в меню Query.
• Выберите SQL Select....

Откроется диалоговое окно для работы с SQL-запросами.

2. Основные компоненты SQL-запроса в MapInfo

В диалоговом окне SQL Select... вы можете задать следующие параметры:

• Select Columns (Выбор столбцов):
  Здесь вы можете выбрать, какие столбцы из таблицы (атрибутивные данные) вы хотите получить в результате запроса. Например: Name, Population, Area.
• From Tables (Из таблиц):
  Укажите, из какой таблицы вы хотите выбрать данные. Например: Countries или Cities.
• Where Condition (Условие):
  Здесь вы можете задать условия для фильтрации данных. Например, если вы хотите выбрать города с населением больше 1 миллиона, запрос будет выглядеть так: Population > 1000000.
• Group By (Группировка):
  Используется для группировки записей по определённым полям. Например, можно сгруппировать данные по странам, чтобы получить суммарное население для каждой страны.
• Order By (Сортировка):
  Сортировка результатов по возрастанию или убыванию значений определённого столбца. Например, можно отсортировать города по численности населения: ORDER BY Population DESC.
• Into Table (В таблицу):
  Определите, куда сохранить результаты запроса. Можно либо создать новую таблицу, либо просто отобразить результат на экране в виде временной таблицы. Например: NewCities.

3. Примеры SQL-запросов в MapInfo

Пример 1: Выборка всех городов с населением больше 1 миллиона

• Select Columns: CityName, Population
• From Tables: Cities
• Where Condition: Population > 1000000
• Into Table: CitiesOverMillion

Пример 2: Сортировка стран по площади

• Select Columns: CountryName, Area
• From Tables: Countries
• Order By: Area DESC
• Into Table: SortedCountries

Пример 3: Подсчет общего населения по странам

• Select Columns: CountryName, SUM(Population) AS TotalPopulation
• From Tables: Cities
• Group By: CountryName
• Into Table: PopulationByCountry

4. Пространственные запросы (Spatial SQL)

MapInfo поддерживает пространственные запросы, что позволяет выполнять операции на основе пространственных отношений объектов. Вот несколько примеров пространственных SQL-запросов:

Пример 1: Найти все города внутри определённой области

• Select Columns: CityName, Population
• From Tables: Cities
• Where Condition: Within(Cities.Obj, Regions.Obj)
  (Этот запрос выберет все города, находящиеся внутри региона.)

Пример 2: Найти все пересекающиеся объекты

• Select Columns: Roads.RoadName, Rivers.RiverName
• From Tables: Roads, Rivers
• Where Condition: Overlaps(Roads.Obj, Rivers.Obj)
  (Запрос вернёт все дороги, которые пересекаются с реками.)

Пример 3: Найти ближайшие города к заданной точке

• Select Columns: CityName, Distance(Cities.Obj, Point(50, 50)) AS Dist
• From Tables: Cities
• Order By: Dist ASC
• Into Table: NearestCities

5. Создание сложных запросов

MapInfo позволяет комбинировать несколько условий, используя логические операторы, такие как AND, OR, и другие. Например:

Пример 1: Найти все города с населением больше 1 миллиона, расположенные в определённом регионе

• Select Columns: CityName, Population
• From Tables: Cities, Regions
• Where Condition: Population > 1000000 AND Within(Cities.Obj, Regions.Obj)Пример 2: Найти все дороги, которые пересекаются с реками и проходят через определённый регион
  
• Select Columns: Roads.RoadName
• From Tables: Roads, Rivers, Regions
• Where Condition: Overlaps(Roads.Obj, Rivers.Obj) AND Within(Roads.Obj, Regions.Obj)

6. Сохранение и использование результатов

Результаты SQL-запросов можно сохранить в виде новой таблицы или временной выборки. Вы можете использовать эти данные для дальнейшего анализа, создания карт или экспорта в другие форматы.

• Если вы хотите сохранить результаты как новую таблицу, в поле Into Table укажите имя новой таблицы. Например, если вы хотите сохранить результат в таблицу CitiesOverMillion, введите это имя.
• Если вы хотите просто отобразить результаты на экране, можно оставить поле Into Table пустым, и результат отобразится как временная выборка.

7. Работа с более сложными запросами через MapBasic

MapInfo также поддерживает язык MapBasic, который можно использовать для создания ещё более сложных SQL-запросов и автоматизации задач. Через MapBasic можно выполнять сложные пространственные операции, циклы и условия.

Пример MapBasic-запроса:

Select CityName, Population

From Cities

Where Population > 1000000

Into CitiesOverMillion

8. Экспорт результатов запросов

После выполнения SQL-запроса вы можете сохранить результаты в одном из поддерживаемых форматов (например, Shapefile, CSV, TAB и др.) для дальнейшего использования в других ГИС или аналитических программах:

• Перейдите в меню File → Save Copy As....
• Выберите формат и укажите путь для сохранения файла.

Если уже не терпится применить знания на практике, но покупать платную программу MapInfo не хочется?

Способы использования SQL в бесплатной ГИС программе QGIS:

• Использование SQL через инструмент "Фильтр" (Query Builder).
• SQL-запросы через DB Manager.
• Виртуальные слои (Virtual Layers).
• PostGis (через DB Manager)

Рассмотрим каждый из этих способов подробнее:

1. SQL в Query Builder (Построитель запросов)

Это самый простой способ выполнения SQL-запросов для фильтрации данных в слое.

Шаги:

1. Откройте QGIS и загрузите слой, с которым хотите работать (где взять слои для работы в QGIS читайте ниже (будет в этой же статье)).

2. В панели слоев щелкните правой кнопкой мыши на слое, к которому хотите применить SQL-запрос, и выберите Properties (Свойства).

3. Перейдите на вкладку Source (Источник), а затем нажмите кнопку Query Builder (Конструктор/Построитель запросов).

4. В открывшемся окне вы можете ввести SQL-запрос для фильтрации данных.

Пример 1: Выбор городов с населением больше 100 000

"population" > 100000

Примечание: Поля к которым можно сделать запрос указаны в окошке слева вверху. (На данной картинке видно что на том слое который я анализирую нет поля "population").

Пример 2: Выбор всех объектов, где тип города – "столица"

"type" = 'capital'

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

2. Использование SQL через DB Manager

DB Manager — это инструмент, встроенный в QGIS, который позволяет выполнять SQL-запросы к базам данных (например, к SQLite, PostGIS, SpatiaLite и другим).

Шаги:

1. В главном меню QGIS перейдите в Database → DB Manager (Управление базами данных).

2. В окне DB Manager выберите подключённую базу данных (например, SpatiaLite или PostGIS). Если базы данных нет, то загрузите или создайте новую базу данных.

3. Нажмите на вкладку SQL Window (Окно SQL) для выполнения SQL-запросов.

4. Введите SQL-запрос, который хотите выполнить, и нажмите Execute (Выполнить).

Пример 1: Выбор всех городов с населением больше 100 000 из таблицы cities

SELECT * FROM cities

WHERE population > 100000;

Пример 2: Подсчет числа городов по типу

SELECT type, COUNT(*)

FROM cities

GROUP BY type;

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

SELECT *

FROM cities

WHERE ST_Within(geometry, (SELECT geometry FROM regions WHERE name = 'RegionName'));

Этот запрос выберет все города, которые находятся внутри полигона с названием региона "RegionName".

3. Виртуальные слои (Virtual Layers)

Virtual Layers — это мощный инструмент в QGIS, который позволяет выполнять SQL-запросы не только к отдельным таблицам, но и к слоям данных, загруженным в проект QGIS. Это полезно, если вы хотите комбинировать данные из нескольких слоев или выполнять сложные пространственные запросы.

Шаги:

1. В главном меню QGIS перейдите в Layer → Add Layer → Add/Edit Virtual Layer (Добавить/Редактировать виртуальный слой).

2. В открывшемся окне вы можете вводить SQL-запросы, обращаясь к существующим слоям QGIS как к таблицам.

Пример 1: Выбор городов с населением больше 100 000

SELECT *

FROM cities

WHERE population > 100000;

Пример 2: Объединение данных из двух слоев (JOIN)

Допустим, у вас есть слой cities (города) и слой regions (регионы). Если вы хотите объединить эти два слоя по региональному коду, можно выполнить такой SQL-запрос:

SELECT cities.*, regions.region_name

FROM cities

JOIN regions ON cities.region_code = regions.region_code;

Пример 3: Пространственное объединение слоев

SELECT cities.name, regions.name AS region_name

FROM cities

JOIN regions ON ST_Within(cities.geometry, regions.geometry);

Этот запрос вернет все города и регионы, в которых они находятся.

Преимущества виртуальных слоев:

• Динамическое обновление: Если изменяются исходные данные, виртуальный слой автоматически обновляется.
• Мощные возможности SQL: Вы можете использовать полный набор функций SQL, включая пространственные функции, такие как ST_Within, ST_Intersects и другие.

4. Использование PostGIS для расширенных SQL-запросов

Если у вас есть база данных PostGIS, вы можете выполнять SQL-запросы напрямую через QGIS. Для этого:

1. Настройте подключение к базе данных PostGIS в QGIS через DB Manager или через панель Browser (Браузер).

2. Используйте окно SQL в DB Manager для выполнения запросов к вашей базе данных.

Пример: Пространственное пересечение объектов

SELECT cities.name, rivers.name

FROM cities, rivers

WHERE ST_Intersects(cities.geometry, rivers.geometry);

Этот запрос выберет все города, которые пересекаются с реками на карте.

Заключение

QGIS предоставляет множество возможностей для работы с SQL-запросами, и вы можете использовать SQL для фильтрации, анализа и манипуляции данными на разных уровнях:

1. Query Builder — для простых запросов к слоям.

2. DB Manager — для работы с базами данных, такими как PostGIS или SpatiaLite.

3. Virtual Layers — для выполнения сложных SQL-запросов и объединения данных из нескольких слоев.

Это делает QGIS гибким инструментом для работы с геопространственными данными и позволяет выполнять сложные пространственные и атрибутивные запросы с помощью SQL.

Бонусом, как и обещал:

Где можно взять слои для работы в ГИС системах?

Существует множество источников, откуда можно скачать слои геопространственных данных для работы в ГИС, таких как QGIS. Эти источники предоставляют различные типы данных, включая векторные, растровые слои, спутниковые изображения и другие геопространственные данные. В зависимости от ваших нужд (например, карты дорог, границы стран, спутниковые снимки, данные о населении и т.д.), вы можете выбрать подходящий источник.

Вот несколько популярных мест, где можно найти слои для работы в ГИС:

1. OpenStreetMap (OSM)

OpenStreetMap — это открытая и свободная для использования база данных с глобальной картографической информацией. Эти данные включают дороги, здания, водоемы, административные границы и многое другое.

Где взять данные:

• Geofabrik — предоставляет данные OpenStreetMap в виде файлов в формате .shp, .osm.pbf и других. Данные разбиты по регионам (странам и континентам).
• BBBike — позволяет извлекать данные OSM для конкретных регионов или городов в различных форматах (например, .shp, .osm, .pbf).

Как загрузить в QGIS:

1. Загрузите данные в формате .shp или .osm.
2. В QGIS используйте Layer → Add Layer → Add Vector Layer для загрузки данных.

2. Natural Earth

Natural Earth — это набор бесплатных картографических данных высокого качества, которые включают политические и физические карты всего мира. Эти данные предоставляются в удобных для использования форматах (например, .shp).

Где взять данные:

• Natural Earth — содержит векторные и растровые данные с различными уровнями детализации (10m, 50m, 110m разрешения). Доступны данные о границах стран, реках, дорогах, населенных пунктах и других объектах.

Как загрузить в QGIS:

1. Загрузите данные с сайта в формате Shapefile.
2. В QGIS используйте Layer → Add Layer → Add Vector Layer для загрузки данных.

3. ESA Copernicus Open Access Hub

Copernicus Open Access Hub — это платформа для доступа к спутниковым данным из программы Copernicus Европейского Космического Агентства (ESA). Она предоставляет доступ к данным спутников Sentinel (Sentinel-1, Sentinel-2 и др.), которые можно использовать для анализа земной поверхности, водных объектов, растительности и других природных процессов.

Где взять данные:

• Copernicus Open Access Hub — портал для загрузки данных Sentinel. Требуется регистрация.

Как загрузить в QGIS:

1. После загрузки спутниковых данных в формате .SAFE или .GeoTIFF, вы можете импортировать их в QGIS с помощью Layer → Add Layer → Add Raster Layer.

4. USGS Earth Explorer

USGS Earth Explorer — это портал для скачивания бесплатных спутниковых данных и картографической информации. Здесь можно найти данные о рельефе, спутниковые снимки Landsat, Sentinel и другие геопространственные данные.

Где взять данные:

• USGS Earth Explorer — предоставляет доступ к данным Landsat, Sentinel, MODIS и другим спутниковым снимкам. Нужно зарегистрироваться для скачивания данных.

Как загрузить в QGIS:

1. Загрузите данные (например, в формате GeoTIFF).
2. Откройте их в QGIS через Layer → Add Layer → Add Raster Layer.

5. GADM (Global Administrative Areas)

GADM предоставляет детализированные данные о границах административных единиц (страны, регионы, округа и т.д.) для всего мира.

Где взять данные:

• GADM — содержит административные границы для всех стран мира на различных уровнях (от национального до муниципального).

Как загрузить в QGIS:

1. Загрузите данные в формате .shp.
2. Импортируйте их в QGIS через Layer → Add Layer → Add Vector Layer.

6. DIVA-GIS

DIVA-GIS — это бесплатный источник географических данных, включая климатические данные и данные о землепользовании. Он также предоставляет инструменты для анализа геоданных.

Где взять данные:

• DIVA-GIS — предоставляет данные о границах стран, реках, населенных пунктах, а также биоклиматические данные.

Как загрузить в QGIS:

1. Загрузите данные в формате Shapefile.
2. Импортируйте их в QGIS через Layer → Add Layer → Add Vector Layer.

7. SRTM (Shuttle Radar Topography Mission)

SRTM — это данные о высоте земной поверхности, собранные с помощью радарной топографической миссии NASA. Данные SRTM предоставляют информацию о рельефе с разрешением 30 метров.

Где взять данные:

• USGS Earth Explorer — можно загрузить данные SRTM.
• SRTM Tile Grabber — быстрый доступ к данным SRTM.

Как загрузить в QGIS:

1. Загрузите данные в формате .hgt или .GeoTIFF.
2. Откройте их в QGIS через Layer → Add Layer → Add Raster Layer.

8. Humanitarian Data Exchange (HDX)

HDX — это открытая платформа для обмена гуманитарными и социально-экономическими данными. Здесь можно найти данные по населению, инфраструктуре, здравоохранению и другим аспектам.

Где взять данные:

• HDX — предоставляет широкий спектр данных, связанных с гуманитарными кризисами и социально-экономическими условиями.

Как загрузить в QGIS:

1. Загрузите данные в формате Shapefile или CSV.
2. Откройте их в QGIS с помощью Layer → Add Layer → Add Vector Layer (для Shapefile) или Add Layer → Add Delimited Text Layer (для CSV).

9. NASA Earthdata

NASA Earthdata — это портал для доступа к спутниковым данным NASA. Здесь можно скачать данные MODIS, VIIRS и других спутников для мониторинга изменений на Земле.

Где взять данные:

• NASA Earthdata — предоставляет доступ к различным наборам данных, включая данные о растительности, температуре поверхности, водоемах и других природных процессах.

Как загрузить в QGIS:

1. Загрузите данные в подходящем формате (например, .hdf или .GeoTIFF).
2. Откройте их в QGIS через Layer → Add Layer → Add Raster Layer.

10. OpenTopography

OpenTopography — это платформа для доступа к данным о рельефе, включая данные LiDAR.

Где взять данные:

• OpenTopography — предоставляет доступ к данным LiDAR и DEM для анализа рельефа.

Как загрузить в QGIS:

1. Загрузите данные в формате .laz, .las или других доступных форматах.
2. Используйте инструменты QGIS для работы с данными рельефа.

11. Global Land Cover Facility (GLCF)

GLCF предоставляет данные о землепользовании и земных покровах по всему миру. Включает спутниковые данные Landsat, MODIS и другие.

Где взять данные:

• GLCF — предоставляет данные о типах земных покровов и изменениях в ландшафтах.

Как загрузить в QGIS:

1. Загрузите данные в формате GeoTIFF или другом подходящем формате.
2. Импортируйте их в QGIS через Layer → Add Layer → Add Raster Layer.

Вместо заключения:

Существует множество источников для загрузки геопространственных данных, как глобального, так и локального уровня. В зависимости от ваших задач (работа с административными границами, спутниковыми снимками, рельефом или объектами инфраструктуры), вы можете выбрать подходящий ресурс и загрузить данные в удобном формате для использования в QGIS.

Рекомендации:

• Для глобальных данных (например, границы стран, основные реки, дороги) используйте Natural Earth или OpenStreetMap.
• Для спутниковых снимков и анализа рельефа подойдут USGS Earth Explorer, Copernicus Open Access Hub или NASA Earthdata.
• Для детализированных локальных данных (например, для конкретных городов и регионов) можно использовать Geofabrik, BBBike или GADM.

Если Вам интересно, что еще можно найти на канале QA Helper, прочитайте статью: Вместо оглавления. Что вы найдете на канале QA Helper - справочник тестировщика?

Не забудьте подписаться на канал, чтобы не пропустить полезную информацию: QA Helper - справочник тестировщика

Пишите в комментариях какой пункт было бы интересно рассмотреть более подробно.

Обязательно прочитайте: Что должен знать и уметь тестировщик

Также будет интересно почитать: Вопросы которые задают на собеседовании тестировщикам