[GIS] Importing shapefiles to a postgis on a remote server

pgsql2shppostgisshapefile

I have two servers X and Y. X contains a postgis instance whereas Y doesn't have postgis installed, but connects to the postgis instance on X through a connector in nodejs.

I want the node script to import some shapefiles which is normally done with the pgsql2shp command. However since pgsql2shp isn't installed on server Y I can't just call the command.

Is there a way to call the pgsql2shp command (or do it's equivalent) through a query? Or should I just have the script on Y ssh to server X and run the command?

Best Answer

IMO it depends on what kind of access you have on db-server Y and the import format matters allways.

Push variant

You could redirect the output of shp2pgsql ... command from the maschine X to db-server Y with:

    $ shp2pgsql ... |  psql -h some_host_name -d some_database_name ...

(assuming you are in a unixoid environment).

But the db-server Y will not understand what shp2pgsql ... on maschine X is generating and talking about without a postgis extension.

Pull variant

On the other you could import not postgis stuff like plain text files via COPY from inside the psql prompt and use tools like ssh to taylor the stuff before. 

  $ psql some_database_on_maschine_y
  > \! ssh maschine_X some_converstion_tool_to_please_psql > result_on_maschine_Y
  > COPY table_name FROM 'result_on_maschine_Y'

... or if you have generated some sql stuff as seen before..

  > \i 'result_on_maschine_Y'

If you exchange some_converstion_tool_to_please_psql with shp2pgsql it describes the push variant.

@Added central node.js variant

I'm not familar with node.js but following the examples above, you could combine a reader and a writer on our node.js environment. Here a plain non JSON example:

Create a shapefile with examples

The attributes

$ dbfcreate points -s NAME 20 -n ZIP 6 0
$ dbfadd points 'Greifswald 1' 17489
$ dbfadd points 'Greifswald 2' 17491
$ dbfadd points 'Greifswald 3' 17493

The geometry

$ shpcreate points point
$ shpadd points 54.0954 13.3786
$ shpadd points 54.0831 13.4061
$ shpadd points 54.1802 13.3435

Assuming the database table on db-server.Y:

$ psql nodejs
 CREATE TABLE points (
     id SERIAL PRIMARY KEY, 
     name VARCHAR(20), 
     zip  INTEGER);

 SELECT AddGeometryColumn('points', 'geom', 4326, 'POINT', 2);

Node script...

var tk = require('util');                     // forma statement 
var shpfile   = require('shapefile-stream');  // shapefile stream
var through   = require('through2');          // transform and insert something
var pg        = require('pg');   
var srcUrl    = 'file://home/user/points.shp'       
var dburl     = 'postgres://user:secret@db-server.Y:5432/nodejs';
var dbclient  = new pg.Client(dburl);
var srid      = 4326;
var table     = 'points';
dbclient.connect(function(err) {
    if(err) {
        console.error('could not connect to postgres', err);
        process.exit(1);
    }
});
shpfile.createReadStream( srcUrl ).pipe( through.obj( function( data, enc, next ){
    var record = tk.format("INSERT INTO %s (name, zip, geom) VALUES ( '%s',%d, ST_SETSRID(ST_POINT(%f, %f), %d)) RETURNING ID",
                           table,
                           data.properties.NAME,data.properties.ZIP,
                           data.geometry.coordinates[0],
                           data.geometry.coordinates[1],
                           srid);
    console.log("DEBUG: ", record);
    dbclient.query(record,
                   function(err, result) {
                       if (err) {
                           console.log(err);
                       }
                   });
    next();
}));
...
Related Question