inserting into Mysql Point Column type

[ghost]

I used Knex to define a table with Point type column

knex.schema.createTable('locations',function(table){
      table.increments('locid').primary();
      table.string('address').notNullable();
      table.string('nick_name');
      table.specificType('loc_gis','Point').nullable();
      table.enu('loc_type',['home','office','college','other']).notNullable();
      table.timestamps();
    })

I do not know how to insert data into the Point type column with ObjectionJS. Though selecting the Point type column returns a {x:12,y:13} object correctly.

I tried inserting with the following code but I got the error "Column count doesn\'t match value count at row 1"

ApplicantLocation.add({address:'test',locGis:{x: 12.0557031197085, y: 70.1893323197085})

[koskimas]

Are you using jsonSchema? If so, have you tried declaring the locGis as an 'object'? Same can be achieved without jsonSchema using jsonAttributes field (see the docs).

[koskimas]

That's all objection can do here. It's really up to the db client (mysql or mysql2 in your case) to handle these. Objection does no type conversions apart from the jsonAtrributes thingy.

[ghost]

Is it possible to write a plugin, which can modify the insert query and set the data something like the following.

insert into location(gis) values ST_GeomFromText('POINT(?)'

I can write the plugin, but i just want to know if the plugin architecture supports modification of insert queries

[koskimas]

When you figure out what kind of a value your mysql client expects for that type, you can do the conversion in a $formatDatabaseJson hook.

[ghost]

The following code helped me. I saw in the example that super was called before formatting was done. But i went ahead and did my formatting before calling super

$formatDatabaseJson(json) {
    // Remember to call the super class's implementation.
    json.locGis=knex.raw('point('+json.locGis.x+','+json.locGis.y+')');
    json = super.$formatDatabaseJson(json);
    return json;
  }

[koskimas]

@jagan-veeraraghavan you should use this instead to avoid an SQL injection hole:

$formatDatabaseJson(json) {
    // Remember to call the super class's implementation.
    json.locGis=knex.raw('point(?, ?)', [json.locGis.x, json.locGis.y]);
    json = super.$formatDatabaseJson(json);
    return json;
}

[Linksku]

Hi, is that still the correct way to do things? I have the following:

  $formatDatabaseJson(json) {
    if (json.lastLocation) {
      json.lastLocation = raw(
        'pointfromtext(\'point(? ?)\')',
        [json.lastLocation?.x || 0, json.lastLocation?.y || 0],
      );
    }

    json = super.$formatDatabaseJson(json);
    return json;
  }

But I'm getting:

DataError: insert intousers(birthday,email,last_location,password) values ('2000-01-01', 'test@example.com', '{\"_sql\":\"pointfromtext(\'point(? ?)\')\",\"_args\":[0,0],\"_as\":null}', '') - ER_CANT_CREATE_GEOMETRY_OBJECT: Cannot get geometry object from data you send to the GEOMETRY field

I tried both knex.raw and objection.raw.

[borisarzentar]

This works for me, if someone is still struggling with it:

$formatDatabaseJson(json) {
    const location = json.location;
    const formattedJson = super.$formatDatabaseJson(json);
    const rawLocation = raw("ST_PointFromText('POINT(? ?)')", [location.lat, location.lng]);
    formattedJson.location = rawLocation;
    return formattedJson;
}

btw, on mysql 8

[johnny5th]

Running super.$formatDatabaseJson(json) first worked for me as well. Otherwise I got the same error as @Linksku