I've got a MySQL table with about 200,000 rows in it. The table has four columns, lat, lng, and value. value is just a percentage, and represents your chances of seeing an aurora in the night sky for a given lat and lng.

I'm wanting to reduce this data into a set of polygons that I can pass to some kind of mapping service (e.g. Google Maps) or pass to a script to draw it to a static image, and I'm wondering if MySQL can do this. All the MySQL spatial tutorials I've looked up deal mostly with point-to-point distance finding (e.g. where's the nearest pub to you?), so I'm a little confused.

Specifically, I'd like to group similar regions together, kind of like this:

A sample image showing contiguous numbers grouped together

So if I requested all the locations that have a 1% chance, I'd get back two records, one for the yellow region, and one for the light blue region.

The data that I'm inserting comes from a text file, 1024 values per line, and 512 lines (each value represents about 0.3515625 degrees) and the value is just a whole number between 1 and 100 (the data is mostly zeroes, so I ignore those to save time / memory / rows needed).

So is there a way for MySQL to do this? Or would I need to do this kind of reduction before inserting the data?

  • Shouldn't this be in a forum that discusses "algorithms? – Rick James Nov 12 at 6:58
  • MySQL may do this, of course, but it is not optimal. Or would I need to do this kind of reduction before inserting the data? - true. – Akina Nov 12 at 7:05
  • MySQL polygons are LINE-plots, not an area ... so I think your current representation is better suited than the geometry objects that mysql offers – eagle275 Nov 12 at 8:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.