2

I am trying to load a shapefile in a PostGIS database using the exec() command. I have read the format I have to follow from here and I checked several similar questions from the forum.

Based on the above I wrote this script:

$command = 'shp2pgsql -s 2100 -d /home/enomix/www/EsoterikoFinal/Maps/shps/nodes.shp | psql -h localhost -p 5432 -d shapeDb -U postgres -W xxx'; $output = exec($command); print_r($output);

What I want is to load the shapefile into a newly created table which is in a database called 'shapeDb'. Every time I load a shapefile I want the previous table to be dropped and a new table to be created (that's why I use the -d option). I have defined a full path for my shapefile (should I define a relative one like: "shps/nodes"?)

When I run the script I get nothing back as a response although I have a:

ini_set('display_errors', 1);

Its the first time I do something like this. Any idea what I am doing wrong?

EDITED

Running the above command on the terminal it works fine. But I still can not figure out how to drop the previous table and create a new one each time I run the query. And also why the exec command doesn't work!

EDITED II

Now I made it work. The -W xxx was causing problems. I don't know why though (its just the password). I run my script like this:

$command = 'shp2pgsql -s 2100 /home/enomix/www/EsoterikoFinal/Maps/shps/nodes.shp  | psql -h localhost -p 5432 -d esoteriko -U postgres';
exec($command,$out,$ret);

BUT still I can not figure out how to drop the table each time and create a new one. ANY IDEAS?

EDITED III

This works now:

$command = 'shp2pgsql -s 2100 -d /home/enomix/www/EsoterikoFinal/Maps/shps/nodes.shp  | psql -h localhost -p 5432 -d esoteriko -U postgres';
exec($command,$out,$ret);

Note: Don't forget to add the postgis extension to your postgresql database like:

CREATE EXTENSION postgis
3

First of all, You need to add a new environment variable called PGPASSWORD

    $ export PGPASSWORD='password'

But, if you try to set as usual, the www-data doesn't see the variable, so you need to add the line PGPASSWORD='password' to the /etc/apache2/envvar.

Test if this works typing:

    sudo -u www-data bash -c "<the_command_shp2pgsql>"

Then you can run the php script bellow as www-data the custom apache user.

    $command  "shp2pgsql -a -k -s <SRID> /<directory_of_shapes>/<your_shape_file>.shp <postgis_table>  | psql -h <your_host_name> -p 5432 -d <your-database> -U postgres ";

    $retorno = shell_exec($command);
    acho $retorno;

the <directory_of_shapes> must have www-data permission access.

  • +1 for hint to adding the Environment Variable, but export doesn't worked for me, instead I used putenv("PGPASSWORD=$pw"); and unset it with putenv('PGPASSWORD'); – Ms. Smith Feb 23 '18 at 8:34
1

This works now:

$command = 'shp2pgsql -s 2100 -d /home/enomix/www/EsoterikoFinal/Maps/shps/nodes.shp  | psql -h localhost -p 5432 -d esoteriko -U postgres';

exec($command,$out,$ret);

0

An alternative to using evil eval() would be my PHP Shapefile library, that can read and write any ESRI Shapefile natively in PHP.

I (and many others, actually) have been using it for this very same scope, loading tons of Shapefiles into PostgreSQL, thanks to the WKT output provided by the library and PostGIS ST_GeomFromText() function.

It is a free, open source, native PHP library, does not require any third party dependency and is actively maintained.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.