How to pass an SQL function on bulk insert query in node-CodePudding

I have to execute an INSERT INTO query using mysql or mysql2 in node.js
The values passed into the query utilizes a spatial geometry function ST_GeomFromGeoJSON() on geoMap column which is of type GEOMETRY.

Here is the simplified code:

const insertQuery = `INSERT INTO maps (name, geoMap) VALUES ?`;
const insertData = [];

geoMapList.forEach((geoMap) => {
  insertData.push([
    geoMap.name,
    `ST_GeomFromGeoJSON(${JSON.stringify(geoMap.map)}, 2)`
  ]);
});

this.connection.query(insertQuery, [insertData]);

The above code does not work and throws the error
Cannot get geometry object from data you send to the GEOMETRY field

I believe this is because the ST_GeomFromGeoJSON() is not parsed as a function but as a string by MySQL.

How can this be resolved?

CodePudding user response：

You can't put MySQL function calls in the parameter array, that data is all treated as literals.

Call the function in the SQL, with the JSON string as its parameter.

I don't think you can use node-mysql's bulk-insert for this, though, so you'll need to insert each row separately.

const insertQuery = `INSERT INTO maps(name, geoMap) VALUES (?, ST_GeomFromGeoJSON(?))`
geoMapList.forEach((geomap) => 
    this.connection.query(insertQuery, [geomap.name, JSON.stringify(geomap.map)])
);

To avoid calling query() thousands of times, you can put multiple values lists in the query.

const values = Array(geoMapList.length).fill('(?, ST_GeomFromGeoJSON(?))).join(',');
const insertQuery = `INSERT INTO maps(name, geoMap) VALUES ${values}`;
const params = geoMapList.flatMap(({name, map}) => [name, JSON.stringify(map)]);
this.connection.query(insertquery, params);

Since this will create a very long INSERT query, make sure you increase the MySQL max_allowed_packet setting. You can also split geoMapList into smaller batches, rather than doing everything in a single query.