Tell me more ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 1 down vote favorite

I have some different kinds of geometry (Polygons and line strings) stored in my mysql database. On different rows.

I know I can retrieve the envelope of one row with: SELECT Envelope(zoneShape) FROM geometryTable WHERE refid=4

If there are three different shapes associated with reference id 4, i'm getting three results, obviously.

How can I (preferably with one sql query) get the envelope/MBR of all the geometry with e.g. refid=4?

share|improve this question

1 Answer

up vote 1 down vote accepted

I'm pretty sure that MySQL spatial does not yet support the (ST_ )Collect aggregate function which could be used in conjunction with the Envelope function by performing a GROUP BY on your refid to yield the envelope of the combined geometries.

http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html

Perhaps you could loop over each refid and use the Union function to do what you need.

If not, you can achieve the desired result using QGIS to dissolve the geometries based on your refid and then calculate the envelope(s) on the dissolved geometr(y)(ies)

share|improve this answer
Damn. This is the first time MySQL is not sufficient. – stUrb May 14 '12 at 18:05
MySQL is far from being a good choice for GIS. It is very slow and rather limited, probably because it is owned by the makers of Oracle Spatial, who would like to sell their flagship product and not the free DBMS. PostGIS is much better but if you want something lightweight, go with Spatialite. Both PostGIS and Spatialite outperform MySQL in GIS benchtests. If you are serious about GIS, ditch MySQL. – MappaGnosis May 15 '12 at 11:30
It's not my first choice to use MySQL for this project. But I do not own the server so I can't install Postgis or spatialite. – stUrb May 19 '12 at 23:13

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.