CODEWITHSUNDEEP
mysqldatabasegeospatial
TIMEX
TIMEX

Reputation: 272274

Was my polygon inserted correctly into the database?

mysql> show create table places\G;
*************************** 1. row ***************************
       Table: places
Create Table: CREATE TABLE `places` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name_full` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `network_id` int(11) DEFAULT NULL,
  `fence_ll` polygon NOT NULL,
  `fence_utm` polygon NOT NULL,
  `area` bigint(20) NOT NULL,
  `created_at` bigint(20) DEFAULT NULL,
  `updated_at` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `network_id` (`network_id`),
  CONSTRAINT `places_ibfk_1` FOREIGN KEY (`network_id`) REFERENCES `networks` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.01 sec)

This is my query:

INSERT IGNORE INTO places(name, fence_ll, fence_utm, area, created_at) 
VALUES('West Covina',
   GeomFromText('POLYGON(( 34.08166844698419 -118.01307678222656,33.994611584814606 -118.01307678222656,33.994042291874415 -117.89566040039062,34.08280585343835 -117.89634704589844)) ', 0), 
   GeomFromText('POLYGON(( 406532.3562310586 3771674.3531455416,406436.8059323749 3762021.032807315,417280.6563504976 3761856.9033192885,417303.43441323494 3771699.900605784)) ', 0), 105355675, 1471220285)

The command inserts the record correctly, but for some reason fence_ll and fence_utm are blank.

*************************** 5. row ***************************
        id: 11
      name: Los Angeles
 name_full: NULL
network_id: NULL
  fence_ll: 
 fence_utm: 
      area: 6859688899
created_at: 1471220285
updated_at: NULL

My worry is that the polygon points are too precise. If so, to what decimal places should I truncate the points?

Upvotes: 1

Views: 36

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

It looks like the first and last points of your polygons are not the same, and this seems to be a requirement. Try this instead:

INSERT IGNORE INTO places(name, fence_ll, fence_utm, area, created_at)
VALUES
    ('West Covina',
     GeomFromText('POLYGON(( 34.08166844698419 -118.01307678222656, 33.994611584814606 -118.01307678222656, 33.994042291874415 -117.89566040039062,34.08280585343835 -117.89634704589844, 34.08166844698419 -118.01307678222656 )) ', 0),
     GeomFromText('POLYGON(( 406532.3562310586 3771674.3531455416,406436.8059323749 3762021.032807315,417280.6563504976 3761856.9033192885,417303.43441323494 3771699.900605784, 406532.3562310586 3771674.3531455416 )) ', 0), 105355675, 1471220285)

Reference

Upvotes: 1

Related Questions