Load Shapefiles into a remote PostGIS db using command line?

Question

Can we import a Shapefile from a system which doesn't have PostgreSQL installed (client machine) into a system having PostGIS installed?

I am getting problem with 'shp2pgsql' and 'psql' since both are not recognized on the client machine.

I am trying this using console.

Answer 1

Not a command line solution, but since you tagged the question QGIS:

I recommend using QGIS PostGIS Manager plugin which is a GUI for shp2pgsql which allows you to upload Shapefiles to a remote database.

enter image description here

Answer 2

I think that if you qgis installed you will have also ogr2ogr (It's provided with the gdal-bin package). With the following command you will create a table called yousahpefile in your database:

ogr2ogr -f PostgreSQL PG:"host=server_ip user=username dbname=dbname password=password" yourshapefile.shp;

There are some workarounds if you find troubles with the encoding.

• Mike T: except replace localhost with the IP address of the remote PostGIS database, e.g. host=192.168.123.12 or whatever

Answer 3

You'll need to install shp2psql on the client machine or copy the shapefile to a machine with it installed.

Answer 4

I think you just need to copy the relevant files. I've done this on ewindows. Haven't tried on Linux. For windows I copy the shp2pgsql.exe, libpq.dll, libiconv-2.dll, comerr32.dll, msvcr71.dll , a couple of others but those are the key ones -- these are located in the PostgreSQL bin folder

Linux ones would be different but libpq and shp2pgsql are definitely required

Answer 5

You can either use shp2pgsql on your local copy of the database, create a dump of the new database and upload it, ... or install shp2pgsql on the remote machine, scp the shapefile over, and shp2pgsql from there (as iant recommends).

AFAIK there is no way to do this remotely in one step. This is because even if you have access to the remote database from where you are, the functions you are using (such as shp2pgsql) are the ones installed on the server, regardless of what is on your local machine.

Answer 6

I'm developing a web map application that uses geoserver + express as its server and postgreSQL + postGIS as its database. In one part, I wanted to add some functionalities that users can upload their .shp files remotely. After spending days, I found that it's possible to execute shell commands just using exec function that both php and express provides for us. So because of psql and shp2pgsql are commands that can be executed in OS command line, exec function is a solution.

Although I use javascript at client and server, in the following I've given a simple piece of code for both php and express based on your need:

1) PHP

This is a part of your php code that your html form is submitted.

$username = 'your_username';
$password = 'your_password';
$dbname = 'target_databasename';
$tbname = 'target_tablename';
$srsname = 'reference_system';
$fileName = 'shapefile_name';
$directory = '\\directory\\' .$fileName. '.shp';
$shellCommand = "shp2pgsql -I -s " .$srsname. " " .$directory. " public." .$tbname. " | psql -U " .$username. " -d " .$dbname."";
$shapfileLoader = exec($shellCommand,$stdOut,$stdErr);
if (!$stdErr) {
 echo $stdOut; 
} else {
 echo $stdErr;
}

2) Express

After defining a path in your express server, just add this js code:

var express = require('express');
var router = express.Router();
var { exec } = require('child_process');
router.post("/", function (_req, _res, _next) {
const postGISdb = 'db_name';
const user = 'your_username';
 exec(`psql -U ${user} -d ${postGISdb}`, (err, stdOut, stdErr) => {
 console.log(stdOut)
 })
});
module.exports = router;

1) Don't forget to replace the database config information by your own.

2) You don't need QGIS anymore.

3) By using exec function, you should enter postgreSQL password so you need to create a .pgpass file to set password non-interactively and automatically. For more information, check this link.

Answer 7

const postGISdb = "database";
 const user = "postgres";
 const password = "password";
 const host = "localhost";
 const port = 5432;
 exec(
 `shp2psql -s 4326 -d -W UTF-8 -I -f - -c -h ${host} -p ${port} -U ${user} ${password} ${postGISdb} | psql -h ${host} -p ${port} -U ${user} ${password} ${postGISdb} `,
 (err, stdOut, stdErr) => console.log(stdOut),
 );

• Vince: The Question explicitly states that shp2psql is unavailable on the system.