26
votes
Accepted
Merging OSM PBF files?
You can use osmium for merging:
osmium merge file1.osm file2.osm -o merged.osm.
Alternatively try osmosis:
osmosis --rx file1.osm --rx file2.osm --merge --wx merged.osm
Note that osmosis has a ...
- 6,255
23
votes
Select all points within a bounding box
You have three problems with your statement though the error message is hinting only at part of it... "WHERE must be type boolean" means that the information you gave the WHERE is not evaluating to a ...
- 3,640
9
votes
Wrong shortest path using pg routing
The two lookup points you pass into the (K)NN sub-queries can't be referenced in EPSG:3857 - their coordinate values seem to be in degree and are likely EPSG:4326!
Assuming planet_osm_roads.way has ...
- 30.9k
8
votes
Merging OSM PBF files?
On Ubuntu:
sudo apt-get install osmium-tool
osmium cat new-york.osm.pbf new-jersey.osm.pbf connecticut.osm.pbf -o ny-nj-ct.osm.pbf
- 203
8
votes
Accepted
Where is flat-nodes file located?
The flat nodes file is just a temporary file generated by the osm2pgsql program during import, so it isn't "located" anywhere. Instead, the --flat-nodes <flat nodes> input parameter, ...
- 2,582
8
votes
Use ST_DWithin on WGS84 geometry data
The problem is that you are using ST_DWithin on GEOMETRY objects with coordinates in WGS84. Therefore, your function call is requesting all features within 500 degrees (i.e., everything on the planet)....
- 1,214
7
votes
How to categorize features based on hstore values in QGIS?
In QGIS 3.4, you can use the hstore_to_map expression to get a map (dictionary) of the hstore field. Then you can use one of the map expressions to manipulate it:
In QGIS 3.6, you can use the "...
- 7,447
6
votes
Accepted
Projection of osm2pgsql nodes table?
Well, I feel dumb. They ARE actually lat, lon values, except they are stored as integers, i.e. with out a decimal. The following works:
UPDATE nodes SET geom = ST_SetSRID(
ST_MakePoint(lon/...
- 748
6
votes
OpenStreetMap - Determining number of nodes, ways, and relations in a PBF file?
You can run osm2pgsql with null output, no RAM cache, and no slim cache. For a 2.5GB PBF extract of Canada, it took me about 37 seconds to count the nodes, on an NVMe SSD.
$ osm2pgsql --output null --...
- 291
5
votes
Loading OpenStreetMap data to a custom PostgreSQL schema via osm2pgsql
osm2pgsql creates the tables and indexes in the user default schema, i.e. the first one found in its search path, which is usually public. By changing this setting, in PostgreSql, one can load the ...
- 43.1k
5
votes
Accepted
Lossless import of full OpenStreetMap planet dump into PostGIS
For osm2pgsql, you can use the --hstore parameter.
From the doc:
--hstore or -k adds any tags not already in a conventional column to a hstore column. With the standard stylesheet this would ...
- 43.1k
4
votes
pgr_createTopology - how tolerance?
I guess that you took the example createTopology function which is shown for WGS84 geometries, using degrees as a projection unit. 0.00001 degree tolerance means that your points have to be less than ...
- 185
4
votes
Deleting duplicate geometry in PostGIS tables
A more general answer to easily delete geometry duplicates in PostGIS table. The following command deletes all features with duplicate geometry in "table_name" based on primary key (column &...
- 9,784
4
votes
Accepted
How can I get the length of segments from the output of pgr_dijkstra?
pgr_dijkstra(edges_sql, start_vid, end_vid, directed:=true ) is a SQL function. It returns the following, regardless of what you input into its TEXT edges_sql argument.
RETURNS SET OF (seq, path_seq,...
- 3,417
4
votes
Add one-way streets to network data in postgis
In pgRouting the direction is always from source to target.
When you run pgr_createTopology(), then source is the start point of your line geometry and target is the end point.
If you want to model ...
- 4,796
4
votes
Accepted
Loading OpenStreetMap data to a custom PostgreSQL schema via osm2pgsql
Since osm2pgsql version 1.4, you can specify a destination schema with option --output-pgsql-schema when you use default behaviour (implicit -O pgsql default option). So, it would look like the ...
- 31.2k
4
votes
Accepted
Define table name with osm2pgsql
You can change the planet part by anything you want using the command line argument --prefix
The following command will create tables such as abc_osm_line
osm2pgsql.exe source.osm.pbf --prefix abc [.....
- 43.1k
4
votes
Accepted
Extract OSM data (xml) from large polygon (up to 1GB of data)
Thanks to Mesa's and mdd's comment I found that sollution:
Download the whole dataset from download.geofabrik.de (or anywhere else)
Create a polygon for clipping (in my case a buffer of a line)
Save ...
- 8,396
4
votes
OpenStreetMap - Determining number of nodes, ways, and relations in a PBF file?
You can use PyOsmium Python package. -Documentation-
It can be time consuming, because it scans all the data to count all elements.
import osmium
class Counter(osmium.SimpleHandler):
def __init__(...
- 78k
4
votes
Accepted
Where to find Windows osm2psql binaries in 2018?
From the README:
Unoffical builds for Windows are available from AppVeyor but you need to find the right build artifacts.
However, the the links in the README to builds of the latest release 0.96....
- 69.5k
4
votes
Accepted
Some OSM tags in polygons and some in points - how to bring them together
One way of getting around this is to use the center function in an Overpass query. (Documentation here.)
Normally, a query for museums gives us what you picture above: nodes as well as ways, ...
- 3,910
3
votes
Accepted
PostGIS error with working expression
I believe you are using the IS NOT syntax instead of the IS DISTINCT FROM syntax.
If what you want is all records where bridge is equal to 'no' or is NULL and tunnel is equal to 'no' or is NULL, then ...
- 190
3
votes
Building a graph from OSM?
http://osm-traffic.com/ allows direct download of graph data from OpenStreetMap in json format.
Disclaimer: I work with the company who developed, it might become a pay service.
- 61
3
votes
Slow import via osm2pgsql to PostgreSQL database
I ran into this issue today. Turns out I have to add --slim flag.
From OSM wiki page:
Slim mode
osm2pgsql has two main modes of running - normal and slim mode.
It is highly recommended to run ...
- 131
3
votes
Deleting duplicate geometry in PostGIS tables
Here is another method I used to remove duplicates from a SSURGO soil data download. The downloaded shapefiles did not have a unique key, so a serial pk column was generated when I imported to PostGIS....
- 1,591
3
votes
Accepted
osm2pgsql style not applied
Although the file is called style, it is not a rendering style like you apply on QGIS layers. It is just a filter on the OSM raw data ro be passed into the Postgis database.
You still need rendering ...
- 77.1k
3
votes
Work flow for using AND adding OSM-Data
QGIS had a plugin for editing and uploading OSM data, but that was dropped due to lack of maintenance. Currently, no OSM user authentication is stored inside QGIS, and there is no plan to do it.
OSM ...
- 77.1k
3
votes
Column "tags" is missing in PostgreSQL table created with osm2pgsql
Tables planet_osm_nodes, planet_osm_ways, and planet_osm_rels are tracking tables which are made for the system, not for the end user. Those tables are not supposed to contain tags and they would be ...
- 69k
3
votes
Accepted
Ring geometries in pgRouting OSM DUMP
I've made an experiment. Just closed one of the isolated roads.
Before this I got result of pgr_analyzeGraph
NOTICE: Ring geometries: 6
After closing that road I've got
NOTICE:...
- 811
3
votes
Accepted
Create a line string from street geometry
I found a solution that fits my needs:
Dump all points from a street geometry.
Collect all points, create a Voronoi Diagram from the collection, then Dump the geometries to split them in individual, ...
- 226
Only top scored, non community-wiki answers of a minimum length are eligible