Questions Tagged With postgreshttp://help.openstreetmap.org/tags/postgres/?type=rssquestions tagged <span class="tag">postgres</span>enMon, 27 Oct 2014 00:19:07 +0000Are there any precanned OSM server setups for Windows that contain the database, data loader and anything elsehttp://help.openstreetmap.org/questions/37982/are-there-any-precanned-osm-server-setups-for-windows-that-contain-the-database-data-loader-and-anything-else<p>Hello,</p>
<p>Are there any precanned OSM server setups that contain the database, data loader and anything else you need to get an open streetmap database up quickly? Im going to use the database to mainly generate tiles, but I also want to use the data for analysis if need be.</p>
<p>Ive searched the internets, but alas I failed.</p>
<p>Hopefully someone can help me out.</p>
<p>If there are no packages, I would like just a good set of nonlinux set of instructions.</p>
<p>thanks</p>ajc2014Mon, 27 Oct 2014 00:19:07 +0000http://help.openstreetmap.org/questions/37982/are-there-any-precanned-osm-server-setups-for-windows-that-contain-the-database-data-loader-and-anything-elsewindowspostgrespackageCommand for importing diffs with osm2pgsqlhttp://help.openstreetmap.org/questions/30090/command-for-importing-diffs-with-osm2pgsql<p>Does anyone have a sample/tutorial command call on how I can import diffs from planet osm using osm2pgsql?</p>
<p>I know you use the osm2pgsql -c --slim ...other stuff to import osm data to the database initially.<br>
Do you use the -a (append) to import the diffs?<br>
</p>
<p>The help documents are rather sparse on this topic.</p>
<p>Thank you</p>
<p>A</p>Fisherman12iWed, 22 Jan 2014 15:06:42 +0000http://help.openstreetmap.org/questions/30090/command-for-importing-diffs-with-osm2pgsqlpostgresqlosmosm2pgsqlpostgresSetting up OSM Windows Server 2008 R2 (OS)http://help.openstreetmap.org/questions/30076/setting-up-osm-windows-server-2008-r2-os<p>Hello List,</p>
<p>I have installed PostGRES 9.2 and PostGIS on windows server OS and I am having issues using the import tools to import OSM data to PostGRES.<br>
</p>
<p>Just to start, I do not have an option of running any flavor of linux at all, virtual or not.<br>
</p>
<p>I wanted to use osm2pgsql to import the pbf files of the whole planet into the PostGRES database, but the program keeps crashing in slim mode '--slim'.</p>
<p>When I switch to osmosis, I just can't get it to setup correctly at all. When I call the program from the .bat file, it say C:\program isn't a valid path, which it isn't. So I moved the program to the root and placed it in c:\osmosis\bin. I still cannot get the osmosis to work.</p>
<p>Are there database tuning options I need to set in PostGRES?<br>
Are there additional commands I need to set on the import of osm2pgsql to help with the program crashing?
Is there a setup guide for windows for osmosis?</p>
<p>Thank you</p>
<p>A</p>Fisherman12iWed, 22 Jan 2014 13:31:34 +0000http://help.openstreetmap.org/questions/30076/setting-up-osm-windows-server-2008-r2-ososmpostgresosmosisosm2pgsqlFull history dump database WITH timestamphttp://help.openstreetmap.org/questions/24562/full-history-dump-database-with-timestamp<p>Hello everyone,</p>
<p>I'm currently trying to perform a historical analysis on OSM data. At the moment I have extracted my study areas from the full history dump using the bounding box command in Osmosis. Second to this, I have used osm2pgsql to input my data into a postgresql database (using the Postgres app). However, I can find no option that would allow me to enter the timestamp as well. Supposedly, there was once a command "--extra-attributes" that would allow user_id and timestamp (and a few other variables) to be input into the database along with the remaining data. However, this no longer works.</p>
<p>Is there ANY way I can get my timestamps into my database? Either with Osmosis, Osm2pgsql or another software I am unfamiliar with?</p>
<p>All the best,</p>
<p>S </p>SophieKSThu, 25 Jul 2013 10:27:38 +0100http://help.openstreetmap.org/questions/24562/full-history-dump-database-with-timestamptimestampspostgresdatabasemapnik-german osm style, problem with views in postgres databasehttp://help.openstreetmap.org/questions/18752/mapnik-german-osm-style-problem-with-views-in-postgres-database<p>Hi, I am trying to get the german mapnik style to work on Ubuntu 12.04 as proposed by the <a href="http://svn.openstreetmap.org/applications/rendering/mapnik-german/">SVN</a> README:</p>
<pre><code>&gt; To deploy this style use the supplied default.style file for
&gt; osm2pgsql and import data using the hstore extension of
&gt; PostgreSQL (--hstore --hstore-match-only switches of osm2pgsql).
&gt;
&gt; To keep the database compatible to the "original" Mapnik style we
&gt; now use views instead of tables. SQL scripts to set up these views
&gt; are supplied in the views directory.
</code></pre>
<p>I create my database using sachsen.osm from geofabrik and osm2pgsql from the ppa:kakrueger/openstreetmap (osm2pgsql SVN version 0.81.0 (64bit id space))</p>
<pre><code>osm2pgsql -m -d osm --hstore -W -S /path/to/mapnik-german/views/default.style mapnik/sachsen.osm
</code></pre>
<p>Then I create the views that are supposed to be used by the germany mapnik style:</p>
<pre><code>sudo -u postgres psql osm &lt; mapnik-german/views/view-line.sql
sudo -u postgres psql osm &lt; mapnik-german/views/view-point.sql
sudo -u postgres psql osm &lt; mapnik-german/views/view-polygon.sql
sudo -u postgres psql osm &lt; mapnik-german/views/view-roads.sql
</code></pre>
<p>Then I create a new directory "mapnik-de" containing the <a href="http://svn.openstreetmap.org/applications/rendering/mapnik/">mapnik-repo</a>. I copy <a href="http://svn.openstreetmap.org/applications/rendering/mapnik-german/">mapnik-german</a> ontop of that (is that the way to go?) and copy all the template files to inc files in mapnik-de/inc-de/.
I use mapnik-de as working directory.</p>
<p>Now, when I try to generate my xml file</p>
<pre><code>./generate_xml.py --dbname osm --host 'localhost' --user USERNAME --port 5432 --password 'PASSWORD' --inc inc-de osm-de.xml &gt; osm-local.xml
</code></pre>
<p>I get the following error:</p>
<pre><code>/usr/lib/pymodules/python2.7/mapnik2/__init__.py:27: DeprecationWarning: mapnik2 module has been deprecated,
please use 'import mapnik'
warnings.warn(msg, DeprecationWarning)
Traceback (most recent call last):
File "./generate_xml.py", line 204, in &lt;module&gt;
serialize(template_xml,options)
File "./generate_xml.py", line 80, in serialize
mapnik.load_map(m,xml,True)
RuntimeError: Postgis Plugin: PSQL error:
ERROR: column "wood" does not exist
LINE 1: ...enity,landuse,leisure,man_made,military,"natural",wood,power...
^
Full sql was: 'SELECT * FROM (select way,aeroway,amenity,landuse,leisure,man_made,military,"natural",wood,power,tourism,coalesce("name:de",name) as name,highway,
case when religion in ('christian','jewish') then religion else 'INT-generic'::text end as religion
from planet_osm_polygon
where landuse is not null
or leisure is not null
or aeroway in ('apron','aerodrome')
or amenity in ('parking','university','college','school','hospital','kindergarten','grave_yard','prison')
or military in ('barracks','danger_area')
or "natural" in ('field','beach','desert','heath','mud','grassland','wood','sand','scrub')
or power in ('station','sub_station','generator')
or tourism in ('attraction','camp_site','caravan_site','picnic_site','zoo')
or highway in ('services','rest_area')
order by z_order,way_area desc
) as leisure LIMIT 0'
encountered during parsing of layer 'landcover' in Layer at line 381 of 'osm-de.xml'
</code></pre>
<p>I am using mapnik 2.1.0 from the ppa:mapnik/v2.1.0</p>
<p>To me it looks like the generated views are not correctly accessed. When I use a custom default.style file with osm2pgsql that contains "wood" and a couple more, then this error does not show and I can create tiles.</p>
<p>I am not used to postgresql or postgis. Maybe I just understood something wrong? I took a look with pgadmin3 and there are the four views and except view_osmde_roads they all contain the column "wood".</p>skaiwalkerSat, 29 Dec 2012 14:24:23 +0000http://help.openstreetmap.org/questions/18752/mapnik-german-osm-style-problem-with-views-in-postgres-databasestylemapnik-germanmapnikpostgispostgresOSM/Nominatim on Postgres 9.2?http://help.openstreetmap.org/questions/16552/osmnominatim-on-postgres-92<p>I am trying to use Nominatim's setup tool to populate an OSM database. On Postgres 9.0/PostGIS 1.5 this works fine. I am now attempting to perform this load on Postgres 9.2/PostGIS 2.0. I needed to modify some of the plpgsql functions for compatibility regarding hstore. Once this was done, the load starts fine but after some time Postgres runs out of memory. I tried reducing the memory usage settings in Postgres, and also tried running Nominatim with limited settings, such as disabling token precalc and limiting the number of threads to one, but Postgres will still eventually run out of memory and the load fails. Has anyone had success loading OSM into Postgres 9.2 or is this not recommended?</p>bryanckSun, 30 Sep 2012 14:59:17 +0100http://help.openstreetmap.org/questions/16552/osmnominatim-on-postgres-92nominatimpostgres9.2Postgres DB SQL calls resulting in inconsistent resultshttp://help.openstreetmap.org/questions/15633/postgres-db-sql-calls-resulting-in-inconsistent-results<p>Hello OSM,</p>
<p>As mentioned <a href="http://help.openstreetmap.org/questions/15496/sql-statement-to-get-all-of-the-xy-pairs-from-a-way">here</a>, I have an extract of a recent planet file created with Osmosis (with the command line arguement that keeps ways even if they extend outside the cut polygon). I dumped the extract into a Postgres database using the snapshot schema. I then have a Java program that calls the following two SQL statements in two nested loops to draw the data.</p>
<pre><code>OUTER LOOP BEGIN
"SELECT nodes, id from public.ways LIMIT 1 OFFSET" + val
INNER LOOP BEGIN
"SELECT ST_X(geom) as x, ST_Y(geom) as y from public.nodes WHERE id=" + nodes[i] + "LIMIT 1"
INNER LOOP END
val++
OUTER LOOP END
</code></pre>
<p>When I run this, if a node belonging to a given way is not found, my program fires off a message to the log and keeps chugging. I have noticed that these logs are showing up in a VERY inconsistent fashion. To demonstrate this, I took the outer loop of my program and clamped it to only loop 1000 times (IE draw 1000 ways) and ran the program twice, which should yield the same results. This is what I got...</p>
<p><img alt="alt text" src="http://help.openstreetmap.org/upfiles/osmwtf.jpg"></p>
<p>Now please understand that I already know that:</p>
<p>A. Planet files dumps take hours so SOME nodes will be missing unless you grab a daily build and use Osmosis to update your planet file...</p>
<p>B. My extract included ways that crossed my cut polygon, for my needs I require the entire road system to be intact.</p>
<p>But this is different, this is one isolated database and one program asking it for the same exact data, and I'm getting inconsistencies between runs which is driving me mad! I don't understand it at all. Do I need to order this data by the id in order to get consistent results? Is this a Postgres "thing" or should I be looking for some kind of illusive bug in my program (which is pretty strait-forward)? Any help would be vastly appreciated.</p>
<p>Thanks, -Cody</p>SmartkidWed, 29 Aug 2012 10:18:31 +0100http://help.openstreetmap.org/questions/15633/postgres-db-sql-calls-resulting-in-inconsistent-resultspostgresosmosissqlPermission issue of .so file in Nominatim osm postgres DBhttp://help.openstreetmap.org/questions/13825/permission-issue-of-so-file-in-nominatim-osm-postgres-db<p>I am following the steps as described in <a href="http://wiki.openstreetmap.org/wiki/Nominatim/Installation">http://wiki.openstreetmap.org/wiki/Nominatim/Installation</a> to install the nominatim osm db on my own server. I need to create some functions before doing </p>
<p><code>./utils/setup.php &lt;my-planet-file&gt; --load-data</code> </p>
<p>So to create those functions I am running this command:</p>
<pre><code>./utils/setup.php --create-functions
</code></pre>
<p>But, it gives me following error:</p>
<pre><code>Error in query: ERROR: could not access file "/home/nominati/public_html/Nominatim/module/nominatim.so": Permission denied
</code></pre>
<p>As I can see error occurs while running the following query:</p>
<pre><code>CREATE OR REPLACE FUNCTION transliteration(text) RETURNS text AS '/home/nominati/public_html/Nominatim/module/nominatim.so', 'transliteration' LANGUAGE c IMMUTABLE STRICT;
</code></pre>
<p>I am running the commands with root user and nominatim.so file owner is also root. I already changed the file owner to postgres and tried but the same error is there.
Please guide what to do?</p>Ravi KotwaniWed, 27 Jun 2012 07:24:42 +0100http://help.openstreetmap.org/questions/13825/permission-issue-of-so-file-in-nominatim-osm-postgres-dbfunctionnominatimosmpostgresImport error with Nominatim installation: No Data and DB Error: insufficient permissionshttp://help.openstreetmap.org/questions/12373/import-error-with-nominatim-installation-no-data-and-db-error-insufficient-permissions<p>I'm on Debian Squeeze and following <a href="http://wiki.openstreetmap.org/wiki/Nominatim/Installation">http://wiki.openstreetmap.org/wiki/Nominatim/Installation</a> . I'm at the point where I import the OSM file ( <a href="http://wiki.openstreetmap.org/wiki/Nominatim/Installation#Import_and_index_OSM_data">http://wiki.openstreetmap.org/wiki/Nominatim/Installation#Import_and_index_OSM_data</a> ):</p>
<p><code>time php -d error_reporting=E_ALL ./utils/setup.php --osm-file austria.osm --all</code></p>
<p>I'm using a specific country as opposed to the whole planet and the setup runs for about 13 minutes and ends with:</p>
<pre><code>INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
COMMIT
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
Import
Using projection SRS 4326 (Latlong)
NOTICE: table "place" does not exist, skipping
NOTICE: type "keyvalue" does not exist, skipping
NOTICE: type "wordscore" does not exist, skipping
NOTICE: type "stringlanguagetype" does not exist, skipping
NOTICE: type "keyvaluetype" does not exist, skipping
NOTICE: function get_connected_ways(pg_catalog.int4[]) does not exist, skipping
Allocating memory for dense node cache
Allocating dense node cache in one big chunk
Allocating memory for sparse node cache
Sharing dense sparse
Node-cache: cache=15000MB, maxblocks=1920001*8192, allocation method=11
Mid: pgsql, scale=10000000 cache=15000
Setting up table: planet_osm_nodes
NOTICE: table "planet_osm_nodes" does not exist, skipping
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "planet_osm_nodes_pkey" for table "planet_osm_nodes"
Setting up table: planet_osm_ways
NOTICE: table "planet_osm_ways" does not exist, skipping
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "planet_osm_ways_pkey" for table "planet_osm_ways"
Setting up table: planet_osm_rels
NOTICE: table "planet_osm_rels" does not exist, skipping
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "planet_osm_rels_pkey" for table "planet_osm_rels"
Reading in file: austria.osm
Processing: Node(16412k 72.6k/s) Way(1550k 8.12k/s) Relation(28484 1017.29/s) parse time: 445s
Node stats: total(16412032), max(1543457158) in 226s
Way stats: total(1550680), max(140997267) in 191s
Relation stats: total(28484), max(1907412) in 28s
node cache: stored: 16412032(100.00%), storage efficiency: 67.22% (dense blocks: 1301, sparse nodes: 15387701), hit rate: 97.81%
Stopping table: planet_osm_nodes
Stopping table: planet_osm_rels
Building index on table: planet_osm_rels (fastupdate=off)
Stopping table: planet_osm_ways
Building index on table: planet_osm_ways (fastupdate=off)
Stopped table: planet_osm_nodes in 0s
Stopped table: planet_osm_rels in 0s
Stopped table: planet_osm_ways in 355s
Osm2pgsql took 800s overall
osm2pgsql SVN version 0.80.0 (32bit id space)
ERROR: No Data
No Data
real 13m37.663s
user 4m18.800s
sys 0m7.800s
</code></pre>
<p>I grepped the source for <em>No Data</em> and found this in <code>util/setup.php</code>:</p>
<pre><code>123 $oDB =&amp; getDB();
124 $x = $oDB-&gt;getRow('select * from place limit 1');
125 if (!$x || PEAR::isError($x)) fail('No Data');
</code></pre>
<p>But what puzzles me is that I see there <strong>is</strong> actually data there:</p>
<pre><code>$ psql nominatim
psql (8.4.11)
Type "help" for help.
nominatim=# select count(*) from place;
count
---------
1788758
(1 row)
</code></pre>
<p>I re-ran the command with additional debug info in the <code>setup.php</code> script to echo the PEAR error, in case it is one, and got this:</p>
<pre><code>Message: DB Error: insufficient permissions
ERROR: No Data
No Data
</code></pre>
<p>I then discovered that the table <code>place</code> has been created with the owner <code>postgres</code>; this is the use under which I executed the whole setup utility and which has local ident rights to do everything (default setup).</p>
<p>I did this because the guide mentions:</p>
<blockquote>
<p>You also need a user with superuser rights for the account that is doing the import. You must not run the import as user www-data.</p>
</blockquote>
<p>However it seems the import script is using different users when doing the stuff and I'm not sure how to handle this. I set proper credentials in <code>settings/local.php</code>, but it seems the actual import with <code>osm2pgsql</code> is done with my CLI user <code>postgres</code> and when the script tries to access the database it is my configured username (which is just <code>nominatim</code> per the examples; this is only a postgresql user, not a system account).</p>
<p>What is the proper procedure for the import?</p>
<p>thanks</p>MarkusThu, 26 Apr 2012 12:35:39 +0100http://help.openstreetmap.org/questions/12373/import-error-with-nominatim-installation-no-data-and-db-error-insufficient-permissionsnominatiminstallationpostgresSetting PostGIS as a datasourcehttp://help.openstreetmap.org/questions/10677/setting-postgis-as-a-datasource<p>Hi there,
I am following tutorials to learn about OSM and how to make some maps.
Currently I am using this one, <a href="https://github.com/mapnik/mapnik/wiki/GettingStartedInPython">https://github.com/mapnik/mapnik/wiki/GettingStartedInPython</a> but in the instructions it uses shapefiles as a datasource just in a normal file directory. I don’t want to do that, I want the file source to be from my database in PostGIS, where I have stored my OSM data. What do I need to do to make it do that? I tried:</p>
<p>lyr = Layer('Geometry from PostGIS')
lyr.datasource = PostGIS(host='localhost',user='postgres',password='',dbname='your_postgis_database',table='your_table')</p>
<p>which I copied off one of the wiki pages. Obviously I amended details as appropriate, but it just said layer not recognised. I wondered if there way something I had to do or tell it to do to get into the database?</p>
<p>In the literature I have read, it says that using PostGIS is one of the most common ways of doing what I want to do, but I cannot seem to find any code to use!
Thanks in Advance,
Tracey</p>lgxtlmMon, 20 Feb 2012 13:09:40 +0000http://help.openstreetmap.org/questions/10677/setting-postgis-as-a-datasourcemapnikpostgresqlpostgrespostgisosm2pgsqlOSM – PostGIS – Mapnik problem!http://help.openstreetmap.org/questions/10671/osm-postgis-mapnik-problem<p>Hello,
I have to learn how to make maps using OSM data. The data will be in held in a PostGIS database and my role in the project is to learn how to take this data and make pretty maps using Mapnik, store the tiles and use something to make slippy maps that can be accessed by the public for free via a web server. In order to fully understand / get to grips with my end I thought I had better learn / get familiar with the whole process. To the end I have done the following:
- I have installed the OSGeo live version 5 bundle on a virtual machine on my PC. I believe this means it is running in ubuntu.
- Then downloaded United_Kingdom.shapefiles.zip, unzipped and used QGIS to make the shape file go into an sql database (PostGIS).
- I think I then access mapnik through a terminal thing a bit like when you want to run command lines through dos. There doesn’t seem to be another way that I could see? I am sorry if I sound like a bit of an idiot, but I don’t really understand what Mapnik is, I thought it was going to be something like ArcGIS or Mapinfo, both of which I’ve seen before. Anyway, I followed some instructions using something they called “python bindings” and managed to get my .shp files to make maps in the .png format. I have some instructions on how I can make that ‘executable’ – whatever that means?</p>
<p>But I need to be able to make these maps from data in the PostGIS database, and I can’t seem to figure out how to do that. I have tried some code copied off OSMwiki, but that didn’t work. Someone has suggested that Mapnik maybe doesn’t know how to read from PostGIS and told me to type ‘registered datasource : postgis’ but I got ‘registered : command not found’ and I don’t even know what that means! </p>
<p>I have been using this OSGeo thing as it came; I have not installed or uninstalled anything else. I am sorry if I am asking help on something really basic and potentially obvious, I don’t come from a computer background and I think I am out of my depth!!</p>
<p>Manythanks
TM</p>OSM-GBMon, 20 Feb 2012 11:34:06 +0000http://help.openstreetmap.org/questions/10671/osm-postgis-mapnik-problemmapnikpostgresqlpostgrespostgisosm databaseshttp://help.openstreetmap.org/questions/8108/osm-databases<p>Postgres database populated via osmosis is for mkgmap, postgres database populated via osm2pgsql is for mapnik. Is there a way to have only one database for creating tiles and also for creating gmapsupp.img?</p>gwkSat, 24 Sep 2011 06:18:53 +0100http://help.openstreetmap.org/questions/8108/osm-databasespostgresosmosisosm2pgsqlOsmosisRuntimeException: Cannot represent -1 as a char.http://help.openstreetmap.org/questions/5743/osmosisruntimeexception-cannot-represent-1-as-a-char<p>Hi, extracting a polygon from postgres via osmosis fails:</p>
<pre><code>/home/osm/osmosis -v --read-pgsql database="osm" user="osm" password="secret" validateSchemaVersion=no --dataset-bounding-box bottom=50.0 left=8.6 top=50.1 right=8.7 --write-xml db.osm
</code></pre>
<blockquote>
<p>FINE: Rolling back JDBC transaction on Connection [jdbc:<a>postgresql://localhost/osm_rn,</a> UserName=osm, PostgreSQL Native Driver]
Jun 13, 2011 10:24:13 PM org.springframework.jdbc.datasource.DataSourceTransactionManager doCleanupAfterCompletion
FINE: Releasing JDBC Connection [jdbc:<a>postgresql://localhost/osm_rn,</a> UserName=osm, PostgreSQL Native Driver] after transaction
Jun 13, 2011 10:24:13 PM org.springframework.jdbc.datasource.DataSourceUtils doReleaseConnection
FINE: Returning JDBC Connection to DataSource
Jun 13, 2011 10:24:13 PM org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager waitForCompletion
SEVERE: Thread for task 1-read-pgsql failed
org.openstreetmap.osmosis.core.OsmosisRuntimeException: Cannot represent -1 as a char.
at org.openstreetmap.osmosis.core.util.IntAsChar.intToChar(IntAsChar.java:35)</p>
</blockquote>
<p>Any hints? </p>gwkMon, 13 Jun 2011 22:27:37 +0100http://help.openstreetmap.org/questions/5743/osmosisruntimeexception-cannot-represent-1-as-a-charpostgresosmosisHow do you make the osm2pgsql diff imports run faster than molasses on postgres 8.4?http://help.openstreetmap.org/questions/1/how-do-you-make-the-osm2pgsql-diff-imports-run-faster-than-molasses-on-postgres-84<p>Using postgres 8.4 diff imports seem to run as slow as a license change. I've hear that it's better to use postgres 8.3. Is there any other way?</p>Andy AllanFri, 25 Jun 2010 16:34:10 +0100http://help.openstreetmap.org/questions/1/how-do-you-make-the-osm2pgsql-diff-imports-run-faster-than-molasses-on-postgres-84diffrenderingosm2pgsqlpostgres