save features from openlayers to postgis via nodejs and websockets save features from openlayers to postgis via nodejs and websockets postgresql postgresql

save features from openlayers to postgis via nodejs and websockets


With this query:

var query = client.query("INSERT INTO pins (p_geom) values('"+pra+"')")

you should not be quoting pra. This will quote the ST_GeomFromText function call, and your submitted query will be something like:

var query = client.query("INSERT INTO pins (p_geom) values('ST_GeomFromText('LINESTRING(2335859.0225 4725430.1340625,2378933.155 4741356.7040625)',900913)')")

PostGIS thinks you are trying to submit a poorly formatted string, with unescaped single quotes in the middle of it.

Switching your query string builder to do this should work:

var query = client.query("INSERT INTO pins (p_geom) values("+pra+")")

BUT DON'T DO THIS

You are now opening yourself up to a SQL injection attack because someone may hack the HTTP request to send something sneaky instead of the geometry WKT you are expecting. Don't use string concatenation to embed input from a web browser into your database query because it bypasses some safety features of your framework.

Now, back to your first attempt to use a parameterized query:

The node.js library is trying to prepare your parameter for insertion into the DB, and is treating it like a String, surrounding it with single quotes, and escaping all embedded single quotes. This causes the query to be sent to PostGIS as:

INSERT INTO pins(p_geom) values('ST_GeomFromText(''LINESTRING(2335859.0225 4725430.1340625,2378933.155 4741356.7040625)'',900913)')

PostGIS then helpfully tries to convert the string to a geometry by parsing it, and complains that a valid geometry wouldn't start with "ST"

Instead of passing ST_GeomFromText(...) as a String parameter, just pass the part of the query that is really a string - the WKT. Something like this:

var wkt = "LINESTRING("+geo+")";

var query = client.query('INSERT INTO pins(p_geom) values(ST_GeomFromText(?,900913))', [wkt])