Is there a way to union or collect any number of layer geometries with Spatialite (or another spatial database)? Two is simple enough, but more than that becomes convoluted. For example, if all I want is all the geometries of four layers, I need to do:

SELECT ST_Collect(ST_Collect(tbl1.geom, tbl2.geom), ST_Collect(tbl3.geom, tbl4.geom))
FROM tbl1, tbl2, tbl3, tbl4

Is there a function or approach which would allow for something like ST_Collect(tbl1, tbl2, tbl3, tbl4)?

  • As you can see from the documentation gaia-gis.it/gaia-sins/spatialite-sql-latest.html ST_Collect takes either one or two "geom". So tbl1, tbl2, tbl3, tbl4 clearly will not work. – user30184 Apr 25 '16 at 10:03
  • @user30184 Yes, I know this. I am asking for an alternative approach that would replicate the same idea. – ssast Apr 25 '16 at 11:08

Try UNION ALL for collecting all the geometries before running ST_COLLECT.

SELECT ST_Collect(geometry) FROM
(SELECT geometry from tbl1
SELECT geometry from tbl2
SELECT geometry from tbl3
SELECT geometry from tbl4);
  • This works and is more transparent than my existing method, which feels like a workaround, thanks. – ssast Apr 25 '16 at 11:11

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.