Insert geometry values in mysql using node.js

Question

I am using https://github.com/felixge/node-mysql module with node.js.

Mysql table has a field of type POINT. The module requires to send array of arrays to insert bulk records. But It doesn't seem to have option to specify data type.

So naturally, the following gets enclosed in quotes

var loc = "GeomFromText('POINT(" + lat + "," + lon + ")')";

Has anybody tried this? How can I convince the query builder to treat this as an sql function?

Or do I have to make my own query builder?

Answer 1

There is a pull request from kevinhikaruevans that does it. You can do something like that to convert objects to points:

if (typeof val === 'object') {
    if(val.hasOwnProperty('lat') && val.hasOwnProperty('long')) {
        return 'POINT(' + [val.lat, val.long].map(parseFloat).join(',') + ')';
    }
}

Supposing you have a table mytable with only the field point of type POINT, you would insert them like this:

var points = [
    [{ lat: 1, long: 4}],
    [{ lat: 23, long: -8.345}]
];
var query = connection.query('INSERT INTO mytable(point) VALUES ?', [points], your_callback_func);
console.log("Query: " + query.sql);

This will generate a query similar to:

INSERT INTO mytable(point)
VALUES (POINT(1,4)), (POINT(23,-8.345))

This would convert any object with both lat and long fields to a MySQL point. If this is not an intended behavior, you could create a Point class and use it instead of plain objects, and in lib/protocol/SqlString.js check if the value is an instance of Point.