2

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?

2

Given that "Machine Y has neither psql or shp2pgsql installed" but does have access to the shapefiles, you could try to transform those shapefiles in server Y into some plaintext format and push them though the node connector into machine X.

For example, with node's shapefile package you could transform shapefiles into geojsons and have them insterted into the database at server X using postgis' ST_GeomFromGeoJSON function.

  • What if the shapefiles are in a projection not supported by GeoJSON? – nmtoken Nov 3 '17 at 13:37
  • I think you could reproject them using something like gdal for node – pandorabob Nov 8 '17 at 23:16
2

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();
}));
...
  • Machine Y has neither psql or shp2pgsql installed; only a node server that can talk to the database that's on machine X. – apricity Nov 1 '17 at 22:48
  • @apricity Are you will be able to run psql from server X server.x$ psql -d dbname -h server.y -U.... to connect to the db server? – huckfinn Nov 3 '17 at 8:44
  • Yes, but X IS the db server. I think you have the variables flopped; I should've named them better. But my point is that on the node machine psql and shp2pgsql is not installed. I can only use this connector inside node to communicate to the db-server: github.com/brianc/node-postgres to talk to the db-server. – apricity Nov 3 '17 at 17:57

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.