[GIS] Load a shapefile in PostgreSQL database with PHP

PHPpostgisshapefileshp2pgsql

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

Best Answer

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.

Related Question