5

As noted in the title, I'm getting the following error:

ERROR 1: AddGeometryColumn failed for layer pretty_polys, layer creation has
failed.
ERROR 1: Terminating translation prematurely after failed
translation of layer mybeautifulshapefile (use -skipfailures to
skip errors)

I'm just adding a shapefile to PostGIS using ogr2ogr (PG connection data is all fake placeholders, obviously):

ogr2ogr \
    -f PostgreSQL \
        PG:"host='000.00.000.00' port='5432' user='nullislandpatriot' password='nullisland4eva' dbname='data_i_like'" \
    "data/mybeautifulshapefile.shp" \
    -nln pretty_polys \
    -nlt POLYGON

I've tried to identify many possible sources for the error, but I can't seem to figure out what's wrong, and the error isn't descriptive enough.

So my question is, how can I drill down into this error to find out why AddGeometryColumn is failing?

Here are some of the things I have already tried:

  • using different files. I receive this error with any file, including the natural earth countries shapefile
  • checking the permissions in PostgreSQL. I have read and write access to the public schema that is being used. Though I should note that I am not the superuser, and we had to give explicit permission to allow me to access spatial_ref_sys, and geometry_columns.
  • editing the -nlt option to make sure I'm using the correct geometry type.
  • checking different arrangements of -a_srs, -s_srs, -t_srs, and not using them at all.
  • lots of other things before I tried the natural earth data, like PRECISION=NO, different database schemas, text encoding, ...

My assumption is that the error has something to do with permissions or settings in PostgreSQL or the syntax for the connection info, since I get the same error for well-known shapefiles like Natural Earth. I've tried the connection info withou the interior single quotes.

I also tried CPL_DEBUG=ON, which shows me a bit more of what is working:

Shape: DBF Codepage = UTF-8 for
/Users/bgolder/Downloads/ne_10m_admin_0_countries/ne_10m_admin_0_countries.shp
Shape: Treating as encoding 'UTF-8'.
OGR:
OGROpen(/Users/bgolder/Downloads/ne_10m_admin_0_countries/ne_10m_admin_0_countries.shp/0x7f9a79d00720)
succeeded as ESRI Shapefile.
PG: DBName="'data_i_like'"
PG: PostgreSQL version string : 'PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu,
compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit'
PG: PostGIS version string : '2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1'
OGR_PG_NOTICE: NOTICE:  CREATE TABLE will create implicit sequence
"countries_ogc_fid_seq" for serial column "countries.ogc_fid"

OGR_PG_NOTICE: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"countries_pkey" for table "countries"

ERROR 1: AddGeometryColumn failed for layer countries, layer creation has
failed.
ERROR 1: Terminating translation prematurely after failed
translation of layer ne_10m_admin_0_countries (use -skipfailures to skip
errors)

GDAL: In GDALDestroy - unloading GDAL shared library.

Specs:

  • PostGIS 2.0, with PostgreSQL 9.1.9 on Ubuntu 12.04
  • GDAL 1.11.0 (on OS X 10.9.3, installed via Homebrew)
6
  • Have you tried adding the data from the same computer Postgis is running on? Maybe OSX and Ubuntu don't like each other...
    – AndreJ
    Commented Jul 18, 2014 at 7:04
  • @AndreJoost we did try that, and received a different error. We are currently testing out a change in PostgreSQL permissions, which looks promising at the moment. Commented Jul 18, 2014 at 7:27
  • This is bedeviling me at the moment with the TIGER "places" shapefile. I've isolated geometry types and still have the same error. Commented Sep 11, 2014 at 18:11
  • 3
    After lots of trial and much error, we discovered that the source of the problem was a permissions issue. The user account did not have sufficient permissions to access all the schemas involved. I will still give the answer to anyone who can show a good debugging process. Commented Sep 11, 2014 at 22:06
  • 1
    By turning on the SQL statement logging in PostgreSQL you at least get all the SQL recorded so you can see what tables and objects are queried and check their permissions.
    – user30184
    Commented Sep 12, 2014 at 8:09

5 Answers 5

3

AddGeometryColumn is a PostGIS function, so the error should be happening on the database... the source code for the client is in ogr/ogrsf_frmts/pg/ogrpgdatasource.cpp and it says (v1.11):

    osCommand.Printf(
             "SELECT AddGeometryColumn(%s,%s,%s,%d,'%s',%d)",
             pszEscapedSchemaNameSingleQuote, pszEscapedTableNameSingleQuote,
             OGRPGEscapeString(hPGConn, pszGFldName).c_str(),
             nSRSId, pszGeometryType, nDimension );

    hResult = OGRPG_PQexec(hPGConn, osCommand.c_str());

    if( !hResult
        || PQresultStatus(hResult) != PGRES_TUPLES_OK )
    {
        CPLError( CE_Failure, CPLE_AppDefined,
                  "AddGeometryColumn failed for layer %s, layer creation has failed.",
                  pszLayerName );

Obviously it would have been better if it had logged the exact command run (osCommand.c_str()), but oh well, that's just the client. You can still look at the database server logs for errors, and find e.g.:

ERROR:  permission denied for relation spatial_ref_sys
STATEMENT:  INSERT INTO spatial_ref_sys (srid,srtext,proj4text) VALUES ([...])

or:

ERROR:  AddGeometryColumn() - invalid SRID
CONTEXT:  SQL statement "SELECT AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7)"
    PL/pgSQL function addgeometrycolumn(character varying,character varying,character varying,integer,character varying,integer,boolean) line 5 at SQL statement
STATEMENT:  SELECT AddGeometryColumn([...])
3

It appears to have been a different root cause (the above issue was resolved via permissions), but I ran into the same error when importing to PostGIS with ogr2ogr:

ERROR 1: Terminating translation prematurely after failed
translation of layer mybeautifulshapefile (use -skipfailures to
skip errors)

In my case it was a lingering shapefile (TIGER census places) precision bug, and the solution was fairly simple for anyone else who comes across it. Add this flag to ogr2ogr:

-lco PRECISION=NO 
3
  • What does Precision=NO do?
    – HeikkiVesanto
    Commented Feb 19, 2016 at 12:00
  • I am thoroughly ashamed to say I have no idea - I've completely forgotten. The -lco flag is for driver-specific parameters, so it's meant to modify the shapefile handling in some way. Many apologies I can't be more specific. Commented Feb 19, 2016 at 14:37
  • 1
    Precision may be "YES" to force new fields created on this layer to try and represent the width and precision information, if available using NUMERIC(width,precision) or CHAR(width) types. If "NO" then the types FLOAT8, INTEGER and VARCHAR will be used instead. The default is "YES". (gdal.org/drv_pg.html)
    – dbaston
    Commented Feb 19, 2016 at 16:16
1

hi I'd also met this issue , After investigate , It due to there is limitation of PG_extension in which 900914 is not allowed. After change to 4326 then , It work, there is no issue anymore . using below ogr command (add -t_srs EPSG:4326 parameter )

ogr2ogr -t_srs EPSG:4326 -f PostgreSQL PG:"host= 
1
  • please use rich text editor for inline command, more readable Commented Apr 18, 2019 at 4:48
0

I got the same error so I show the errorlog from postgres and got this:

ERROR: function addgeometrycolumn(unknown, unknown, unknown, integer, unknown, integer) is not unique at character 8 HINT: Could not choose a best candidate function. You might need to add explicit type casts. STATEMENT: SELECT AddGeometryColumn('bm','g_popis_tisk_b','geom',900914,'POINT',2)

So the problem was, that the function was defined more than once with different parameters but some non-compulsory. It was probably caused by importing data (and functions) from other databases with another version of postgis.

http://postgis.net/docs/AddGeometryColumn.html

0

I got this same error:

ERROR: function addgeometrycolumn(unknown, unknown, unknown, integer, unknown, integer

and turned out it was because I didn't have PostGIS installed. More info here: http://www.silota.com/docs/sql-load-data/shapefile-postgis.html

ogr2ogr to a postgres db only works if you have PostgresSQL and PostGIS installed (and a postgis table with all the extensions properly configured).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.