Akam Hasan Sharkar Akam Hasan Sharkar - 3 months ago 35
MySQL Question

MySQL GeomFromText with Google Map Latitude and Longitude

I am working with MySQL GEOMETRY and Google Map Javascript API.

I created an MySQL table where I have a field

delivery_loc_1
which is
POLYGON NOT NULL
.

Trying to insert/update Google Map polygon latitude and longitude and it's failing every time with error
Warning: #1048 Column 'delivery_loc_1' cannot be null


My update query is :

UPDATE `zone` SET `delivery_loc_1` = GeomFromText('POLYGON((-118.33309699999995 33.923032,-118.33509700000002 33.915032,-118.32709699999998 33.916032,-118.331097 33.919032))',0) WHERE zone_id = '1';


I found a MySQL polygon online and tried with this :

UPDATE `zone` SET `delivery_loc_1` = GeomFromText('POLYGON((-73.96509524 40.80721011,-73.95319048 40.80721011,-73.95319048 40.7982011,-73.96509524 40.7982011,-73.96509524 40.80721011))',0) WHERE zone_id = '1';


And this one works perfectly, seems like Google Map Latitude and Longitude not in a proper format for MySQL GEOMETRY.

Is there any MySQL function to convert Google Map values into MySQL polygon?

Thanks in advance

Answer

I believe that the first and last points of a POLYGON have to be identical, as this is how MySQL "knows" that the shape has been enclosed and defined correctly.

In your UPDATE query:

UPDATE `zone`
SET `delivery_loc_1` = GeomFromText('POLYGON((-118.33309699999995 33.923032,
                                              -118.33509700000002 33.915032,
                                              -118.32709699999998 33.916032,
                                              -118.331097 33.919032))',0)
WHERE zone_id = '1'

you specified four points, but the first and last do not match. Assuming that you want to retain all these points, meaning that you were trying to define a five-sided polygon, you could try the following query:

UPDATE `zone`
SET `delivery_loc_1` = GeomFromText('POLYGON((-118.33309699999995 33.923032,
                                              -118.33509700000002 33.915032,
                                              -118.32709699999998 33.916032,
                                              -118.331097 33.919032,
                                              -118.33309699999995 33.923032))',0)
WHERE zone_id = '1'