[postgis-users] Severe shapefile upload issues
Paragon Corporation
lr at pcorp.us
Thu Apr 5 16:02:36 PDT 2012
He has that it seems in fact he has it in the first slot since all his test
tables are being created in postgis. It should be at the end.
The AddGeometryColumn error and non-unique would happen if you have some
obsolete functions in your install. All these would be in public if they
are from an older postgis install.
What you can do THX -- I'm pretty sure your install is dirty. Is run the
drop function statements that are in the postgis_upgrade_minor.sql.
Make sure to set your search_path to public so it only removes the old ones
before you run the drop statements.
You'll see the drops at the end of the script: postgis_upgrade_minor.sql
That will get rid of the functions you have that shouldn't be there.
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On
> Behalf Of Bborie Park
> Sent: Thursday, April 05, 2012 6:47 PM
> To: postgis-users at postgis.refractions.net
> Subject: Re: [postgis-users] Severe shapefile upload issues
>
> 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('010600002
> >
> 0E50800000100000001030000000100000005000000E03113F38C20474188DA1ADB914
> >
> 2634140726A6B94204741B07B4EE669426341007FCF91FE1F4741D0BE4D2668426341A
> > 03E7819F71F474190631C1B90426341E03113F38C20474188DA1ADB91426341',
> > 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-tp46909
> > 80p4690980.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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
More information about the postgis-users
mailing list