MySQL: Query the envelope (mbr, extents) of a spatial table?

Question

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?

---

[Answer]

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
SELECT 
 asText(
 Envelope(
 GeomFromText(
 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.

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.

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