TIMEX TIMEX - 3 months ago 11
MySQL Question

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?

Answer

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