1

Once again I try to import a shapefile to my remote PostGIS database using og2ogr. Last time it was an Odyssey but I have managed to solve all the issues and report them one by one here.

I have checked everything:

  • Permissions of files,
  • Path of shapefiles,
  • Validity of shapefile,
  • triple checked my ogr2ogr command,
  • used ogrinfo to validate my connection to the DB and check the shapefile,
  • granted roles on my user and on the required tables (e.g. spatial_ref_sys)
  • if the table already exists
  • etc.

This is my ogr2ogr command:

ogr2ogr -f "PostgreSQL" PG:"host=localhost user=my_user password=my_pass dbname=my_db_name" -nlt GEOMETRY my_shape_file.shp

And the error I get (also checked the PostgreSQL log files) is:

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

Last time I got this error cause I haven't GRANTED permissions to my user. That have solved the problem:

GRANT ALL ON TABLE spatial_ref_sys TO my_user_name;

But not this time.

And that's what I see on my log file:

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('public','wld_bnd_adm2_gaul_2015','wkb_geometry',4326,'GEOMETRY',2)
7
  • If you need a quick solution, you could try to load it via the QGIS DB Manager plugin... – DPSSpatial_BoycottingGISSE Jun 16 '16 at 15:18
  • Are you certain that your postgres database has the postgis extension loaded? Also, if you use pgAdmin, you could try using the PostGIS Shapefile and DBF loader that comes with that software – Matt Jun 16 '16 at 15:20
  • Tried with shp2pgsql plugin. It didnt work either. – user1919 Jun 16 '16 at 15:31
  • Can you upload your shapefile somewhere so I can give it a go? – Matt Jun 16 '16 at 15:39
  • -nlt GEOMETRY I don't think GEOMETRY is a valid parameter here. I think you need to pass in the geometry type, like POLYGON, LINESTRING, etc. – Mintx Jun 16 '16 at 16:05
1

I figured this out. It seems like there were some artifact functions with the name addGeometryColumn (overloading in postgres is possible).

This error in the logfile made me suspect there is something very wrong with the addGeometryColumn function:

ERROR:  function addgeometrycolumn(unknown, unknown, unknown, integer, unknown, integer) is not unique at character 8

After droping the functions (DROP FUNCTION ..) the issue was resolved!

1
  • 1
    Interesting. So you dropped an overloaded function for which you had duplicate names, but different argument signatures? ..I'm curious as to how they got there in the first place! :) – elrobis Jun 17 '16 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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