Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

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
add comment

Your Answer

 
discard

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.