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 ...
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 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 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 ...
planet_osm_line: contains all imported ways
planet_osm_point: contains all imported nodes with tags
planet_osm_polygon: contains all imported polygons. Relations seem to be resolved for that.
planet_osm_roads: contains a subset of planet_osm_line suitable for rendering at low zoom levels. planet_osm_line contains too many
elements to render on ...
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, ...
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 ...
What are you intending to do with the OSM data once in a postgis table ?
Depending on what you intend on doing with the data (Are you looking to do spatial analysis of highways ? Make a map of a complete city including restaurants and other POIs (points of interests) ? ), your osm2pgsql style files will be a little different.
First, osm2pgsql's ...
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.
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 ...
I think you must (in addition to other scripts you've run) also run legacy.sql file which you can find next to postgis.sql and spatial_ref_sys.sql on whatever platform you are. This will add function aliases for old names because in Postgis 2.0, all functions have a 'ST_' prefix.. so AsText is now named ST_AsTexT().
Hope this helps!
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 ...
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 ...
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.
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.
This will likely require significant amount of manually work to detect and remove the duplicated data. When you're detecting and resolving the duplicate data; you'll want both sources to be in the same geo format: shapefile, PostGIS DBs, or as OSM data.
The following workflow is based on having both sources of data as OSM before ...
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");
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 ...
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 ...
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 ...
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 ...