This is my postgresql table:
create table scheduled_locations
(
id uuid not null,
device_id text not null,
user_id uuid,
created_date date,
st_astext geometry not null
constraint scheduled_locations_idkey
primary key
);
alter table scheduled_locations
owner to postgres;
create index gist_idx
on scheduled_locations (st_astext);
By nodejs i want to fill my table. this is my json object that i want to store into my this table:
const dataSingle = {
'id': uuidv1(),
'device_id': user.device_id,
'user_id': user.user_id,
'created_date': user.date,
'st_astext': user.multiLine
};
user.multiLine has this value:
user.multiLine
Object {type: "Feature", properties: Object, geometry: Object}
geometry:Object {type: "MultiLineString", coordinates: Array(23)}
properties:Object {}
type:"Feature"
__proto__:Object {constructor: , __defineGetter__: , __defineSetter__: , …}
By using pg-promise i want to save into postgresql:
await db.any(pgp.helpers.insert(dataSingle, null, 'scheduled_locations'));
But i got this error :
Error: parse error - invalid geometry
code:"XX000"
column:undefined
constraint:undefined
dataType:undefined
detail:undefined
file:"lwgeom_pg.c"
hint:""{"" <-- parse error at position 2 within geometry"
internalPosition:undefined
internalQuery:undefined
length:151
line:"92"
message:"parse error - invalid geometry"
name:"error"
position:"230"
routine:"pg_parser_errhint"
schema:undefined
severity:"ERROR"
stack:"error: parse error - invalid geometry\n at Connection.parseE (e:\Projects\NodeJs\HistoricalLogPg\node_modules\pg\lib\connection.js:553:11)\n at Connection.parseMessage (e:\Projects\NodeJs\HistoricalLogPg\node_modules\pg\lib\connection.js:378:19)\n at Socket.<anonymous> (e:\Projects\NodeJs\HistoricalLogPg\node_modules\pg\lib\connection.js:119:22)\n at Socket.emit (events.js:182:13)\n at addChunk (_stream_readable.js:283:12)\n at readableAddChunk (_stream_readable.js:264:11)\n at Socket.Readable.push (_stream_readable.js:219:10)\n at TCP.onread (net.js:639:20)"
table:undefined
where:undefined
I am new in gis stuff but why i got this error ? user.multiLine
is MultiLineString
and my table has a geometry column..
Even i have used pg-promise column but still have above error:
const getPoint = col => {
const p = col.source;
return p ? pgp.as.format('st_astext(${multiLine})', p) : 'NULL';
};
const cs = new pgp.helpers.ColumnSet([
{
name: 'id',
mod: ':raw',
init: generate_id
},
'device_id',
'user_id',
{
name: 'created_date',
prop: 'date'
},
{
name: 'st_astext',
prop: 'multiLine',
mod: ':raw',
init: getPoint
}
], {
table: 'scheduled_locations'
});
let results = await db.any(pgp.helpers.insert(user, cs));
First issue is that you double-redirect into property
multiLine
, i.e. you first tellColumnSet
that the value is inmultiLine
, and then you re-do that inside the formatting logic.Replace the column with this:
And then I'm guessing you want to check if the value is set, and not if the whole object is set. So the correct implementation for
getPoint
should be:P.S. I still see a problem with your code. You are returning
NULL
for the column when the value is not set, while in the table it is clearly defined as non-NULL:st_astext geometry not null
. That's a contradiction you need to fix.