[postgis-users] Severe shapefile upload issues

[THX1138]

I am working with postgis 2.0.0 and have found several issues when trying to
upload shapefiles into a postgis database.

The most pressing issue I have found is that when setting up the postgis
extension in a schema other than public, AddGeometryColumn() completely
fails to do anything. 

For example I used shp2pgsql to try to test upload a shapefile using the
following command:

shp2pgsql -c -s 2277:4326  -i -I "C:\tcad2\20120301_TCAD SHAPEFILES\MUD.shp"
postgis.mudtest | psql ...options

which creates the following sql:

SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
CREATE TABLE "postgis"."mudtest" (gid serial,
"objectid" int4,
"tcmud_name" varchar(50),
"shape_area" numeric,
"shape_len" numeric);
ALTER TABLE "postgis"."mudtest" ADD PRIMARY KEY (gid);
SELECT
AddGeometryColumn('postgis','mudtest','geom','4326','MULTIPOLYGON',2);
INSERT INTO "postgis"."mudtest"
("objectid","tcmud_name","shape_area","shape_len",geom) VALUES
('14','LAKEWAY
MUD','9.60083055833e+004','1.24005179036e+003',ST_Transform('0106000020E50800000100000001030000000100000005000000E03113F38C20474188DA1ADB9142634140726A6B94204741B07B4EE669426341007FCF91FE1F4741D0BE4D2668426341A03E7819F71F474190631C1B90426341E03113F38C20474188DA1ADB91426341',
4326));
CREATE INDEX "mudtest_geom_gist" ON "postgis"."mudtest" USING GIST ("geom");
COMMIT;

When AddGeometryColumn is run, I receive the following error:

ERROR:  function addgeometrycolumn(unknown, character varying, character
varying, character varying, integer, character varying, integer, boolean)
does not exist
LINE 1: SELECT AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7)
               ^
HINT:  No function matches the given name and argument types. You might need
to add explicit type casts.
QUERY:  SELECT AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7)
CONTEXT:  PL/pgSQL function "addgeometrycolumn" line 5 at SQL statement

********** Error **********

ERROR: function addgeometrycolumn(unknown, character varying, character
varying, character varying, integer, character varying, integer, boolean)
does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need
to add explicit type casts.
Context: PL/pgSQL function "addgeometrycolumn" line 5 at SQL statement

I verified that the function does in fact exist within the postgis schema
and then even tried explicitly casting the arguments in the following way so
that they would match exactly:

SELECT
postgis.AddGeometryColumn('postgis'::varchar,'mudtest'::varchar,'geom'::varchar,4326,'MULTIPOLYGON'::varchar,2
true);

but I had no luck, and when I tested in another database where the postgis
extension is installed in public it worked without complaint.

I created the postgis extensions in my first database as follows:

create schema postgis;
create extension postgis with schema postgis;
GRANT ALL ON SCHEMA postgis TO postgres;
GRANT ALL ON SCHEMA postgis TO public;
set search_path to postgis, "$user",public

I figured this would pretty much take care of everything that could possibly
cause any problemes, but I horribly mistaken in that regard. 

-----------------------------------------------------------------------------------------------------

The second issue comes with the srid conversion itself --even in cases when
I import to a database where the postgis extension is in the public schema. 

I receive the following error:

ERROR:  function st_transform(unknown, integer) is not unique
LINE 11: ...AY MUD','9.60083055833e+004','1.24005179036e+003',ST_Transfo...
                                                              ^
HINT:  Could not choose a best candidate function. You might need to add
explicit type casts.

********** Error **********

ERROR: function st_transform(unknown, integer) is not unique
SQL state: 42725
Hint: Could not choose a best candidate function. You might need to add
explicit type casts.
Character: 495

In this case it appears that the geometry field is simply not being
recognized as such and thus confusing the function.

Does anyone know why these issues are occurring and how I might fix them?

 I realize I could just programatically edit the sql to bypass using
AddGeometryColumn and then add  in 
::geometry(MULTIPOLYGON,srid) after the geometry string and then upload the
edited sql into the database but I am hoping there is a better . 

If anyone has a script that does this already (in a fast efficient
manner)please let me know! Otherwise, I will just write one in python. 

As a side note, I noticed that the gui shapefile uploader no longer has the
srid convert option, so I am wondering if this feature has been deprecated
since it does not work and the documentation has just not been updated to
reflect this change. 

I will also note that if I upload a shapefile into a database with the
postgis extension set to public and then alter the extenion to postgis, most
of the postgis functions seem to work fine. 

Thanks!

THX1138








--
View this message in context: http://postgis.17.n6.nabble.com/Severe-shapefile-upload-issues-tp4690980p4690980.html
Sent from the PostGIS - User mailing list archive at Nabble.com.