4 ms·

GeoDesk is a spatial database engine for OpenStreetMap features

107 points by goplayoutside 4y ago

crimsoneer 4y ago
This seems potentially...very useful, if I've understood the use case right? I've really struggled every time I've wanted to use OSM data (I tend to fall back on downloading the geopackage from Geofabrik)
- techterrier 4y ago
  We use BigQuery and it works great for us! dataset: https://console.cloud.google.com/marketplace/details/openstreetmap/geo-openstreetmap https://console.cloud.google.com/marketplace/details/openstr...
  - kevinventullo 4y ago
    Out of curiosity, is it problematic that the data appears to be three years old?
college_physics 4y ago
a comparison with postgis (pros and cons, most appropriate use cases etc) would be quite informative
- geodesk 4y ago
  The most apparent difference is storage consumption: A GeoDesk GOL file for a recent planet comes in at 80 GB -- as a pgSQL DB, it would require nearly 1 TB. GeoDesk spatial queries (especially intersects) leave pgSQL in the dust, even on low-end hardware (Throughput rates of 30 million features per second for bbox, 3M for 10K-vertex polygon, on a dual-core notebook with 8 GB RAM, SATA SSD; 4x this on a proper workstation). However, GeoDesk isn't intended as a general-purpose database. If you want to find the element with wikidata tag "Q1234", or all postal-code areas with ZIPs between 90000 and 92000, pgSQL will most likely perform better, assuming the data is properly indexed. One thing to note is that GOLs are read-only; future releases will allow incremental updates with fresh OSM data, but you cannot modify the data or add to it. We designed GeoDesk with the assumption that users analyze/extract the OSM data with GeoDesk's tag-filtering/spatial operations, and then migrate the results to another database that matches their applications' needs. GOLs simplify distribution of OSM data. Internally, a GOL is organized into tiles (tens of thousands for a complete planet). With basic zip compression, these tiles are comparable in size to OSM-PBF (the most popular distribution format). As @pastage has already mentioned, converting OSM data into a GOL is significantly faster than importing into pgSQL (at least 20x). In the future, we envision that users will simply download GeoDesk tiles, skipping the conversion step altogether. Another key difference is ease of development. GeoDesk queries return feature objects, obviating the need for object-relational mapping (For now, this is limited to Java and other JVM languages; we're considering a C++ port, which would pave the way for a Python API). GOLs replicate OSM object graphs, making it easy to explore the relationships between nodes, ways and relations: Find all residential streets within a city, count the number of speed bumps on each, find the crossroads, check which of these belong to bus routes, etc. All this can be done in pgSQL as well, but will require planning and proper table design to get it right.
- pastage 4y ago
  Import with osm2psql can take 13 h on a 128 GB machine with nvme, compared to 30 minutes with geodesk with only 32GB of ram. They do not solve the same thing it seems. But if you want to ask questions on the data and how it relates to itself the geodesk is for you. It is made to do intersect and members checking.
cwmoore 4y ago
From: https://docs.geodesk.com/core-concepts https://docs.geodesk.com/core-concepts "GeoDesk... [stores] coordinates as 32-bit integers, using an artificial unit called imp (integer, Mercator-projected). This allows for a coordinate resolution of roughly 1 centimeter. The Earth (excluding polar areas) is represented as a square, 232 imps high and wide..."
- geodesk 4y ago
  This is essentially Spherical Mercator (EPSG:3857), except that instead of meters at the equator, planar distances are scaled so that the Earth's circumference equals 2^32 units (the maximum range of a 32-bit integer). This simplifies indexing, and also allows coordinate operations that cross the Antimeridian to wrap naturally. We chose this projection because it preserves angles and shapes at local scale, and it is already familiar to OpenStreetMap users. There are API methods to convert coordinates back to WGS-84 (degrees longitude/latitude) and obtain distances in meters (scaled based on latitude). For operations for which Mercator is not suitable (such as measuring the exact area of large landmasses), users can re-project the feature geometries using a library like Proj4J.
shock-value 4y ago
There isn’t much information available on how its indexing works internally, whether tag existence / tag values can be indexed, etc. So it’s hard to judge the extent to which this could be a replacement for Postgres with PostGIS.
- geodesk 4y ago
  Indexing in GeoDesk GOL files is predominantly spatial (a quadtree-like structure combined with r-trees), with probabilistic indexing for the most frequent keys (`highway`, `building`, `amenity`, etc.). This means queries with a spatial dimension execute very quickly (bounding-box queries have a typical throughput of tens of millions of features per second on a simple dual-core notebook). GeoDesk is less suitable for non-spatial queries (e.g. "Where in the world is the Louvre?" may take several seconds on a planet-size database), but we'll consider enhanced indexing for these scenarios if there are enough real-world use cases to warrant it.
bdon 4y ago
This is interesting! * Is there a way to access a GOL file from a different language? * Can a GOL be updated in-place with replication diffs?
- geodesk 4y ago
  - Currently, GeoDesk is only available for Java (and other JVM languages). We're considering a future C++ implementation of the database engine (with possible Python bindings) - We will be adding the capability to incrementally update GOLs via OpenStreetMap change files (.osc). We expect this feature to ship in Version 0.3 (slated for Q2 of 2023)

107 points by goplayoutside 4y ago

This seems potentially...very useful, if I've understood the use case right? I've really struggled every time I've wanted to use OSM data (I tend to fall back on downloading the geopackage from Geofabrik)

techterrier 4y ago
We use BigQuery and it works great for us! dataset: https://console.cloud.google.com/marketplace/details/openstreetmap/geo-openstreetmap https://console.cloud.google.com/marketplace/details/openstr...
- kevinventullo 4y ago
  Out of curiosity, is it problematic that the data appears to be three years old?

college_physics 4y ago

a comparison with postgis (pros and cons, most appropriate use cases etc) would be quite informative

geodesk 4y ago
The most apparent difference is storage consumption: A GeoDesk GOL file for a recent planet comes in at 80 GB -- as a pgSQL DB, it would require nearly 1 TB. GeoDesk spatial queries (especially intersects) leave pgSQL in the dust, even on low-end hardware (Throughput rates of 30 million features per second for bbox, 3M for 10K-vertex polygon, on a dual-core notebook with 8 GB RAM, SATA SSD; 4x this on a proper workstation). However, GeoDesk isn't intended as a general-purpose database. If you want to find the element with wikidata tag "Q1234", or all postal-code areas with ZIPs between 90000 and 92000, pgSQL will most likely perform better, assuming the data is properly indexed. One thing to note is that GOLs are read-only; future releases will allow incremental updates with fresh OSM data, but you cannot modify the data or add to it. We designed GeoDesk with the assumption that users analyze/extract the OSM data with GeoDesk's tag-filtering/spatial operations, and then migrate the results to another database that matches their applications' needs. GOLs simplify distribution of OSM data. Internally, a GOL is organized into tiles (tens of thousands for a complete planet). With basic zip compression, these tiles are comparable in size to OSM-PBF (the most popular distribution format). As @pastage has already mentioned, converting OSM data into a GOL is significantly faster than importing into pgSQL (at least 20x). In the future, we envision that users will simply download GeoDesk tiles, skipping the conversion step altogether. Another key difference is ease of development. GeoDesk queries return feature objects, obviating the need for object-relational mapping (For now, this is limited to Java and other JVM languages; we're considering a C++ port, which would pave the way for a Python API). GOLs replicate OSM object graphs, making it easy to explore the relationships between nodes, ways and relations: Find all residential streets within a city, count the number of speed bumps on each, find the crossroads, check which of these belong to bus routes, etc. All this can be done in pgSQL as well, but will require planning and proper table design to get it right.
pastage 4y ago
Import with osm2psql can take 13 h on a 128 GB machine with nvme, compared to 30 minutes with geodesk with only 32GB of ram. They do not solve the same thing it seems. But if you want to ask questions on the data and how it relates to itself the geodesk is for you. It is made to do intersect and members checking.

cwmoore 4y ago

From: https://docs.geodesk.com/core-concepts https://docs.geodesk.com/core-concepts "GeoDesk... [stores] coordinates as 32-bit integers, using an artificial unit called imp (integer, Mercator-projected). This allows for a coordinate resolution of roughly 1 centimeter. The Earth (excluding polar areas) is represented as a square, 232 imps high and wide..."

geodesk 4y ago
This is essentially Spherical Mercator (EPSG:3857), except that instead of meters at the equator, planar distances are scaled so that the Earth's circumference equals 2^32 units (the maximum range of a 32-bit integer). This simplifies indexing, and also allows coordinate operations that cross the Antimeridian to wrap naturally. We chose this projection because it preserves angles and shapes at local scale, and it is already familiar to OpenStreetMap users. There are API methods to convert coordinates back to WGS-84 (degrees longitude/latitude) and obtain distances in meters (scaled based on latitude). For operations for which Mercator is not suitable (such as measuring the exact area of large landmasses), users can re-project the feature geometries using a library like Proj4J.

shock-value 4y ago

There isn’t much information available on how its indexing works internally, whether tag existence / tag values can be indexed, etc. So it’s hard to judge the extent to which this could be a replacement for Postgres with PostGIS.

geodesk 4y ago
Indexing in GeoDesk GOL files is predominantly spatial (a quadtree-like structure combined with r-trees), with probabilistic indexing for the most frequent keys (`highway`, `building`, `amenity`, etc.). This means queries with a spatial dimension execute very quickly (bounding-box queries have a typical throughput of tens of millions of features per second on a simple dual-core notebook). GeoDesk is less suitable for non-spatial queries (e.g. "Where in the world is the Louvre?" may take several seconds on a planet-size database), but we'll consider enhanced indexing for these scenarios if there are enough real-world use cases to warrant it.

bdon 4y ago

This is interesting! * Is there a way to access a GOL file from a different language? * Can a GOL be updated in-place with replication diffs?

geodesk 4y ago
- Currently, GeoDesk is only available for Java (and other JVM languages). We're considering a future C++ implementation of the database engine (with possible Python bindings) - We will be adding the capability to incrementally update GOLs via OpenStreetMap change files (.osc). We expect this feature to ship in Version 0.3 (slated for Q2 of 2023)