0

I am trying to run .sql file/import into MySQL database, however, I am running into Error Code: 1416. Cannot get geometry object from data you send to the GEOMETRY field .

Here's my code:

CREATE TABLE `listings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  .  
  .  
  .  
  .  
  `location` point DEFAULT NULL,
  `lat` decimal(10,8) DEFAULT NULL,
  `lng` decimal(11,8) DEFAULT NULL,
  `meta` text COLLATE utf8_unicode_ci,
  `city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `region` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `country` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=78 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC;
INSERT INTO `listings` VALUES (
   1,
   .
   .
   .
   'GBP',
   '\0\0\0\0\0\0\0\ÐoN\Ûu%º¿0žACÁI@',
   51.61100420,-0.10213410,
   '[]',
   'London',
   NULL,
   'UK'

I am using MySQL Workbench v8.0.15

  • in all your unreadable example is not clear what you are trying to insert, how may fields do you have any way. Show also the create table of listings. because in all the text i cn't see any geometyr data at all, but maybe it is hidden somewhere – nbk Apr 13 at 19:36
  • Ok i counted 52 columns, that is a lot , but still for number 1 i could find any geometry data a longitude latitude . – nbk Apr 13 at 19:37
  • I added the create table statement and made the code more readable now. – kms Apr 14 at 4:02
  • @nbk The error seems to be appear from location point DEFAULT NULL and the encoding for point datatype seems to be off. – kms Apr 14 at 4:09
1

You problem is mysql expects there a kind of binary format and not a string even it is a representation, but i concverted it into bonary and it still will nozt insert the data.

When you realy want toi save it you must use.

POINT(51.61100420,-0.10213410)

instead of your string, however you got from.

I think your backup program does something wrong. With mysqk workbench export Poit loo like

INSERT INTO `tespint` VALUES (_binary '\0\0\0\0\0\0\0ý[¸b5\ÎI@\ÐoN\Ûu%º¿',51.61100420,-0.10213410);
| improve this answer | |
  • right, point data is set of coords that are being inserted into the table. however, the values that are being passed have strange encoding type -- '\0\0\0\0 \0\0\0\ÐoN\Ûu%º¿ 0žACÁI@' – kms Apr 14 at 16:01
  • For context, I am running a .sql file created from mysql daily dump. – kms Apr 14 at 16:02
  • What chaeacter set are you running, where the dump comes from? Is it different from that where you insert the data? – nbk Apr 14 at 16:15
  • The dump is auto-generated from a service I use. Schema is of character set: utf8mb4. Create and INSERT tables also use utf8 character set, which is default encoding for mySQL, I think. – kms Apr 14 at 16:27
  • Workbench and phpmyadnin can export and import point data. You should give it a try – nbk Apr 14 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.