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, e.g.,
WITH unique_geoms (id, geom) AS (
row_number() OVER (...
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.
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.
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.
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 ...
The osm2pgsql documentation suggests osm2pgsql -c -d gis --slim -C <cache size> --flat-nodes <flat nodes> planet-latest.osm.pbf.
<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 ...
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 ...
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, ...
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.
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.
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 ...
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 ...
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 ...
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 ...
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(
You simply need to divide the values by ten million.
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 ...
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 ...
You can find the source code of osm2pgsql here:
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");
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
The short answer is, there's no outstanding way.
Options that won't work well
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 ...
In QGIS 3.4, you can use the htsore_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 "explode hstore field" which will create one field for each keys in the hstore field:
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.
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
There’s a thorough explanation in the PostGIS FAQ:
In PostGIS 2, the default geometry operator class gist_geometry_ops was changed to gist_geometry_ops_2d and the gist_geometry_ops was completely removed.
Simply remove the gist_geometry_ops statement, changing this:
CREATE INDEX idx_my_table_geom ON my_table USING gist(geom gist_geometry_ops);
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 ...
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 ...
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.
no you dont need a server.
you can install postgis on your computer and use osm2pgsql.
i recommend using tilemill
to create the tiles from your postgis db and use
to style them.
you can then export them from tilemill in number of formats (i use mbtiles - you can open it to see the actual images if you need via mbutils)
i dont know about a ...