Spatial Column Support

[scube-elate]

Adonis v5 is amazing. I'm building an office project and I'm stuck managing the spatial column on MySQL

I created a spatial column on the migration file by running the following code since I couldn't find an inbuilt method on migration documentation that offers spatial column creation
this.schema.raw('ALTER TABLE delivery_locations ADD latlng POINT;').

I was wondering if I could use Lucid ORM to insert into latlng column by prepare decorator? Something like this,

  @column({
    prepare: (latlng: string) => `POINT(${latlng})`,
  })
  public latlng: string

Package version

5.1.11

Node.js and npm version

Node.js - v14.15.4
Npm - 6.14.10

Or did I miss anything on documentation? A better way to do it?

[thetutlage]

Is it not working for you or are you looking for a more pragmatic way to do it?

[scube-elate]

It is not working for me. I have to wrap the point value with ST_GeomFromText('POINT(x y)'). But I'm unable to do it with prepare statement.

As of now, I'm perform it like the following

await Database.rawQuery(`
       insert into delivery_locations
          (
            \`address\`, \`city\`, \`country\`, \`created_at\`, \`delivery_hours\`, \`latlng\`, \`number\`,
            \`point_of_contact_id\`, \`state\`, \`title\`, \`updated_at\`, \`user_id\`, \`zip\`
          ) values
          ('${row.address}', '${row.city}', '${row.country}', '${date}', '${row.delivery_hours}', ST_GeomFromText('POINT(${row.latlng})'),
          '${row.number}', ${row.point_of_contact_id}, '${row.state}', '${row.title}', '${date}', ${user.id}, '${row.zip}')
      `)

because following code wouldn't work

  @column({
    prepare: (value: string) => `ST_GeomFromText(POINT(${value.replace(',', ' ')}))`,
  })
  public latlng: string

Is there a way where I can use DB raw to insert into db something like the following

  @column({
    prepare: (value: string) => Database.raw(`ST_GeomFromText(POINT(${value.replace(',', ' ')}))`),
  })
  public latlng: string

[thetutlage]

Can you check with knex ( outside AdonisJS ), if it allows insert values to be a raw query? If yes, then I can take out some time in next couple of days to add support for it.

What you have to check is following.

knex.table('users').insert({
   latlng: knex.raw(`ST_GeomFromText(POINT(${value.replace(',', ' ')})) `)
})

If not, then unfortunately it needs more work on my end to support it outside the Knex landscape