3

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?

3

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.

  • Good idea but didn't seem to fix it. Also I think if this was the case I would get an error, right now it's just null on insert or saying 0 rows affected for update – Sabrina Gelbart Apr 24 '14 at 23:14
2

From the example you linked, it looks you're giving MySQL a non-closed polygon. MySQL is happily accepting it and inserting null into the table instead. Here's an example:

CREATE TABLE testing (geom geometry);
INSERT INTO testing (geom) VALUES 
    (PolygonFromText('POLYGON ((0 0, 0 1, 1 1, 1 0, 0.00000003 0))'));

Running a SELECT on this table shows that the geometry field is null. However, fixing the polygon results in a successful INSERT:

INSERT INTO testing (geom) VALUES 
    (PolygonFromText('POLYGON ((0 0, 0 1, 1 1, 1 0, 0 0))'));

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.