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@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users