[postgis-users] Severe shapefile upload issues

[Paragon Corporation]

It sounds like you might have a mix of postgis installs.  Did you upgrade
from a prior version and how did you do it?

What does 

SELECT postgis_full_verion();  

return.

I just moved my postgis extension from public to postgis schema.  Added
postgis to my search path and then imported a shape file with the gui loader
and had no issues.

Thanks,
Regina
http://www.postgis.us


 

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of THX1138
> Sent: Thursday, April 05, 2012 6:06 PM
> To: postgis-users at postgis.refractions.net
> Subject: [postgis-users] Severe shapefile upload issues
> 
> 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('0
106000020E50800000100000001030000000100000005000000E03113F38C20474188DA1ADB9
142634140726A6B94204741B07B4EE669426341007FCF91FE1F4741D0BE4D2668426341A03E>
7819F71F474190631C1B90426341E03113F38C20474188DA1ADB91426341',
> 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'::varcha
> r,'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
>