[GIS] MySQL: Query the envelope (mbr, extents) of a spatial table

mysql-spatial

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.

Best Answer

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

Related Question