16

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 ...


14

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. ...


11

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.


9

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 ...


9

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 ...


8

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 ...


8

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, ...


8

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 ...


7

Magnus, 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 ...


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

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

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

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 ...


7

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.


6

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!


6

You have to set up the database in the form osm2pgsql expects it. I have written a detailed Howto here (you might use google to translate): http://wiki.openstreetmap.org/wiki/User:Ajoessen/Postgis


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

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

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

Introduction 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. Workflow The following workflow is based on having both sources of data as OSM before ...


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

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 ...


4

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


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

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

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