Severe shapefile upload issues

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Severe shapefile upload issues

Andrew Joseph
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






Reply | Threaded
Open this post in threaded view
|

Re: 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
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

--
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
[hidden email]
_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Severe shapefile upload issues

Regina Obe-2
In reply to this post by Andrew Joseph
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: [hidden email]
> [mailto:[hidden email]] On
> Behalf Of THX1138
> Sent: Thursday, April 05, 2012 6:06 PM
> To: [hidden email]
> 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
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>


_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Severe shapefile upload issues

Regina Obe-2
In reply to this post by Bborie Park
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: [hidden email]
> [mailto:[hidden email]] On
> Behalf Of Bborie Park
> Sent: Thursday, April 05, 2012 6:47 PM
> To: [hidden email]
> 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
> > [hidden email]
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
> --
> Bborie Park
> Programmer
> Center for Vectorborne Diseases
> UC Davis
> 530-752-8380
> [hidden email]
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>


_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Severe shapefile upload issues

Andrew Joseph
My postgis full version is as follows:

"POSTGIS="2.0.0 r9605" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER"

My install (on Ubuntu-64 bit) could not be dirty because I created a new virtual machine and built it from scratch.

I am using  shp2pgsql and pgadmin from a windows machine (64 bit with 64 bit postgres and 64 bit postgis) and piping to my ubuntu server.

On my windows machine I completely removed postgres and reinstalled everything so there should be no conflict their either.

I managed to solve the addgeometrycolumn problem by impementing Bborie's suggestion (thanks btw, I would have never realized that I was supposed to do that!)

I used:

create schema postgis;
create extension postgis with schema postgis;
create extension postgis_topology;
set search_path to public,postgis;
ALTER USER postgres set search_path to public,postgis;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

The srid conversion issue remains and I tested on both windows and linux and I get the same error result...

 I run:

shp2pgsql -c -s 2277:4326  -i -I "C:\tcad2\20120301_TCAD SHAPEFILES\MUD.shp" postgis.mudtest4 | psql -d testing -U postgres -h 10.0.0.000 -p 5432

and get back:

ERROR:  function st_transform(unknown, integer) is not unique
LINE 1: ...CH MUD','4.55571330968e+007','3.22484265423e+004',ST_Transfo...
                                                             ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

I don't see how this could be a version upgrade error, considering I did clean installs on both windows and ubuntu , with the linux install being done on a brand new virtual machine and the windows on being done using precompiled binaries and installed using the batch file that came with them.

Am I perhaps forgetting to specify an option or adding in one that is incompatible? I have tried multiple shapefiles and nothing works.
Reply | Threaded
Open this post in threaded view
|

Re: Severe shapefile upload issues

Regina Obe-2
Confirmed its a bug. I think it's a fairly easy one to fix so we should have
it fixed soon.

 I think it only happens if you use the new transform switch.  Can you
confirm you can import without using a transform.

I've ticketed it.  

http://trac.osgeo.org/postgis/ticket/1759

Regarding the:

 ALTER USER postgres set search_path to public,postgis;

I would highly suggest not doing that.  The user search_path overrides the
database search_path
and it will make things hard to debug since your postgres user then may
behave differently from other users.  Since you have your db search path set
right, you shouldn't need an explicit
search path setting on the user so I would remove that setting.

ALTER ROLE postgres RESET search_path;

Hope that helps,
Regina
http://www.postgis.us

 

> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On
> Behalf Of THX1138
> Sent: Thursday, April 05, 2012 10:15 PM
> To: [hidden email]
> Subject: Re: [postgis-users] Severe shapefile upload issues
>
> My postgis full version is as follows:
>
> "POSTGIS="2.0.0 r9605" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel.
> 4.8.0, 6 March 2012" GDAL="GDAL 1.9.0, released 2011/12/29"
> LIBXML="2.7.8"
> LIBJSON="UNKNOWN" RASTER"
>
> My install (on Ubuntu-64 bit) could not be dirty because I
> created a new virtual machine and built it from scratch.
>
> I am using  shp2pgsql and pgadmin from a windows machine (64
> bit with 64 bit postgres and 64 bit postgis) and piping to my
> ubuntu server.
>
> On my windows machine I completely removed postgres and
> reinstalled everything so there should be no conflict their either.
>
> I managed to solve the addgeometrycolumn problem by
> impementing Bborie's suggestion (thanks btw, I would have
> never realized that I was supposed to do that!)
>
> I used:
>
> create schema postgis;
> create extension postgis with schema postgis; create
> extension postgis_topology; set search_path to
> public,postgis; ALTER USER postgres set search_path to
> public,postgis; GRANT ALL ON SCHEMA public TO postgres; GRANT
> ALL ON SCHEMA public TO public;
>
> The srid conversion issue remains and I tested on both
> windows and linux and I get the same error result...
>
>  I run:
>
> shp2pgsql -c -s 2277:4326  -i -I "C:\tcad2\20120301_TCAD
> SHAPEFILES\MUD.shp"
> postgis.mudtest4 | psql -d testing -U postgres -h 10.0.0.000 -p 5432
>
> and get back:
>
> ERROR:  function st_transform(unknown, integer) is not unique
> LINE 1: ...CH
> MUD','4.55571330968e+007','3.22484265423e+004',ST_Transfo...
>                                                              ^
> HINT:  Could not choose a best candidate function. You might
> need to add explicit type casts.
>
> I don't see how this could be a version upgrade error,
> considering I did clean installs on both windows and ubuntu ,
> with the linux install being done on a brand new virtual
> machine and the windows on being done using precompiled
> binaries and installed using the batch file that came with them.
>
> Am I perhaps forgetting to specify an option or adding in one
> that is incompatible? I have tried multiple shapefiles and
> nothing works.
>
>
> --
> View this message in context:
> http://postgis.17.n6.nabble.com/Severe-shapefile-upload-issues
-tp4690980p4691389.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>


_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Severe shapefile upload issues

Andrew Joseph
Everything imports fine if I do not use the transform. I would also point out that the srid conversion functionality is missing in the shapefile/dbf loader GUI in pgadmin as there is no box for convert srid.

As to my path issues, if I do not change my user path addgeometrycolumn fails.

I used:

ALTER ROLE postgres RESET search_path;

and it yielded the function does not exist error. When I changed it back it worked fine again.

From what I can tell, set search_path to seems to only affect the database temporarily for the given session. The reason why it seems to be failing is because in order to execute an query involving postgis one would need to run "set search_path to postgis,..." in a preceding query in the same session in order for it to take, whereas if you change the users path it sets the path permanently.

It seems to fail differently on different systems as well.

For example, if I do not change the user path on my windows postgres I cannot even use  postgis_full_version() but this works fine on my linux install without altering my user path.

In pg_db_role_setting there is originally no data for the user search path.

When I add a user search path I get back:
"{"search_path=public, postgis"}"

When I reset the role it simply deletes the entry again and processes begin to fail.

Is there  a way to permanently set the database search path without changing the user's search path?

Thanks,

THX1138
Reply | Threaded
Open this post in threaded view
|

Re: Severe shapefile upload issues

Regina Obe-2

Hmm strange works all fine on my window box and my postgres doesn't have
it's own search_paths.

You did set the search path of the database right?
Mine is like this:

ALTER DATABASE mygisdb SET search_path="$user", postgis, public,  contrib;

and it works fine for all users.  I do have user specific schemas thus the
need for "$user"


> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On
> Behalf Of THX1138
> Sent: Friday, April 06, 2012 5:36 PM
> To: [hidden email]
> Subject: Re: [postgis-users] Severe shapefile upload issues
>
> Everything imports fine if I do not use the transform. I
> would also point out that the srid conversion functionality
> is missing in the shapefile/dbf loader GUI in pgadmin as
> there is no box for convert srid.
>
> As to my path issues, if I do not change my user path
> addgeometrycolumn fails.
>
> I used:
>
> ALTER ROLE postgres RESET search_path;
>
> and it yielded the function does not exist error. When I
> changed it back it worked fine again.
>
> From what I can tell, set search_path to seems to only affect
> the database temporarily for the given session. The reason
> why it seems to be failing is because in order to execute an
> query involving postgis one would need to run "set
> search_path to postgis,..." in a preceding query in the same
> session in order for it to take, whereas if you change the
> users path it sets the path permanently.
>
> It seems to fail differently on different systems as well.
>
> For example, if I do not change the user path on my windows
> postgres I cannot even use  postgis_full_version() but this
> works fine on my linux install without altering my user path.
>
> In pg_db_role_setting there is originally no data for the
> user search path.
>
> When I add a user search path I get back:
> "{"search_path=public, postgis"}"
>
> When I reset the role it simply deletes the entry again and
> processes begin to fail.
>
> Is there  a way to permanently set the database search path
> without changing the user's search path?
>
> Thanks,
>
> THX1138
>
> --
> View this message in context:
> http://postgis.17.n6.nabble.com/Severe-shapefile-upload-issues
> -tp4690980p4693779.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>


_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Severe shapefile upload issues

Regina Obe-2
In reply to this post by Andrew Joseph
One other thought.  It's possible you have the search_path set for postgres
in your database.  I think that features was introduced in postgresql 9.0 or
9.1.  Can't recall.

So for example in my database, I have a setting like this:

ALTER ROLE postgres IN DATABASE mygisdb SET search_path="$user", master,
postgis, public,  contrib;


Because when I log in as postgres, I want postgres to hit the master schema
(which all the project schemas inherit from), but the application user
hits the project schema instead.
So you might want to try this to wipe out any db specific postgres user
search path setting you have.

ALTER ROLE postgres IN DATABASE mygisdb RESET search_path;

Regarding the missing transform in loader GUI -- just put in a ticket
request enhancement under dumper/loader for that one:

http://trac.osgeo.org/postgis/newticket

hope that helps,
Regina
http://www.postgis.us


> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On
> Behalf Of THX1138
> Sent: Friday, April 06, 2012 5:36 PM
> To: [hidden email]
> Subject: Re: [postgis-users] Severe shapefile upload issues
>
> Everything imports fine if I do not use the transform. I
> would also point out that the srid conversion functionality
> is missing in the shapefile/dbf loader GUI in pgadmin as
> there is no box for convert srid.
>
> As to my path issues, if I do not change my user path
> addgeometrycolumn fails.
>
> I used:
>
> ALTER ROLE postgres RESET search_path;
>
> and it yielded the function does not exist error. When I
> changed it back it worked fine again.
>
> From what I can tell, set search_path to seems to only affect
> the database temporarily for the given session. The reason
> why it seems to be failing is because in order to execute an
> query involving postgis one would need to run "set
> search_path to postgis,..." in a preceding query in the same
> session in order for it to take, whereas if you change the
> users path it sets the path permanently.
>
> It seems to fail differently on different systems as well.
>
> For example, if I do not change the user path on my windows
> postgres I cannot even use  postgis_full_version() but this
> works fine on my linux install without altering my user path.
>
> In pg_db_role_setting there is originally no data for the
> user search path.
>
> When I add a user search path I get back:
> "{"search_path=public, postgis"}"
>
> When I reset the role it simply deletes the entry again and
> processes begin to fail.
>
> Is there  a way to permanently set the database search path
> without changing the user's search path?
>
> Thanks,
>
> THX1138
>
> --
> View this message in context:
> http://postgis.17.n6.nabble.com/Severe-shapefile-upload-issues
-tp4690980p4693779.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>


_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Severe shapefile upload issues

Andrew Joseph
The following works fine for me:

psql -U postgres
#in psql prompt:
CREATE DATABASE template_postgis20 WITH TEMPLATE = template1 ENCODING = 'UTF8';
\c template_postgis20
CREATE SCHEMA postgis;CREATE EXTENSION postgis WITH SCHEMA postgis; CREATE EXTENSION postgis_topology;
SET search_path TO postgis;
\i /usr/share/postgresql/9.1/contrib/postgis-2.0/legacy_minimal.sql
ALTER DATABASE template_postgis20 SET search_path TO public,postgis,topology;
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template_postgis20';

When I originally did:

SET search_path TO postgis;

I thought that this meant to set the search path in the database (not for the instance of a session), I did not realize that I actually had to alter the database and change the search path.

It might be worth adding something about going about these steps to the extension installation documentation as, while it makes sense once you get used to it, someone new to schemas, such as myself, assumes their postgis install itself is corrupt.

Thanks for helping me solve this issue!

THX1138