Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 1 down vote accepted

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

share|improve this answer
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 '12 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 '12 at 14:05

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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