Vjylku

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)

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([...])

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 

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

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=