1

We have a table in MariaDB that contains a lot of point of interest. These POI have a list of numerical attribute such has occurrence, weight, etc. We are looking for a way to aggregate these POI by tile (bounding boxes) to finally display the result on a map (google maps).

Is it possible to do so in a request like : SELECT SUM(...) GROUP BY bounding_boxes?

*Note that we can use PostGIS if there's no way to accomplish these kind of request with MariaDB. (but I don't even know if it is possible)

1

As MariaDB is a fork of MySQL, I believe it has al the same spatial functionality, which is considerably less than Postgis, but sufficient for your query. You can't GROUP BY a bounding box as such, but you can summarize attributes where the geometries bounding boxes intersect some input geometry. In MySQL/MariaDB, functions that operate on bounding boxes lack the ST_ prefix, see the Contains or Intersects functions. If you want exact spatial containment/intersection, use ST_Contains/ST_Intersects.

So, you could write your query along the lines of:

SET @g1 = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
SELECT SUM(some_attribute) FROM some_table WHERE Intersects(geom, @g1)=1;

assuming that your geometry column is called geom.

Note in MySQL you can't use Contains/Intersects on two columns to find all intersections as you can in Postgis, so you would have to run this query for every bounding box geometry you wanted to summarize by. ie, you CAN NOT write:

SELECT attribute from some_table A, some_table B
WHERE ST_Intersects(a.geom, b.geom);

You could write a stored procedure/function in MariaDB that would iterate through each of your bounding boxes providing them to ST_Intersects or Intersects (for MBR) one at a time, but, in my opinion, if you have access to Postgres/Postgis, you will find it much easier to get the output your are looking for.

  • Thanks for your time! In fact, I have a lot of bounding boxes so I was wondering to find a way to group by many tiles (bounding boxes)... Do you think that these kind of requests are available with PostGIS? – rattek Feb 2 '16 at 13:38
  • In Postgis, yes, in MySQL/MariaDB, I don't think so, because you basically need to do something like ST_Intersects(a.geom, b.geom) GROUP BY b.geom where b.geom would the bounding boxes and MySQL forces you to have a constant geometry as one of the inputs. To be honest I haven't worked on MySQL spatial for a few years now and this was one of the reasons why I migrated. So, it is possible that it has changed, but from looking at the docs, I don't think so. – John Powell Feb 2 '16 at 14:04
  • I added an edit to the answer as well. I have written functions in MySQL to get round the lack of an Intersects function that works on groups of geometries, ie, Intersects(geom.a, geom.b), but it was a somewhat painful experience. – John Powell Feb 2 '16 at 17:23
  • Good to know that it's easier with PostGIS? Can you quick describe the way to accomplish the tiled aggregation? – rattek Feb 2 '16 at 17:58
  • Can I just be totally clear on the question first. You want to summarize the geometries in one table that are intersected by some bounding boxes in another table? – John Powell Feb 2 '16 at 18:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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