4

In Laravel migrations, what should I use as the default value of Point type column? I originally wanted to keep it NULL but then I read that:

Columns in spatial indexes must be declared NOT NULL.

So what should I use as the default value of my column and how do I specify that in migration, to represent NULL, something like 0,0 or -1,-1?

$table->point('location')->default(???);

UPDATE

Doing more research, I found an even bigger issue. MySQL doesn't allow specifying default value for POINT type column. So I must insert a NULL-equivalent at INSERT time. What would be the correct value for this purpose?

dotNET
  • 33,414
  • 24
  • 162
  • 251
  • 1
    @Downvoter: Sometimes I just wonder... – dotNET Sep 26 '18 at 05:31
  • maybe `->point('location')` instead of `->point(location')`? – Egretos Sep 26 '18 at 06:16
  • @Egretos: Yep. Thanks for the input. – dotNET Sep 26 '18 at 06:20
  • @Kyslik: Yep. Really really. I'm using GoDaddy's shared hosting and MySQL is my only option. – dotNET Sep 26 '18 at 10:57
  • Did you see https://stackoverflow.com/a/30816278/4848587? Looks like you have to choose a default value depending on your use case. – Jonas Staudenmeir Sep 26 '18 at 12:53
  • 1
    @JonasStaudenmeir: Thanks for the pointer. No, I didn't see this before. Looks like many people are struggling with the same issue. I was going to use `(-1000, -1000)` as my null value, but was not sure if MySQL would be happy with that. Looks like North Pole is cold enough to be set as null. :) – dotNET Sep 26 '18 at 16:47

2 Answers2

2

I don't know if it is you case, but if you are trying to add a column to an existing table, and then add a spatial index to that on a migration, the way I find to address this in MySQL was not an elegant solution, but worked:

Schema::table('table', function(Blueprint $table)
{
    // Add the new nullable column
    $table->point('column')->nullable();
});
// You must separate this to ensure the execution order
Schema::table('table', function(Blueprint $table)
{
    // Insert the dummy values on the column
    DB::statement("UPDATE `table` SET `column` = POINT(0,90);");
    // Set the column to not null
    DB::statement("ALTER TABLE `table` CHANGE `column` `column` POINT NOT NULL;");

    // Finally add the spatial index
    $table->spatialIndex('column');
});
Antonio
  • 21
  • 2
  • This worked for me! I also tried to change the column to non-nullable by $table->point('column')->nullable(false)->change(), but this throws an error. So, I had to do it by raw SQL like in this answer. – kanji Jul 04 '20 at 11:53
0

Using MariaDB, I could insert it successfully with:

->default(DB::raw("POINT(0,90)"))

Antonio
  • 21
  • 2
  • Thanks for the input. Unfortunately my web host didn't support MariaDB. MySQL was my only option. – dotNET Feb 05 '19 at 02:29