osm2pgsql: Function AddGeometryColumn doesn't exist

Question

I want to import OSM file into my PostgreSQL database (Windows, Postgres Version 9.2) using the tool Osm2pgsql.

When I run following command

osm2pgsql.exe --create -d mydb artyom.xml -U myuser -W --style default.style

I get the error

SELECT AddGeometryColumn('planet_osm_point', 'way', 900913, 'POINT', 2 );
 failed: FEHLER:  Funktion addgeometrycolumn(unknown, unknown, integer, unknown,
 integer) existiert nicht
LINE 1: SELECT AddGeometryColumn('planet_osm_point', 'way', 900913, ...
               ^
HINT:  Keine Funktion stimmt mit dem angegebenen Namen und den Argumenttypen ├╝b
erein. Sie m├╝ssen m├Âglicherweise ausdr├╝ckliche Typumwandlungen hinzuf├╝gen.

Error occurred, cleaning up

Translation from German:

SELECT AddGeometryColumn('planet_osm_point', 'way', 900913, 'POINT', 2 );
 failed: ERROR:  Function addgeometrycolumn(unknown, unknown, integer, unknown,
 integer) doesn't exist
LINE 1: SELECT AddGeometryColumn('planet_osm_point', 'way', 900913, ...
               ^
HINT: No function matches the specified name and argument types. Maybe you need
to make explicit casts.

Error occurred, cleaning up

How can I fix this?

There is a PostGIS folder in the menu "Start". Do I need to explicitly install PostGIS for a particular database? — Glory to Russia, Oct 30 '12 at 05:52

Craig Ringer · Accepted Answer · 2012-10-31T04:34:44.807

26

It looks like you haven't added PostGIS support to the database you're trying to use osm2pgsql.exe on. See the PostGIS installation documentation (2.0).

Since you are using PostGIS 2.0, you should be able to just CREATE EXTENSION postgis; to load PostGIS. This command must be run as a superuser - normally the user postgres. Use:

psql -U postgres mydbname

to connect as user postgres.

It appears that at least the Windows builds of osm2pgsql don't support PostGIS 2.0 - or didn't about six months ago, anyway. See this issue report on the OSM GitHub, and the instructions on how to set a PostGIS 2 database to be compatible with an osm2pgsql that expects PostGIS 1.x. Future readers should check that these steps are still actually required before proceeding; it's likely that osm2pgsql for Windows will be updated to support PostGIS 2 at some point.

edited Oct 31 '12 at 04:34

answered Oct 30 '12 at 05:54

Craig Ringer

307,061
76
688
778

When I run `"C:\Program Files\Post greSQL\9.2\bin\psql" -d ccp-web -f postgis.sql --username=ccp-web-user` in a command line window, I get lots of errors like `psql:postgis.sql:6054: ERROR: Current Transaction was canceled, remaining commands will be ignored`. How can I fix them? – Glory to Russia Oct 30 '12 at 19:13
@DmitriPisarenko Do you have a `template_postgis` database? Create the database from that template instead. If you don't want to do that, it's hard to say from the errors given what's wrong. You need to look at the *first* error. Try running the psql command with the additional argument `-v ON_ERROR_STOP=1`. – Craig Ringer Oct 30 '12 at 23:26
I've created a database from template `template_postgis_20`. When I run the command with `-v ON_ERROR_STOP=1` I get the error `psql:C:/Program Files/PostgreSQL/9.2/share/contrib/postgis-2.0/postgis.sql:47: ERROR: no permission for laguage c`. I suppose it is this command that fails: `CREATE OR REPLACE FUNCTION spheroid_in(cstring) RETURNS spheroid AS '$libdir/postgis-2.0','ellipsoid_in' LANGUAGE 'c' IMMUTABLE STRICT;` – Glory to Russia Oct 31 '12 at 03:32
@DmitriPisarenko OK, you're using PostGIS 2.0 on PostgreSQL 9.2, so you can just `CREATE EXTENSION postgis;` in your database. You must run this command as a PostgreSQL superuser (same as when loading the PostGIS SQL directly as previously shown) because you're loading new C functions into the system. The default superuser is the user `postgres` so connect to the database as the user `postgres` with the `-U postgres` argument to `psql`. Answer updated. – Craig Ringer Oct 31 '12 at 04:17
I've done that. When I execute `osm2pgsql.exe --create -d ccp-gis-test artyom.xml -U postgres -W --style default.style` I get several new errors: 1) Table `planet_osm_point` doesn't exist. 2) Table `planet_osm_point_tmp` doesn't exist. 3) Function `astext(geometry)` doesn't exist. – Glory to Russia Oct 31 '12 at 04:27
@DmitriPisarenko Are you sure OpenStreetMap supports PostGIS 2? A quick Google search suggests you're trying to use PostGIS 1.x SQL, and that at least until recently osm2pgsql for PostGIS 2 didn't support Windows. See https://github.com/hotosm/installer/issues/1 and https://github.com/springmeyer/win-osm-workshop/blob/master/Tutorial.md#step-6-configure-the-osm-postgis-database for instructions on how to install the compatibility layer. This is now an entirely new issue so I'm done here. – Craig Ringer Oct 31 '12 at 04:31
If you wish, you can help me with this issue in scope of a paid project - http://www.vworker.com/RentACoder/misc/BidRequests/ShowBidRequest.asp?lngBidRequestId=1995124 – Glory to Russia Nov 02 '12 at 04:32
@DmitriPisarenko Good idea, but I'm not who you want; I know Pg, but not the GIS/OSM side. – Craig Ringer Nov 02 '12 at 07:05

score 3 · Answer 2 · edited Sep 22 '16 at 15:49

Rather late but I stumbled and tripped upon this Sept '16. The SQL line:

SELECT AddGeometryColumn('planet_osm_point', 'way', 900913, 'POINT', 2 );

needs to be rewritten as this function signature:

('catalog','schema','table','column',srid,'type',type_mod,boolean);

White space is immaterial. So something like the following should do the trick:

SELECT AddGeometryColumn('','','planet_osm_point', 'way', 900913, 'POINT', 2,true );

Check one of the actual INSERT statements for the correct column name which in my version is 'geom'.

Ensure that varchars are quoted, integers and boolean are unquoted and of course that the right values are in the places.

Good luck.

osm2pgsql: Function AddGeometryColumn doesn't exist

2 Answers2