[GIS] MySQL Polygon (Spatial) Insert Statement works sporadically

mysql-spatialpolygon

I know that MySQL doesn't have great spatial support at the moment, but is it that buggy that my insert statements would work sporadically? In general, longer coordinates seem to be failing on insert (there is no notice of them failing though, they just insert as NULL or show 0 rows affected on update). I believe though that I've seen large ones work and small ones fail. Here's my insert statement:

UPDATE places SET geom = PolygonFromText('POLYGON((-122.225255 37.890342,-122.22343 37.891445,-122.220923 37.89297...)),1') WHERE id=16

The only semi-logical things I can think of pertain to size/memory, or maybe invalid coordinates? Anyone have any ideas?

Best Answer

By default, MySQL has a maximum packet size of 1 MB. This can easily cause INSERT statements with complex polygons to fail. You can check your maximum size in bytes using

SHOW VARIABLES LIKE 'max_allowed_packet'

The MySQL docs explain a few different ways to adjust this parameter.

Related Question