[postgis-users] Severe shapefile upload issues

[Bborie Park]

You'll want to make sure that the postgis schema is in the user 
account's search_path.

-bborie

On 04/05/2012 03:05 PM, THX1138 wrote:
> 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.
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkpark at ucdavis.edu