Когда человек раньше говорил что он контролирует весь мир, то его обычно помещали в соседнюю палату с Бонапартом Наполеоном. Надеюсь, что эти времена остались в прошлом и каждый желающий может анализировать геоданные всей Земли и получать ответы на свои глобальные вопросы за минуты и секунды. Я опубликовал Openstreetmap_h3 — свой проект, который позволяет производить геоаналитику над данными из OpenStreetMap в PostGIS или в движке запросов, способном работать с Apache Arrow/Parquet.
Первым делом передаю привет хейтерам и скептикам. То что я разработал — действительно уникально и решает проблему преобразования и анализа геоданных используя обычные и привычные инструменты доступные каждому аналитику и датасаенс специалисту без бигдат, GPGPU, FPGA. То что выглядит сейчас простым в использовании и в коде — это мой личный проект в который я инвестировал свои отпуска, выходные, бессонные ночи и уйму личного времени за последние 3 года. Может быть я поделюсь и предысторией проекта и граблями по которым ходил, но сначала я все же опишу конечный результат.
Первый пост не претендует на монографию, начну с краткого обзора. Планирую поделиться своим опытом на конференциях и в публикациях, а тема хоть и нишевая, но будет полезна многим проектам, работающих с геоданными из OSM. Для программистов и пользователей, не сталкивавшихся с OSM инструментарием и моделью данных многие вещи могут показаться немного странными. Пока я не буду углубляться в эти основы. Отмечу лишь, что несмотря на его критику сам OpenStreetMap и сервисы развиваются и объем данных увеличивается год от года. В некоторых регионах планеты полнота и качество данных OSM лучше чем у других проектов. Поэтому просто примем модель данных такой какая она есть — гибкая и не ограничивающая пользователя в разметке, усложняющая решения для аналитики и визуализации данных за счёт особенностей дизайна структур данных.
Есть специализированные колоночные, распределенные opensource БД с которыми я также экспериментировал, но все же PostgreSQL+PostGIS на данный момент оказались самыми надёжными и протестированными доступными решениями. Ну и вишенкой на торте: эта же схема данных в Postgresql всего лишь одним параметром может загружаться в колоночное хранилище CitusDB внутри того же PostgreSQL.
С анализом геоданных вы сталкиваетесь каждый день
Когда вы пользуетесь такси в небольших городах, вызывая машину по телефону, то с высокой вероятностью вашу поездку тарифицирует программа на основе данных OSM. Для тарификации используется какой-либо из пакетов прокладки маршрута. Благодаря этому сценарию использования, сотрудники таксопарка указывают номер дома и улицу на зданиях и делают вклад не только в свой бизнес, но и в OpenStreetMap.
В сценарии аналитики данных входят и задачи где лучше разместить торговую точку, чтобы в нее приходили покупатели. Опять же данные о шаговой доступности и населенности окресностей можно извлечь из геоданных. Можно расчитывать стоимость недвижимости на основе множества факторов связанных с расположением объекта и его окружения.
Ученые могут строить прогнозные модели для предсказания эпидемий, эволюции городов, планировать рекреационные зоны и застройку существующих территорий на основе открытых геоданных.
Ну и можно ответить на любой вопрос по географии который вам придет в голову: посчитать площади городов и построек, протяженности дорог и извлечь названия городов, областей и островов. Можете, например, стать чемпионом по игре в "Города" или основать новый сервис прокатов электро самокатов. Все ограничивается лишь вашей фантазией.
Зачем этот проект и чем не устраивали существующие решения
Попытки загрузить дамп планеты в PostgreSQL базу были долгие и мучительные... Утилита Osmosis написана на java, имеет богатый функционал по работе с данными OSM и в том числе позволяет загружать эти данные в PostgreSQL базу данных со структурой pgSnapshot для аналитики. Этот проект больше не развивается по функционалу, только на поддержке. Позволяет преобразовывать данные из PBF в tsv файлы для копирования в базу данных и есть скрипты для создания схемы БД. Схема pgSnapshot lossless, что позволяет анализировать всю доступную информацию без потерь, строить маршруты итп. Но главный минус — огромные таблицы nodes, ways на сотни гигабайт, что не позволяет производить параллельную загрузку данных в таблицу сохраняя физический порядок записей, долгое построение гигантских по объему индексов, длительный full scan по гигантской таблице. Osm2pgsql, Imposm позволяет загружать данные в PostGIS, импорт данных происходит по мэппингу с потерей части данных, не подходит для роутинга и больше ориентирована для визуализации тайлов или работы с подмножеством трансформированных данных в таблицы определенные пользователем. Osmium export делает скрипты быстрее чем Osmosis, но функционал PostGIS экспорта ограничен и модель данных примитивнее по структуре чем в Osmosis.
Когда рабочая лошадка не утянет, пора запрягать слона (PostgreSQL+PostGIS)
Хотелось бы иметь данные в PostgreSQL в формате максимально близком к pgSnapshot, с быстрым механизмом загрузки данных из PBF и возможности параллельной загрузки в БД. Было бы идеально чтобы данные были секционированы так чтобы внутри одной секции были объекты расположенные близко друг к другу и сетка разбиения была приблизительно одинаковой по радиусу, как у экватора, так и у полюсов планеты. Из систем геопартиционирования рассматривал варианты — деления по административным границам/странам, разбиение прямоугольной сеткой, иерархические системы S2 и H3. И выбор был сделан в пользу последней, так как эта иерархическая система индексирования H3 имеет преимущества в сохранении расстояний (приблизительно одинаковый радиус сегмента в любой точке земного шара), отлично подходит для задач маршрутизации, библиотеки для работы с H3 хорошо поддерживаются и постоянно развиваются и доступны для PostgreSQL/JVM.
В итоге я разработал программу на Java и спроектировал схему БД совместимую с pgSnapshot, которая использует декларативное партиционирование данных PostgreSQL на основе диапазонов H3 индекса координат объектов. Утилиту, которая может автоматически создавать скрипты для схемы БД, подготавливать данные для загрузки из PBF в таблицы и загружать их в БД многопоточно.
Это был путь многочисленных экспериментов, начинал с модификации кода Osmosis и osm‑parquetizer. Собирал координаты точек линии используя memory mapped файл на сотню гигабайт в Java коде, чтобы затем выбросить этот свой код и переиспользовать функционал add‑locations‑to‑ways из osmium — наиболее оптимизированного доступного решения которое делает это же гораздо быстрее. К тому же osmium умеет собирать мультиполигоны, что гораздо быстрее и надежнее чем делать это на уровне БД, здесь тоже решил положиться на протестированное сообществом решение.
Если вас все ещё тянет в бигдату призываем на помощь Apache Arrow
У нас теперь есть секционированные по H3 индексам геоданные в PostgreSQL и вся надежность и качество этой экосистемы. Но если нам нужно передать эти же секционированные данные в Apache Spark или Hadoop экосистему, то хотелось бы сразу получить данные из PBF в формате Arrow/Parquet без промежуточного PostgreSQL. Учитывая свой предыдущий опыт оптимизаций osm‑parquetizer, я добавил в свою утилиту возможность экспорта геоданных через Apache Arrow формат сериализации. Для получения Parquet файлов, партиционированных по H3 индексу нужен дополнительный шаг постобработки результирующего набора файлов, из-за недоработки в Arrow Java API (пока что делаю это дополнительным python скриптом постпроцессинга). Из преимуществ моей утилиты перед osm‑parquetizer — больший параллелизм в трансформации PBF данных, сборка координат для линий и наличие геоиндекса H3 для nodes, ways. Одна проблема выбора для геоаналитики в parquet — это как сохранять линии. Некоторые из альтернативных решений сохраняют их как массив точек, я же пока решил кодировать линии в WKB формате.
Openstreetmap_h3 написана на Java
Как я упомянул выше, этот проект возник эволюционно из Osmosis и osm‑parquetizer и был переписан «с нуля» учитывая все грабли, которые собрал в этих Java проектах. Скрипт сборки проекта на maven, а само приложение написано как утилита командной строки:
Основная логика проекта
package com.github.isuhorukov.osm.pgsnapshot;
import com.beust.jcommander.JCommander;
import com.github.isuhorukov.osm.pgsnapshot.model.*;
import com.github.isuhorukov.osm.pgsnapshot.model.statistics.BlockStat;
import com.github.isuhorukov.osm.pgsnapshot.model.statistics.MultipolygonTime;
import com.github.isuhorukov.osm.pgsnapshot.model.statistics.PbfStatistics;
import com.github.isuhorukov.osm.pgsnapshot.model.statistics.Stat;
import com.github.isuhorukov.osm.pgsnapshot.model.table.StatType;
import com.github.isuhorukov.osm.pgsnapshot.model.table.TableStat;
import com.github.isuhorukov.osm.pgsnapshot.util.CompactH3;
import com.github.isuhorukov.osm.pgsnapshot.util.PartitionSplitter;
import com.google.common.util.concurrent.MoreExecutors;
import com.uber.h3core.H3Core;
import com.uber.h3core.util.LatLng;
import net.postgis.jdbc.geometry.LineString;
import net.postgis.jdbc.geometry.LinearRing;
import net.postgis.jdbc.geometry.Point;
import net.postgis.jdbc.geometry.Polygon;
import net.postgis.jdbc.geometry.binary.BinaryWriter;
import org.apache.arrow.memory.ArrowBuf;
import org.apache.arrow.memory.BufferAllocator;
import org.apache.arrow.memory.RootAllocator;
import org.apache.arrow.vector.*;
import org.apache.arrow.vector.complex.ListVector;
import org.apache.arrow.vector.complex.MapVector;
import org.apache.arrow.vector.complex.impl.UnionListWriter;
import org.apache.arrow.vector.complex.impl.UnionMapWriter;
import org.apache.arrow.vector.ipc.ArrowFileWriter;
import org.apache.arrow.vector.types.FloatingPointPrecision;
import org.apache.arrow.vector.types.pojo.ArrowType;
import org.apache.arrow.vector.types.pojo.Field;
import org.apache.arrow.vector.types.pojo.FieldType;
import org.apache.arrow.vector.types.pojo.Schema;
import org.apache.commons.io.IOUtils;
import org.geotools.geometry.jts.JTS;
import org.geotools.referencing.CRS;
import org.h2gis.functions.spatial.properties.ST_IsClosed;
import org.locationtech.jts.algorithm.MinimumBoundingCircle;
import org.locationtech.jts.geom.*;
import org.locationtech.jts.io.WKBWriter;
import org.opengis.referencing.FactoryException;
import org.opengis.referencing.crs.CoordinateReferenceSystem;
import org.opengis.referencing.operation.TransformException;
import org.openstreetmap.osmosis.core.container.v0_6.EntityContainer;
import org.openstreetmap.osmosis.core.container.v0_6.NodeContainer;
import org.openstreetmap.osmosis.core.container.v0_6.RelationContainer;
import org.openstreetmap.osmosis.core.container.v0_6.WayContainer;
import org.openstreetmap.osmosis.core.domain.v0_6.Entity;
import org.openstreetmap.osmosis.core.domain.v0_6.RelationMember;
import org.openstreetmap.osmosis.core.domain.v0_6.Tag;
import org.openstreetmap.osmosis.core.domain.v0_6.WayNode;
import org.openstreetmap.osmosis.pbf2.v0_6.impl.PbfBlobDecoder;
import org.openstreetmap.osmosis.pbf2.v0_6.impl.PbfBlobDecoderListener;
import org.openstreetmap.osmosis.pbf2.v0_6.impl.RawBlob;
import org.openstreetmap.osmosis.pgsnapshot.v0_6.impl.MemberTypeValueMapper;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.concurrent.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
import static java.util.stream.Collectors.toList;
public class OsmPbfTransformation {
public static final String NODES_DIR = "nodes";
public static final String RELATIONS_DIR = "relations";
public static final String WAYS_DIR = "ways";
public static final String MULTIPOLYGON_DIR = "multipolygon";
public static final String SQL_DIR = "sql";
public static final String ARROW_DIR = "arrow";
public static final String IMPORT_RELATED_METADATA_DIR = "import_related_metadata";
public static final String STATIC_DIR = "static";
public static void main(String[] args) throws Exception{
CliParameters parameters = parseCliArguments(args);
if (parameters == null){
return;
}
long commandStartTime = System.currentTimeMillis();
String sourceFilePath = parameters.sourceFilePath;
File sourcePbfFile = new File(sourceFilePath);
if(!sourcePbfFile.exists() || sourcePbfFile.length()==0){
throw new IllegalArgumentException("Input pbf should exists and should be non empty");
}
Splitter.Blocks blocks =ExternalProcessing.enrichSourcePbfAndSplitIt(sourcePbfFile);
File inputDirectory = new File(blocks.getDirectory());
File[] files = inputDirectory.listFiles();
Arrays.sort(Objects.requireNonNull(files));
File resultDirectory = prepareResultDirectories(new File(inputDirectory.getParent(),
resultDirectoryNameFromSource(inputDirectory)),
parameters.savePostgresqlTsv, parameters.saveArrow);
copyOsmiumSettings(resultDirectory);
if(parameters.savePostgresqlTsv) {
copyResources(resultDirectory, parameters.columnarStorage);
}
long processingStartTime = System.currentTimeMillis();
final H3Core h3Core = H3Core.newInstance();
ExecutorService saveExecutorService = getExecutorService(parameters.workers);
ExecutorService executorService = getExecutorService(parameters.workers);
Map<Long, BlockStat> blockStat= new ConcurrentHashMap<>();
AtomicInteger currentBlockToSave= new AtomicInteger(0);
for(File blockFile: files){
executorService.submit(() -> {
long threadStart = System.currentTimeMillis();
Long blockNumber = Long.parseLong(blockFile.getName());
if(blockNumber%1000==0){
System.out.println(blockNumber);
}
RawBlob rawBlob;
try {
FileInputStream blobInputStream = new FileInputStream(blockFile);
rawBlob = new RawBlob("OSMData", IOUtils.toByteArray(blobInputStream));
} catch (IOException e) {
throw new IllegalArgumentException(e);
}
PbfBlobDecoder blobDecoder = new PbfBlobDecoder(rawBlob, new PbfBlobDecoderListener() {
@Override
public void complete(List<EntityContainer> decodedEntities) {
long blockStartTime = System.currentTimeMillis();
GeometryFactory geometryFactory = new GeometryFactory();
MemberTypeValueMapper memberTypeValueMapper = new MemberTypeValueMapper();
Map<Short, StringBuilder> csvResultPerH33 =new HashMap<>();
BinaryWriter binaryWriter = new BinaryWriter();
ArrayList<ArrowNodeOrWay> arrowNodeOrWays = new ArrayList<>();
ArrayList<ArrowRelation> arrowRelations = new ArrayList<>();
WKBWriter wkbWriter = new WKBWriter();
final CoordinateReferenceSystem coordinateReferenceSystem;
try {
coordinateReferenceSystem = CRS.decode("EPSG:" + Serializer.SRID);
} catch (FactoryException e) {
throw new RuntimeException(e);
}
Map<Short, Stat> nodeStat =new HashMap<>();
long nodeRecords = decodedEntities.stream().
filter(entityContainer -> entityContainer instanceof NodeContainer).
map(entityContainer -> ((NodeContainer) entityContainer).getEntity()).map(entity -> {
prepareNodeData(csvResultPerH33, binaryWriter, arrowNodeOrWays,
nodeStat, entity, h3Core, parameters.collectOnlyStat, parameters.saveArrow, parameters.savePostgresqlTsv);
return null;
}).filter(Objects::isNull).count();
Map<Short, Stat> wayStat =new HashMap<>();
long wayRecords = decodedEntities.stream().
filter(entityContainer -> entityContainer instanceof WayContainer).
map(entityContainer -> ((WayContainer) entityContainer).getEntity()).map(entity -> {
prepareWayData(geometryFactory, csvResultPerH33, binaryWriter, wkbWriter,
arrowNodeOrWays, wayStat, entity, h3Core,
parameters.scaleApproximation, parameters.collectOnlyStat, parameters.skipBuildings,
coordinateReferenceSystem, parameters.saveArrow, parameters.savePostgresqlTsv);
return null;
}).filter(Objects::isNull).count();
BlockStat blockStatistic = new BlockStat(blockNumber);
blockStatistic.setThreadStart(threadStart);
if(!nodeStat.isEmpty()) {
blockStatistic.setNodeStat(nodeStat);
}
if(!wayStat.isEmpty()) {
blockStatistic.setWayStat(wayStat);
}
long relationCount = decodedEntities.stream().
filter(entityContainer -> entityContainer instanceof RelationContainer).
map(entityContainer -> ((RelationContainer) entityContainer).getEntity()).
map(entity -> {
if(!parameters.collectOnlyStat) {
long relationId = entity.getId();
if(parameters.savePostgresqlTsv){
StringBuilder relationCsv = csvResultPerH33.computeIfAbsent((short)0, h33Key -> new StringBuilder());
Serializer.serializeRelation(relationCsv, relationId, entity.getTags());
}
ArrowRelation arrowRelation = null;
if(parameters.saveArrow){
arrowRelation = new ArrowRelation(relationId, TagsUtil.tagsToMap(entity.getTags()));
arrowRelations.add(arrowRelation);
}
List<RelationMember> relationMembers = entity.getMembers();
for(int sequenceId=0; sequenceId<relationMembers.size();sequenceId++){
RelationMember relationMember = relationMembers.get(sequenceId);
long memberId = relationMember.getMemberId();
String memberType = memberTypeValueMapper.getMemberType(relationMember.getMemberType());
String memberRole = relationMember.getMemberRole();
if(parameters.saveArrow){
arrowRelation.getRelationMembers().add(
new ArrowRelationMember(memberId, memberType.charAt(0), memberRole));
}
if(parameters.savePostgresqlTsv){
StringBuilder relationMembersCsv = csvResultPerH33.computeIfAbsent((short)1, h33Key -> new StringBuilder());
Serializer.serializeRelationMembers(relationMembersCsv, relationId,
memberId, memberType, memberRole, sequenceId);
}
}
}
return null;
}).count();
long multipolygonCount = relationCount==0 ? 0 : decodedEntities.stream().
filter(entityContainer -> entityContainer instanceof RelationContainer).
map(entity -> {
for(Tag tag: entity.getEntity().getTags()){
if("type".equals(tag.getKey()) && "multipolygon".equals(tag.getValue())){
return 1;
}
}
return 0;
}).mapToLong(Integer::longValue).sum();
long relationMemberCount = decodedEntities.stream().
filter(entityContainer -> entityContainer instanceof RelationContainer).
map(entityContainer -> ((RelationContainer) entityContainer).getEntity()).
mapToLong(value -> value.getMembers().size()).sum();
blockStatistic.setNodeCount(nodeRecords);
blockStatistic.setWayCount(wayRecords);
blockStatistic.setRelationCount(relationCount);
blockStatistic.setRelationMembersCount(relationMemberCount);
blockStatistic.setMultipolygonCount(multipolygonCount);
blockStat.put(blockNumber, blockStatistic);
blockStatistic.setProcessingTime(System.currentTimeMillis()-blockStartTime);
if(!parameters.collectOnlyStat) {
if(parameters.saveArrow){
long startSaveTime = System.currentTimeMillis();
if(!arrowNodeOrWays.isEmpty()){
saveArrowNodesOrWays(arrowNodeOrWays, blockNumber, new File(resultDirectory,ARROW_DIR));
}
if(!arrowRelations.isEmpty()){
saveArrowRelations(arrowRelations, blockNumber, new File(resultDirectory, ARROW_DIR));
}
blockStatistic.setSaveTime(System.currentTimeMillis()-startSaveTime);
}
if(parameters.savePostgresqlTsv){
saveDataOnlyInOneThread(csvResultPerH33, nodeRecords, wayRecords,
blockStatistic, relationCount,
currentBlockToSave, blockNumber, resultDirectory, saveExecutorService);
}
}
}
@Override
public void error() {
System.out.println("ERROR in block "+blockNumber);
}
});
blobDecoder.run();
long threadTime = System.currentTimeMillis() - threadStart;
blockStat.get(blockNumber).setThreadTime(threadTime);
});
}
executorService.shutdown();
executorService.awaitTermination(2,TimeUnit.DAYS);
saveExecutorService.shutdown();//stop executor only when all tasks in processing executor is finished
List<BlockStat> blockStatistics = new ArrayList<BlockStat>(blockStat.values());
long multipolygonCount = blockStatistics.stream().map(BlockStat::getMultipolygonCount).mapToLong(Long::longValue).sum();
long dataProcessingTime = System.currentTimeMillis() - processingStartTime;
System.out.println(files.length+" "+" time "+dataProcessingTime);
System.out.println("diff between total and processing " + blockStatistics.stream().map(blockStat1 -> blockStat1.getThreadTime()-blockStat1.getProcessingTime()).mapToLong(Long::longValue).sum());
System.out.println("total thread time "+ blockStatistics.stream().map(BlockStat::getThreadTime).mapToLong(Long::longValue).sum());
System.out.println("total processing time "+ blockStatistics.stream().map(BlockStat::getProcessingTime).mapToLong(Long::longValue).sum());
System.out.println("total save time "+ blockStatistics.stream().map(BlockStat::getSaveTime).mapToLong(Long::longValue).sum());
System.out.println("total waiting for save time "+ blockStatistics.stream().map(BlockStat::getWaitingForSaveTime).mapToLong(Long::longValue).sum());
System.out.println("thread max time "+ blockStatistics.stream().map(BlockStat::getThreadTime).mapToLong(Long::longValue).max().orElse(0));
System.out.println("processing max time "+ blockStatistics.stream().map(BlockStat::getProcessingTime).mapToLong(Long::longValue).max().orElse(0));
System.out.println("nodes "+ blockStatistics.stream().map(BlockStat::getNodeCount).mapToLong(Long::longValue).sum());
System.out.println("ways "+ blockStatistics.stream().map(BlockStat::getWayCount).mapToLong(Long::longValue).sum());
System.out.println("relations "+ blockStatistics.stream().map(BlockStat::getRelationCount).mapToLong(Long::longValue).sum());
System.out.println("relation members "+ blockStatistics.stream().map(BlockStat::getRelationMembersCount).mapToLong(Long::longValue).sum());
System.out.println("multipolygon count "+ multipolygonCount);
if(!parameters.collectOnlyStat) {
savePartitioningScripts(resultDirectory, parameters.scriptCount,
parameters.thresholdPercentFromMaxPartition, blockStatistics, parameters.columnarStorage);
}
MultipolygonTime multipolygonTime = new MultipolygonTime(); //multipolygonCount calculation is only one reason why this generator at the end of process
if(!parameters.collectOnlyStat) {
multipolygonTime = ExternalProcessing.prepareMultipolygonDataAndScripts(sourcePbfFile,
resultDirectory, parameters.scriptCount, multipolygonCount);
}
PbfStatistics statistics = new PbfStatistics(blockStatistics);
statistics.setMultipolygonCount(multipolygonCount);
statistics.setDataProcessingTime(dataProcessingTime);
statistics.setAddLocationsToWaysTime(blocks.getAddLocationsToWaysTime());
statistics.setPbfSplitTime(blocks.getPbfSplitTime());
statistics.setMultipolygonExportTime(multipolygonTime.getMultipolygonExportTime());
statistics.setSplitMultipolygonByPartsTime(multipolygonTime.getSplitMultipolygonByPartsTime());
statistics.setTotalTime(System.currentTimeMillis()-commandStartTime);
saveStatistics(resultDirectory, statistics);
}
}Все это позволит в будущем легко перенести обработку в Apache Spark, что позволит еще больше повысить параллелизм обработки секций PBF дампа в распределенной среде. Но опять же надо доделать прототип и измерить производительность — стоит ли игра свеч. Для генерации parquet файлов такой подход по моему мнению будет иметь смысл, а для PostgreSQL TSV файлов вряд ли.
Первые шаги с Openstreetmap_h3
Итак, все что нам потребуется для начала: база данных PostgreSQL 14, данные проекта Openstreetmap и обычный ноутбук. В моем случае это Dell Latitude 7320: i7–1165G7@2.80GHz, 16Гб ОЗУ с 2Тб NVMe накопителем. Проект работает как с дампом всего мира в формате PBF, так и региональными выгрузками c Geofabrik
Клонируем и собираем проект:
git clone https://github.com/igor-suhorukov/openstreetmap_h3.git
cd openstreetmap_h3
mvn installДля работы проект требует Docker. При запуске отображается список доступных параметров и их описание:
~/dev/projects/oss_contrib/openstreetmap_h3$ java -jar target/osm-to-pgsnapshot-schema-ng-1.0-SNAPSHOT.jar
The following option is required: [-source_pbf]
Usage: <main class> [options]
Options:
* -source_pbf
Source path for OpenStreetMap data in PBF format
-collect_only_statistics
Collect only statistics from data - partition distribution
Default: false
-columnar_storage
Use columnar storage in PostgreSql tables for nodes/ways/multipolygon
Default: false
-data_partition_ratio
Filling ratio from maximum size of partition. This parameter change
PostgreSQL partitions count
Default: 0.48
-help
Information about command line parameters
-pg_script_count
Script count for PostgreSQL parallel COPY
Default: 4
-result_in_arrow
Save data transforming and enrichment in Apache Arrow format
Default: false
-result_in_tsv
Save result data in TabSeparatedValue format for PostgreSQL COPY
Default: true
-scale_approx_calc
Approximate scale calculation. Value 'false' - distance in meter
Default: false
-skip_buildings
Skip any ways with 'building' tag
Default: false
-worker_threads
Worker threads count for data processing
Default: 4После этого загружаем OSM PBD дамп планеты или какую‑либо одну страну с Geofabric
java -jar target/osm-to-pgsnapshot-schema-ng-1.0-SNAPSHOT.jar -source_pbf /home/acc/dev/map/thailand/thailand-latest.osm.pbf
В результате получаем набор скриптов для параллельной загрузки в БД:
~/dev/map/thailand/thailand-latest_loc_ways$ tree
.
├── import_related_metadata
│ ├── osm_file_block_content.tsv
│ ├── osm_file_block.tsv
│ └── osm_file_statistics.tsv
├── multipolygon
│ ├── multipolygon_aa
│ ├── multipolygon_ab
│ ├── multipolygon_ac
│ ├── multipolygon_ad
│ └── multipolygon_ae
├── nodes
│ ├── 16713.tsv
│ ├── 16717.tsv
│ ├── 16744.tsv
│ ├── 16745.tsv
│ ├── 16747.tsv
│ ├── 16748.tsv
│ ├── 16749.tsv
│ ├── 25600.tsv
│ ├── 25601.tsv
│ ├── 25606.tsv
│ ├── 25620.tsv
│ ├── 25622.tsv
│ ├── 25632.tsv
│ ├── 25634.tsv
│ ├── 25636.tsv
│ ├── 25638.tsv
│ ├── 25648.tsv
│ ├── 25649.tsv
│ ├── 25650.tsv
│ ├── 25651.tsv
│ ├── 25652.tsv
│ ├── 25653.tsv
│ ├── 25654.tsv
│ ├── 25728.tsv
│ ├── 25729.tsv
│ ├── 25730.tsv
│ ├── 25731.tsv
│ ├── 25732.tsv
│ ├── 25733.tsv
│ ├── 25734.tsv
│ ├── 25744.tsv
│ ├── 25745.tsv
│ ├── 25748.tsv
│ ├── 25749.tsv
│ ├── 25756.tsv
│ ├── 25760.tsv
│ ├── 25761.tsv
│ ├── 25762.tsv
│ ├── 25763.tsv
│ ├── 25764.tsv
│ ├── 25765.tsv
│ ├── 25766.tsv
│ ├── 25774.tsv
│ ├── 25776.tsv
│ ├── 25777.tsv
│ ├── 25778.tsv
│ ├── 25779.tsv
│ ├── 25780.tsv
│ ├── 25781.tsv
│ ├── 25782.tsv
│ ├── 25873.tsv
│ ├── 25880.tsv
│ ├── 25881.tsv
│ ├── 25882.tsv
│ ├── 25883.tsv
│ ├── 25884.tsv
│ ├── 25885.tsv
│ ├── 25886.tsv
│ ├── 25985.tsv
│ ├── 25989.tsv
│ ├── 25992.tsv
│ ├── 25993.tsv
│ ├── 25994.tsv
│ ├── 25995.tsv
│ ├── 25996.tsv
│ ├── 25998.tsv
│ ├── 26000.tsv
│ ├── 26001.tsv
│ ├── 26003.tsv
│ ├── 26008.tsv
│ ├── 26009.tsv
│ ├── 26010.tsv
│ ├── 26011.tsv
│ ├── 26012.tsv
│ ├── 26013.tsv
│ ├── 26014.tsv
│ ├── 26019.tsv
│ ├── 26026.tsv
│ ├── 26029.tsv
│ └── 26030.tsv
├── relations
│ ├── 00000.tsv
│ └── 00001.tsv
├── sql
│ ├── nodes_import_000.sql
│ ├── nodes_import_001.sql
│ ├── nodes_import_002.sql
│ ├── nodes_import_003.sql
│ ├── ways_import_000.sql
│ ├── ways_import_001.sql
│ ├── ways_import_002.sql
│ ├── ways_import_003.sql
│ ├── y_multipoly_aa.sql
│ ├── y_multipoly_ab.sql
│ ├── y_multipoly_ac.sql
│ ├── y_multipoly_ad.sql
│ └── y_multipoly_ae.sql
├── static
│ ├── database_after_init.sql
│ ├── database_init.sql
│ ├── h3_poly.tsv.gz
│ ├── multipolygon.sql
│ ├── multipolygon_tables.sql
│ └── osmium_export.json
└── ways
├── 16717.tsv
├── 16744.tsv
├── 16745.tsv
├── 16747.tsv
├── 16748.tsv
├── 16749.tsv
├── 25620.tsv
├── 25622.tsv
├── 25634.tsv
├── 25638.tsv
├── 25648.tsv
├── 25649.tsv
├── 25650.tsv
├── 25651.tsv
├── 25652.tsv
├── 25653.tsv
├── 25654.tsv
├── 25728.tsv
├── 25729.tsv
├── 25730.tsv
├── 25731.tsv
├── 25732.tsv
├── 25733.tsv
├── 25734.tsv
├── 25744.tsv
├── 25745.tsv
├── 25748.tsv
├── 25749.tsv
├── 25756.tsv
├── 25760.tsv
├── 25761.tsv
├── 25762.tsv
├── 25763.tsv
├── 25764.tsv
├── 25765.tsv
├── 25766.tsv
├── 25774.tsv
├── 25776.tsv
├── 25777.tsv
├── 25778.tsv
├── 25779.tsv
├── 25780.tsv
├── 25781.tsv
├── 25782.tsv
├── 25873.tsv
├── 25880.tsv
├── 25881.tsv
├── 25882.tsv
├── 25883.tsv
├── 25884.tsv
├── 25886.tsv
├── 25984.tsv
├── 25985.tsv
├── 25989.tsv
├── 25992.tsv
├── 25993.tsv
├── 25994.tsv
├── 25995.tsv
├── 25996.tsv
├── 25998.tsv
├── 26000.tsv
├── 26001.tsv
├── 26003.tsv
├── 26008.tsv
├── 26009.tsv
├── 26010.tsv
├── 26011.tsv
├── 26012.tsv
├── 26013.tsv
├── 26014.tsv
├── 26017.tsv
├── 26019.tsv
├── 26026.tsv
├── 26030.tsv
└── 32767.tsv
7 directories, 184 filesИ чтобы автоматически создать базу данных я использую свой docker образ PostgreSQL(Dockerfile в репозитории проекта) с предустановленными расширениями и скриптом инициализации БД, поддерживающем многопоточную загрузку данных:
docker run --name postgis14-thailand --memory=12g --memory-swap=12g --memory-swappiness 0 --shm-size=1g -v /home/acc/dev/map/database/thailand:/var/lib/postgresql/data -v /home/acc/dev/map/thailand/thailand-latest_loc_ways:/input -e POSTGRES_PASSWORD=osmworld -e LD_LIBRARY_PATH=/usr/lib/jvm/java-11-openjdk-amd64/lib/server/ -d -p 5432:5432 -p 5005:5005 5d411c3be57f -c checkpoint_timeout='15 min' -c checkpoint_completion_target=0.9 -c shared_buffers='4096 MB' -c wal_buffers=-1 -c bgwriter_delay=200ms -c bgwriter_lru_maxpages=100 -c bgwriter_lru_multiplier=2.0 -c bgwriter_flush_after=0 -c max_wal_size='32768 MB' -c min_wal_size='16384 MB'docker logs postgis14-thailand | tail -n 40
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
2023-01-29 10:22:09.841 UTC [1] LOG: starting PostgreSQL 14.1 (Debian 14.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2023-01-29 10:22:09.841 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
2023-01-29 10:22:09.841 UTC [1] LOG: listening on IPv6 address "::", port 5432
2023-01-29 10:22:09.847 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-01-29 10:22:09.855 UTC [163] LOG: database system was shut down at 2023-01-29 10:22:08 UTC
2023-01-29 10:22:09.863 UTC [1] LOG: database system is ready to accept connections
Time: 16.011 ms
ANALYZE
Time: 49.590 ms
CREATE TABLE
Time: 1.509 ms
COPY 1
Time: 1.110 ms
ANALYZE
Time: 1.167 ms
CREATE TABLE
Time: 1.284 ms
COPY 566
Time: 2.092 ms
ANALYZE
Time: 1.880 ms
CREATE TABLE
Time: 1.210 ms
COPY 17482
Time: 19.023 ms
ANALYZE
Time: 27.044 ms
SELECT 80
Time: 8.333 ms
ANALYZE
Time: 1.533 ms
SELECT 75
Time: 9.650 ms
ANALYZE
Time: 1.213 ms
2023-01-29 10:22:07.219 UTC [49] LOG: received fast shutdown request
waiting for server to shut down....2023-01-29 10:22:07.222 UTC [49] LOG: aborting any active transactions
2023-01-29 10:22:07.223 UTC [49] LOG: background worker "logical replication launcher" (PID 56) exited with exit code 1
2023-01-29 10:22:07.392 UTC [51] LOG: shutting down
..2023-01-29 10:22:09.631 UTC [49] LOG: database system is shut down
done
server stopped
PostgreSQL init process complete; ready for start up.
docker exec -it postgis14-thailand psql -U postgres -d osmworld
psql (14.1 (Debian 14.1-1.pgdg110+1))
Type "help" for help.
osmworld=# \d
List of relations
Schema | Name | Type | Owner
--------+------------------------+-------------------+----------
public | geography_columns | view | postgres
public | geometry_columns | view | postgres
public | h3_3_bounds_complex | table | postgres
public | multipolygon | partitioned table | postgres
public | multipolygon_000 | table | postgres
public | multipolygon_001 | table | postgres
public | multipolygon_002 | table | postgres
public | multipolygon_003 | table | postgres
public | multipolygon_004 | table | postgres
public | multipolygon_005 | table | postgres
public | multipolygon_006 | table | postgres
public | multipolygon_007 | table | postgres
public | multipolygon_008 | table | postgres
public | multipolygon_009 | table | postgres
public | multipolygon_010 | table | postgres
public | multipolygon_32767 | table | postgres
public | nodes | partitioned table | postgres
public | nodes_000 | table | postgres
public | nodes_001 | table | postgres
public | nodes_002 | table | postgres
public | nodes_003 | table | postgres
public | nodes_004 | table | postgres
public | nodes_005 | table | postgres
public | nodes_006 | table | postgres
public | nodes_007 | table | postgres
public | nodes_008 | table | postgres
public | nodes_009 | table | postgres
public | nodes_010 | table | postgres
public | osm_file_block | table | postgres
public | osm_file_block_content | table | postgres
public | osm_file_statistics | table | postgres
public | osm_stat_nodes_3_3 | table | postgres
public | osm_stat_ways_3_3 | table | postgres
public | relation_members | table | postgres
public | relations | table | postgres
public | spatial_ref_sys | table | postgres
public | ways | partitioned table | postgres
public | ways_000 | table | postgres
public | ways_001 | table | postgres
public | ways_002 | table | postgres
public | ways_003 | table | postgres
public | ways_004 | table | postgres
public | ways_005 | table | postgres
public | ways_006 | table | postgres
public | ways_007 | table | postgres
public | ways_008 | table | postgres
public | ways_009 | table | postgres
public | ways_010 | table | postgres
public | ways_32767 | table | postgres
(49 rows)
osmworld=# explain select h3_3, count(*) from ways group by 1;
QUERY PLAN
-------------------------------------------------------------------------------------------
Gather (cost=34456.99..97465.64 rows=76 width=10)
Workers Planned: 4
-> Parallel Append (cost=33456.99..96458.04 rows=19 width=10)
-> HashAggregate (cost=96457.88..96457.95 rows=7 width=10)
Group Key: ways_4.h3_3
-> Seq Scan on ways_004 ways_4 (cost=0.00..90269.92 rows=1237592 width=2)
-> HashAggregate (cost=60359.46..60359.53 rows=7 width=10)
Group Key: ways_9.h3_3
-> Seq Scan on ways_009 ways_9 (cost=0.00..56666.64 rows=738564 width=2)
-> HashAggregate (cost=39018.48..39018.54 rows=6 width=10)
Group Key: ways_7.h3_3
-> Seq Scan on ways_007 ways_7 (cost=0.00..36567.32 rows=490232 width=2)
-> HashAggregate (cost=33456.99..33457.06 rows=7 width=10)
Group Key: ways_2.h3_3
-> Seq Scan on ways_002 ways_2 (cost=0.00..31580.66 rows=375266 width=2)
-> HashAggregate (cost=30029.44..30029.53 rows=9 width=10)
Group Key: ways_5.h3_3
-> Seq Scan on ways_005 ways_5 (cost=0.00..28346.96 rows=336496 width=2)
-> HashAggregate (cost=26775.62..26775.72 rows=10 width=10)
Group Key: ways.h3_3
-> Seq Scan on ways_000 ways (cost=0.00..25251.75 rows=304775 width=2)
-> HashAggregate (cost=19023.96..19024.03 rows=7 width=10)
Group Key: ways_1.h3_3
-> Seq Scan on ways_001 ways_1 (cost=0.00..17901.97 rows=224397 width=2)
-> HashAggregate (cost=15682.35..15682.43 rows=8 width=10)
Group Key: ways_6.h3_3
-> Seq Scan on ways_006 ways_6 (cost=0.00..14803.23 rows=175823 width=2)
-> HashAggregate (cost=13881.85..13881.90 rows=5 width=10)
Group Key: ways_3.h3_3
-> Seq Scan on ways_003 ways_3 (cost=0.00..13173.90 rows=141590 width=2)
-> HashAggregate (cost=6970.89..6970.92 rows=3 width=10)
Group Key: ways_8.h3_3
-> Seq Scan on ways_008 ways_8 (cost=0.00..6567.26 rows=80726 width=2)
-> HashAggregate (cost=2385.09..2385.10 rows=1 width=10)
Group Key: ways_11.h3_3
-> Seq Scan on ways_32767 ways_11 (cost=0.00..2327.06 rows=11606 width=2)
-> HashAggregate (cost=37.55..37.59 rows=4 width=10)
Group Key: ways_10.h3_3
-> Seq Scan on ways_010 ways_10 (cost=0.00..36.70 rows=170 width=2)
(39 rows)
osmworld=# select h3_3, count(*) from ways group by 1 order by 2 desc limit 20;
h3_3 | count
-------+--------
25764 | 890643
26010 | 329973
25994 | 203645
25730 | 188810
25995 | 139373
26011 | 136930
25780 | 118165
25765 | 105996
26009 | 100196
25762 | 91177
26014 | 75555
26003 | 71479
26008 | 70353
25883 | 60808
25634 | 59705
25777 | 58151
16749 | 57887
25782 | 57509
25638 | 57296
25880 | 56838
(20 rows)
Запускаем контейнер базы с OSM данными всего мира и подключаемся к базе данных любым удобным пользователю клиентом:
docker start postgis14-planet-220704
psql -h 127.0.0.1 -p 5432 -U postgres -d osmworldИ любуемся статистикой базы всего мира (587 GB):
Table | Rows | Total Size | Table Size | Index(es) Size | TOAST Size
------------------------+-------+--------------------+--------------------+------------------+--------------------
*** TOTAL *** | ~1B | 587 GB (100.00%) | 501 GB (100.00%) | 54 GB (100.00%) | 32 GB (100.00%)
| | | | |
ways_051 | ~21M | 12 GB (2.02%) | 11 GB (2.14%) | 1070 MB (1.94%) | 73 MB (0.22%)
relation_members | ~113M | 9523 MB (1.58%) | 6136 MB (1.20%) | 3386 MB (6.15%) | 8192 bytes (0.00%)
ways_043 | ~14M | 8102 MB (1.35%) | 7334 MB (1.43%) | 737 MB (1.34%) | 31 MB (0.09%)
ways_052 | ~14M | 8013 MB (1.33%) | 7200 MB (1.40%) | 729 MB (1.32%) | 84 MB (0.26%)
ways_002 | ~17M | 7923 MB (1.32%) | 7013 MB (1.37%) | 870 MB (1.58%) | 40 MB (0.12%)
ways_001 | ~15M | 7360 MB (1.22%) | 6485 MB (1.26%) | 788 MB (1.43%) | 87 MB (0.26%)
ways_081 | ~14M | 6963 MB (1.16%) | 6127 MB (1.19%) | 716 MB (1.30%) | 120 MB (0.37%)
ways_004 | ~13M | 6886 MB (1.15%) | 6108 MB (1.19%) | 691 MB (1.25%) | 87 MB (0.27%)
ways_029 | ~11M | 6824 MB (1.14%) | 6197 MB (1.21%) | 567 MB (1.03%) | 60 MB (0.18%)
ways_047 | ~11M | 6750 MB (1.12%) | 6101 MB (1.19%) | 577 MB (1.05%) | 71 MB (0.22%)
ways_093 | ~13M | 6522 MB (1.09%) | 5749 MB (1.12%) | 644 MB (1.17%) | 128 MB (0.39%)
ways_095 | ~13M | 6451 MB (1.07%) | 5689 MB (1.11%) | 671 MB (1.22%) | 91 MB (0.28%)
ways_090 | ~12M | 6405 MB (1.07%) | 5708 MB (1.11%) | 620 MB (1.13%) | 77 MB (0.23%)
ways_003 | ~12M | 6401 MB (1.07%) | 5553 MB (1.08%) | 641 MB (1.16%) | 206 MB (0.63%)
multipolygon_32767 | ~187k | 6366 MB (1.06%) | 338 MB (0.07%) | 27 MB (0.05%) | 6002 MB (18.32%)
ways_005 | ~12M | 6317 MB (1.05%) | 5590 MB (1.09%) | 601 MB (1.09%) | 126 MB (0.38%)
ways_092 | ~12M | 6227 MB (1.04%) | 5511 MB (1.07%) | 610 MB (1.11%) | 107 MB (0.33%)
ways_074 | ~11M | 6208 MB (1.03%) | 5507 MB (1.07%) | 587 MB (1.07%) | 114 MB (0.35%)
ways_091 | ~11M | 6158 MB (1.02%) | 5425 MB (1.06%) | 578 MB (1.05%) | 156 MB (0.47%)
ways_098 | ~12M | 6154 MB (1.02%) | 5421 MB (1.06%) | 595 MB (1.08%) | 138 MB (0.42%)
ways_089 | ~11M | 6128 MB (1.02%) | 5418 MB (1.06%) | 576 MB (1.05%) | 134 MB (0.41%)
ways_080 | ~11M | 5973 MB (0.99%) | 5206 MB (1.01%) | 549 MB (1.00%) | 217 MB (0.66%)
ways_097 | ~11M | 5940 MB (0.99%) | 5298 MB (1.03%) | 547 MB (0.99%) | 95 MB (0.29%)
ways_045 | ~11M | 5877 MB (0.98%) | 5261 MB (1.03%) | 546 MB (0.99%) | 70 MB (0.21%)
ways_053 | ~9M | 5810 MB (0.97%) | 5289 MB (1.03%) | 487 MB (0.88%) | 34 MB (0.10%)
ways_019 | ~10M | 5794 MB (0.96%) | 5124 MB (1.00%) | 517 MB (0.94%) | 153 MB (0.47%)
ways_006 | ~10M | 5774 MB (0.96%) | 5033 MB (0.98%) | 510 MB (0.93%) | 231 MB (0.70%)
ways_046 | ~10M | 5702 MB (0.95%) | 5094 MB (0.99%) | 505 MB (0.92%) | 103 MB (0.31%)
ways_042 | ~10M | 5683 MB (0.95%) | 5075 MB (0.99%) | 496 MB (0.90%) | 112 MB (0.34%)
ways_049 | ~9M | 5663 MB (0.94%) | 5118 MB (1.00%) | 456 MB (0.83%) | 89 MB (0.27%)
ways_037 | ~9M | 5634 MB (0.94%) | 5040 MB (0.98%) | 447 MB (0.81%) | 147 MB (0.45%)
ways_054 | ~9M | 5627 MB (0.94%) | 4856 MB (0.95%) | 485 MB (0.88%) | 286 MB (0.87%)
ways_017 | ~10M | 5605 MB (0.93%) | 4950 MB (0.97%) | 507 MB (0.92%) | 148 MB (0.45%)
ways_075 | ~9M | 5587 MB (0.93%) | 4940 MB (0.96%) | 488 MB (0.89%) | 159 MB (0.49%)
ways_026 | ~9M | 5585 MB (0.93%) | 5064 MB (0.99%) | 482 MB (0.88%) | 39 MB (0.12%)
ways_048 | ~9M | 5584 MB (0.93%) | 4985 MB (0.97%) | 484 MB (0.88%) | 114 MB (0.35%)
ways_082 | ~10M | 5548 MB (0.92%) | 4890 MB (0.95%) | 500 MB (0.91%) | 158 MB (0.48%)
ways_000 | ~9M | 5460 MB (0.91%) | 4750 MB (0.93%) | 461 MB (0.84%) | 248 MB (0.76%)
ways_094 | ~10M | 5457 MB (0.91%) | 4737 MB (0.92%) | 493 MB (0.89%) | 227 MB (0.69%)
ways_033 | ~8M | 5454 MB (0.91%) | 4808 MB (0.94%) | 403 MB (0.73%) | 243 MB (0.74%)
ways_087 | ~9M | 5445 MB (0.91%) | 4808 MB (0.94%) | 463 MB (0.84%) | 173 MB (0.53%)
ways_010 | ~9M | 5357 MB (0.89%) | 4774 MB (0.93%) | 440 MB (0.80%) | 143 MB (0.44%)
ways_040 | ~9M | 5348 MB (0.89%) | 4722 MB (0.92%) | 467 MB (0.85%) | 159 MB (0.48%)
ways_088 | ~9M | 5262 MB (0.88%) | 4662 MB (0.91%) | 455 MB (0.83%) | 145 MB (0.44%)
ways_060 | ~8M | 5243 MB (0.87%) | 4673 MB (0.91%) | 420 MB (0.76%) | 150 MB (0.46%)
ways_096 | ~10M | 5207 MB (0.87%) | 4613 MB (0.90%) | 495 MB (0.90%) | 99 MB (0.30%)
ways_024 | ~8M | 5205 MB (0.87%) | 4722 MB (0.92%) | 431 MB (0.78%) | 52 MB (0.16%)
ways_071 | ~9M | 5198 MB (0.87%) | 4625 MB (0.90%) | 469 MB (0.85%) | 104 MB (0.32%)
:
Воспользуемся примером из моих прошлых постов и посчитаем количество зданий школ во всем мире с разбивкой данных по H3 индексу:
docker start postgis14-planet-220704
psql -h 127.0.0.1 -p 5432 -U postgres -d osmworld
Password for user postgres:
🧐 🐘 postgres_dba 6.0 installed. Use ":dba" to see menu
Timing is on.
psql (14.6 (Ubuntu 14.6-1.pgdg20.04+1), server 14.1 (Debian 14.1-1.pgdg110+1))
Type "help" for help.
osmworld=# \timing on
Timing is on.
osmworld=# create table school
as select h3_3, count(*) as "count" from ways
where closed and (tags->'building' = 'school'
or (tags->'building' is not null and tags->'amenity'='school'))
group by h3_3 order by 2 desc;
SELECT 6783
Time: 215154,128 ms (03:35,154)Посмотрим план запроса, что же заняло эти 03:35 минуты у базы данных, как наш запрос выполнялся параллельно на всех партициях:
explain create table school
as select h3_3, count(*) as "count" from ways
where closed and (tags->'building' = 'school'
or (tags->'building' is not null and tags->'amenity'='school'))
group by h3_3 order by 2 desc
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Merge (cost=13217952.21..13219300.04 rows=11052 width=10)
Workers Planned: 6
-> Sort (cost=13216952.12..13216956.72 rows=1842 width=10)
Sort Key: (count(*)) DESC
-> Parallel Append (cost=1107174.96..13216852.21 rows=1842 width=10)
-> GroupAggregate (cost=1889369.47..1890603.83 rows=7 width=10)
Group Key: ways_51.h3_3
-> Sort (cost=1889369.47..1889780.90 rows=164572 width=2)
Sort Key: ways_51.h3_3
-> Seq Scan on ways_051 ways_51 (cost=0.00..1873744.28 rows=164572 width=2)
Filter: (closed AND (((tags -> 'building'::text) = 'school'::text) OR (((tags -> 'building'::text) IS NOT NULL) AND ((tags -> 'amenity'::text) = 'school'::text))))
-> HashAggregate (cost=1278539.18..1278539.58 rows=40 width=10)
Group Key: ways_2.h3_3
-> Seq Scan on ways_002 ways_2 (cost=0.00..1277795.15 rows=148806 width=2)
Filter: (closed AND (((tags -> 'building'::text) = 'school'::text) OR (((tags -> 'building'::text) IS NOT NULL) AND ((tags -> 'amenity'::text) = 'school'::text))))
-> GroupAggregate (cost=1269524.73..1270332.33 rows=7 width=10)
Group Key: ways_43.h3_3
-> Sort (cost=1269524.73..1269793.90 rows=107671 width=2)
Sort Key: ways_43.h3_3
-> Seq Scan on ways_043 ways_43 (cost=0.00..1260525.44 rows=107671 width=2)
Filter: (closed AND (((tags -> 'building'::text) = 'school'::text) OR (((tags -> 'building'::text) IS NOT NULL) AND ((tags -> 'amenity'::text) = 'school'::text))))
-> GroupAggregate (cost=1248285.94..1249040.42 rows=7 width=10)
Group Key: ways_52.h3_3
-> Sort (cost=1248285.94..1248537.41 rows=100588 width=2)
Sort Key: ways_52.h3_3
-> Seq Scan on ways_052 ways_52 (cost=0.00..1239928.03 rows=100588 width=2)
Filter: (closed AND (((tags -> 'building'::text) = 'school'::text) OR (((tags -> 'building'::text) IS NOT NULL) AND ((tags -> 'amenity'::text) = 'school'::text))))
...
JIT:
Functions: 806
Options: Inlining true, Optimization true, Expressions true, Deforming true
(612 rows)И визуализируем результат в QGIS:
select
h3_to_geo_boundary_geometry(h3_3::h3index),
count
from school
where count>100PostgreSQL поддерживается большинством ПО для визуализации данных, сложные запросы с оконными функциями, подзапросами также работают. Поскольку постгрес можно запускать почти на любом оборудовании то и геоаналитика доступна везде, лишь с вопросами к производительности.
Продолжение истории
Две беды: дороги и полнота данных. Считаем протяженность дорог родины по данным OpenStreetMap
Колоночная база данных в PostgreSQL 15 и факты о территории России по данным OpenStreetMap
Как в Северной Америке с дорогами и плотностью застройки? Работаем с 100Гб OSM данных в PostgreSQL
Добро пожаловать в мир аналитики гео данных!
Уникальность проекта OpenStreetMap в возможности каждого на планете использовать данные проекта, но с учетом объема данных, это было не так просто. Теперь для запуска базы не нужен мощный специализированный сервер и, надеюсь, что мой подход к секционированию данных OSM и возможность использовать для аналитики геопространственных данных PostgreSQL позволит большему количеству людей делать запросы к этому уникальному проекту и разрабатывать новые сервисы, которые позволят учитывать расстояния, объекты реального мира и улучшать мир вокруг нас.
