this question provides a solution to get the evelope (aka: mbr, aka: extents) of a spatial table in PostGIS, but I'm having trouble doing the same in MySQL.

The ogr MySQL driver does this, and a solution is noted here on a Ruby forum (circa 2006), but surely this doesn't require external programming? Does anyone have a clean SQL recipe for this case?


After getting pointed in the right direction by @GIS-Jonathan, the working query ends up looking like what follows. CAVEAT: You may not need to change the group_concat_max_len value for a small table, but large tables with many features may need the larger group_concat_max_len buffer. It seems safe to me to just increase this value in the session as part of the solution.

# increase group_concat_max_len to be safe

SET SESSION group_concat_max_len = 104448;

# air8 is my geodata table
# shape is the geometry column

        Concat(Concat("geometrycollection(", Group_Concat(asText(shape))),")")
  AS extent FROM air8;

[Concluding Remark]

I was curious how well this would scale against larger tables and found that it starts to break down around some upper limit I don't have the time to identify.

For a table with 60864 features, I had to use SET SESSION group_concat_max_len = 10444800;, then it worked (that's up to about 10MB). However, for a table with 242439 features, I tried 104448000 (about 100MB), then 1044480000 (about 1GB), then most rediculously 10444800000 (about 10GB). At that point I quit caring if it would eventually work because surely that violates sensible practice!

Anyway here is the take-home message: If you run this on a large dataset and it returns null, you may very well be hitting the upper limit.

1 Answer 1


I believe this thread may answer your question. It doesn't look like the simplest solution, but it may give you a starting point.

  • Hey thanks, that grab looks good. I'll give it a shot and return to accept assuming it's on the money. /E
    – elrobis
    Apr 17, 2012 at 14:00
  • Yes that worked great. I'll update the qustion to include the query I used. Thanks for the fast help.
    – elrobis
    Apr 17, 2012 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.