22

One way of doing this, is to use a window function and partition by geometry, so that each repeated geometry gets an id: 1, 2, 3, etc (or 1, 2) in your case, and then you just select from the table where the id = 1, to get a unique set of values (attributes and geometry) back, eg, WITH unique_geoms (id, geom) as (SELECT row_number() OVER (PARTITION BY ...


16

Fixed. The problem was is no primary key. In pgAdmin do this request. ALTER TABLE tableName ADD PRIMARY KEY (id); Example for planet_osm_line table and setting osm_id column ,as primary key : ALTER TABLE planet_osm_line ADD PRIMARY KEY (osm_id); untill osm_id is unique.


16

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 boolean result. ST_MakeEnvelope asks for its parameters in this order: xmin, ymin, xmax, ymax, srid. You incorrectly passed in ymax, ymin, xmax, xmin, srid. ...


15

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 weird syntax and you need to specify the --merge commands n-1 times for merging n files.


12

The solution is to create a primary key, as already mentioned. But by design, osm2pgsql does not garantee that the osm_id is unique. It can produce several rows with the same osm_id. To edit planet_osm_* tables in QGIS, it is wise the add another id column, for example gid. To add a unique gid column: ALTER TABLE planet_osm_point ADD gid serial PRIMARY ...


11

The osm2pgsql documentation suggests osm2pgsql -c -d gis --slim -C <cache size> --flat-nodes <flat nodes> planet-latest.osm.pbf. where <cache size> is 24000 on machines with 32GiB or more RAM or about 75% of memory in MiB on machines with less <flat nodes> is a location where a 24GiB file can be saved. This works, and on a fast ...


10

If you have imported a planet or extract some time ago and have now downloaded a (much) newer planet or extract: It does not really make sense to do any updating as I think calculating and applying the diff will not save you time. Just re-run osm2pgsql again and it will remove the tables and create new ones resulting in updated data. If you want to keep ...


9

There's a few issues. The first one is hardware. Your drive is a WD Green drive, which is generally is about 5400 RPM which is a very slow drive, slower than typical 7200 RPM consumer drives. One of the biggest tasks in updating is fetching node positions. This is random access, which your drive sucks at. One option is to use the --flat-nodes option, ...


9

I imported a Planet File on a 24Gb Machine (Ubuntu Trusty) with the following .. bzcat planet-latest.osm.bz2 | osm2pgsql --verbose -U YourUser --flat-nodes flat-nodes --keep-coastlines --cache 24000 --hstore --hstore-add-index --tablespace-index pg_default --exclude-invalid-polygon --number-processes 6 --unlogged --cache-strategy dense --extra-attributes -...


7

Your computer should be fine for importing Europe. Given your dataset size and computer, I'd recommend something like this I'm assuming that you have an 8 thread CPU, if not, adjust --number-processes. You don't need 25GB of ram for cache with just Europe. For Europe, flat nodes should be smaller and faster than in-DB storage of node positions. If ...


7

You will have to create a new one. Nominatim requires a different database scheme than your rendering stack because it needs to perform really different queries. Using the same database for rendering and geocoding would be very inefficient, if possible at all.


7

Besides hardware and other software, rendering performance depends on the database, custom indexes, and the SQL in the style rendered. The style makes a huge difference, and badly written layer definitions can result in abysmal performance. If you change the database schema, it requires changing both the custom indexes and layer SQL, so it's not possible to ...


7

QGIS has several ways to import OSM data: using Layer -> Add Layer -> Add vector layer using Vector -> OpenStreetmap Both create a spatialite database. You can take the layers of the spatialite database and export them to an empty Postgis database. But using om2pgsql is the far better tool, because it can deal with large amounts of data far ...


6

Replacing st_length with st_transform(way,4326)::geography solves the geodetic distance problem - thanks unicoletti!. Old Beechy Rail Trail | 47.2km Great Southern Rail Trail | 53.5km Ballarat-Skipton Rail Trail | 57.2km High Country Rail Trail | 63.7km East Gippsland Rail Trail | 97.6km ...


6

You are not using geodesic functions to calculate the length, which means that for a point there is an error factor of: cos( LATITUDE * pi() / 180 ) If you then multiply the calculated lenght by the error factor you should obtain a value pretty close to the actual trail length. For instance the Old Beechy Rail Trail is close to Melbourne, which has a ...


6

planet_osm_roads contains only ways that are used for rendering low zoom levels, such as motorsways, rivers, etc. It does not contain details like residential roads, streams and various other features normally rendered only at high zoom. The default C tag transform definitions of what to add to the roads table are tagtransform.cpp If you want to customize ...


6

I found the solution here: http://postgis.net/docs/UpdateGeometrySRID.html --This will change the srid of the roads table to 3857 from whatever it was before UpdateGeometrySRID(varchar table_name, varchar column_name, integer srid); So for my database the table name was 'planet_osm_line' and the column that contains the srid and geometry is 'way'. the SQL ...


6

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/10000000.0,lat/10000000.0), 4326 ) You simply need to divide the values by ten million.


5

You can find the source code of osm2pgsql here: https://github.com/openstreetmap/osm2pgsql/blob/master/reprojection.c and you will find this note in line 67: /* hard-code the source projection to be lat/lon, since OSM XML always * has coordinates in degrees. */ pj_source = pj_init_plus("+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs"); /* hard-...


5

ST_AsLatLonText documentation says: It is assumed the point is in a lat/lon projection. The X (lon) and Y (lat) coordinates are normalized in the output to the "normal" range (-180 to +180 for lon, -90 to +90 for lat). So you are seeing your point (in 900913) "wrapped" around until it eventually gets into the normal range. To see your positions ...


5

As you query to have only nodes it is that all you get. See http://wiki.openstreetmap.org/wiki/Overpass_API/Language_Guide#Nodes for more details. Your query should be http://overpass-api.de/api/interpreter?data=(node(52.06,5.04,52.08,5.06);<;>;);out;


5

The short answer is, there's no outstanding way. Options that won't work well Hadoop Hadoop and similar tools aren't the solution, as it's entirely possible to do this type of analysis on a reasonably powerful server. You may not have a reasonably powerful server, in which case Hadoop wouldn't be a good option since it needs a cluster. If you happen to ...


5

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


5

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 --cache 0 canada-latest.osm.pbf osm2pgsql version 0.96.0 (64 bit id space) WARNING: ram cache is disabled. This will likely slow down processing a lot. Using ...


5

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, allows you to specify the location and filename where osm2pgsql should create this temporary file. Note that for Geofabrik's Europe extract, the generated flat nodes file ...


5

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 result in tags like highway appearing in a conventional column while tags not in the style like name:en or lanes:forward would appear only in the hstore ...


4

The file seems to have moved. Have a look here: https://raw.github.com/openstreetmap/osm2pgsql/master/default.style


4

Indeed this a challenge at OSM, as this tagging schema develops step by step. For example before there were aerial imagery available, users usually just added nodes for restaurants, as there were no building outlines available. But in present a lot of people to 'armchair mapping' and so we get more and more buildings, and you might need to consider watching ...


4

I had the same problem, and here is how I solved it: The problem lies in PolygonBuilder.cpp within the libgeos library. The line causing the problem has actually been fixed already, but the fix is not in the current ubuntu/debian repository that is installed via apt-get. Details of the change can be found here. I downloaded the libgeos from github and ...


4

With osm2pgsql, you have to edit the default.style file. it is a simple text file, so any text editor will do. For including lanes, add a line node,way lane text linear you have to re-import your data afterwards. If you have installed the hstore, you could extract the tags from there, but that's a bit more difficult.


Only top voted, non community-wiki answers of a minimum length are eligible