Re: [postgis-users] import shape or sql to postgres automatically
Hello, Thanks for the link. I tried psql with my computer and it gives the error FATAL: Ident authentication failed for user "username" Error and Solution. I google it, and find the solution could be by modifying the pg_hba.conf file. I modified the file and saved. After I run your code again it comes another error:Could not connect to server: permission denied. Is the server running locally and accepting connections on Unix domain socket "var/run/postgresql/.s.PGSQL.5432"Somebody suggested this solution: The permissions on the socket file var/run/postgresql/.s.PGSQL.5432 Ordinarily the postmaster will create the socket as world-writable, but evidently not this time. Perhaps you have set a nonstandard value for "unix_socket_permissions"? Another possibility is that it's not the socket file itself, but /var that is mis-permissioned. I didn´t know how to solve this problem. Do I have to install web server such as apache in my machine? I have used chmod 777 to change the right of var/run/postgresql/.s.PGSQL.5432, but it still doesn´t work. Can you help me? Thanks > Date: Mon, 13 Dec 2010 12:13:44 + > From: mark.cave-ayl...@siriusit.co.uk > To: postgis-users@postgis.refractions.net > Subject: Re: [postgis-users] import shape or sql to postgres automatically > > zhang zhe wrote: > > > Hello, > > > > Does anyone know how to import sql table or shape file to postgres > > database automatically? > > What I did before is: > > I have shpe file save in C:/users > > I go to terminal in linux, and go to the directory C:/users > > C:/users shp2pgsql shapefile.shp > sqlfile.sql > > I will get sql file saved into c:/users directory. I open the sql file > > copy all the sql query. After that I open postgres database, and paste > > all the query to sql query window, and run the query. I will have new > > table appear in the dabase. > > > > Sometimes the sql table is too large, that I cannot paste them all in > > the postgres sql query window. Postgres is always get stuck because of > > the query is too large.For instance I have road dataset of whole contry, > > and it has really millions of queries. Is there any way to import shape > > file or sql table to Postgres database automatically without any manual > > work? > > Assuming you have the PostgreSQL client installed on your local Windows > workstation, it sounds as if you need to pass the -f option to psql and > then use -h to connect to your linux server instead, e.g: > > psql.exe -h mylinuxserver -d mydatabase -U myuser > -f c:/users/sqlfile.sql > > HTH, > > Mark. > > -- > Mark Cave-Ayland - Senior Technical Architect > PostgreSQL - PostGIS > Sirius Corporation plc - control through freedom > http://www.siriusit.co.uk > t: +44 870 608 0063 > > Sirius Labs: http://www.siriusit.co.uk/labs > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] import shape or sql to postgres automatically
Hello, I might works this way. But I want to import the sql or shape file to postgres automaticlly without any manual work. I have php file, Í want to find the postgres query that could import a sql table to post gis. My php code could execute the query when user visit my web page. Do you have any idea how to do this? Date: Mon, 13 Dec 2010 12:28:20 + From: robertdbuck...@yahoo.com To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] import shape or sql to postgres automatically Hi, I find it easyest with the built in GUI from the opengeosuite. You can download the community edition and use the whole Shape2postgis import tool as part of the deal here is the link to the download. http://opengeo.org/community/suite/download/ Simply install the desktop version and start the dashboard. In PGAdmin3 for Opengeo there is the llink to the postgis importer tool. Hope this helps, Robert Buckley ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] import shape or sql to postgres automatically
zhang zhe wrote: Hello, Thanks. Actually, I have windows and apache server installed in my computer. In this case how the code should look like? What´s the mylinuxserver ? is it the IP adress of the server? or name of the server? Will my code changed into psql.exe -h mywindowsServer -d postgres -U zhe -f c:/users/sqlfile.sql You can, but I would highly recommend that you read the psql.exe documentation at http://www.postgresql.org/docs/9.0/interactive/app-psql.html which should answer all your questions. In particular, the postgres database is an in-built database designed for maintenance and shouldn't be used to store user data. Can I write this code to php? Indeed - you'll need to look at the exec() function. Don't forget to escape your shell characters for security. HTH, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] import shape or sql to postgres automatically
Hello, Thanks. Actually, I have windows and apache server installed in my computer. In this case how the code should look like? What´s the mylinuxserver ? is it the IP adress of the server? or name of the server? Will my code changed into psql.exe -h mywindowsServer -d postgres -U zhe -f c:/users/sqlfile.sql Can I write this code to php? Thanks. psql.exe -h mylinuxserver -d mydatabase -U myuser -f c:/users/sqlfile.sql > Date: Mon, 13 Dec 2010 12:13:44 + > From: mark.cave-ayl...@siriusit.co.uk > To: postgis-users@postgis.refractions.net > Subject: Re: [postgis-users] import shape or sql to postgres automatically > > > > Hello, > > > > Does anyone know how to import sql table or shape file to postgres > > database automatically? > > What I did before is: > > I have shpe file save in C:/users > > I go to terminal in linux, and go to the directory C:/users > > C:/users shp2pgsql shapefile.shp > sqlfile.sql > > I will get sql file saved into c:/users directory. I open the sql file > > copy all the sql query. After that I open postgres database, and paste > > all the query to sql query window, and run the query. I will have new > > table appear in the dabase. > > > > Sometimes the sql table is too large, that I cannot paste them all in > > the postgres sql query window. Postgres is always get stuck because of > > the query is too large.For instance I have road dataset of whole contry, > > and it has really millions of queries. Is there any way to import shape > > file or sql table to Postgres database automatically without any manual > > work? > > Assuming you have the PostgreSQL client installed on your local Windows > workstation, it sounds as if you need to pass the -f option to psql and > then use -h to connect to your linux server instead, e.g: > > psql.exe -h mylinuxserver -d mydatabase -U myuser > -f c:/users/sqlfile.sql > > HTH, > > Mark. > > -- > Mark Cave-Ayland - Senior Technical Architect > PostgreSQL - PostGIS > Sirius Corporation plc - control through freedom > http://www.siriusit.co.uk > t: +44 870 608 0063 > > Sirius Labs: http://www.siriusit.co.uk/labs > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] import shape or sql to postgres automatically
Can you not just pipe it in : (from the manual) shp2pgsql shaperoads.shp myschema.roadstable | psql -d roadsdb or use the \i metacommand to run the created script. cheers Ben On 13/12/2010, at 10:22 PM, zhang zhe wrote: > Hello, > > Does anyone know how to import sql table or shape file to postgres database > automatically? > What I did before is: > I have shpe file save in C:/users > I go to terminal in linux, and go to the directory C:/users > C:/users shp2pgsql shapefile.shp > sqlfile.sql > I will get sql file saved into c:/users directory. I open the sql file copy > all the sql query. After that I open postgres database, and paste all the > query to sql query window, and run the query. I will have new table appear in > the dabase. > > Sometimes the sql table is too large, that I cannot paste them all in the > postgres sql query window. Postgres is always get stuck because of the query > is too large.For instance I have road dataset of whole contry, and it has > really millions of queries. Is there any way to import shape file or sql > table to Postgres database automatically without any manual work? > > I am now developing a application in php. In php, is there method to import a > sql table or shape file to Postgres automatically? I know with php there is > way to connect postgres and run the query. > > $query 1="import sql table to postgres"; > pg_query($query1) or die ('query1 failed'. pg_last_erro()); > > Is there any query could import sql table to postgres automatically? > > Thanks a lot:) > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] import shape or sql to postgres automatically
Hi, I find it easyest with the built in GUI from the opengeosuite. You can download the community edition and use the whole Shape2postgis import tool as part of the deal here is the link to the download. http://opengeo.org/community/suite/download/ Simply install the desktop version and start the dashboard. In PGAdmin3 for Opengeo there is the llink to the postgis importer tool. Hope this helps, Robert Buckley ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] import shape or sql to postgres automatically
zhang zhe wrote: Hello, Does anyone know how to import sql table or shape file to postgres database automatically? What I did before is: I have shpe file save in C:/users I go to terminal in linux, and go to the directory C:/users C:/users shp2pgsql shapefile.shp > sqlfile.sql I will get sql file saved into c:/users directory. I open the sql file copy all the sql query. After that I open postgres database, and paste all the query to sql query window, and run the query. I will have new table appear in the dabase. Sometimes the sql table is too large, that I cannot paste them all in the postgres sql query window. Postgres is always get stuck because of the query is too large.For instance I have road dataset of whole contry, and it has really millions of queries. Is there any way to import shape file or sql table to Postgres database automatically without any manual work? Assuming you have the PostgreSQL client installed on your local Windows workstation, it sounds as if you need to pass the -f option to psql and then use -h to connect to your linux server instead, e.g: psql.exe -h mylinuxserver -d mydatabase -U myuser -f c:/users/sqlfile.sql HTH, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users