2

I have a layer that consists of a number of features. Each features is a line segment and has an attribute named zone_name which takes one of 7 values. What I want to do is to use the atlas generation to print out a map for each zone (which consists of multiple features/lines) but (assuming I understand things correctly) the atlas generator iterates over all features in the layer -- first time I tried I got a large number of very small maps ;)

After a bit of research I came up with this:

SELECT zone_name,
     ST_Multi(ST_Collect(f.the_geom)) as singlegeom
     FROM (SELECT layer_name, (ST_Dump(the_geom)).geom As the_geom
                FROM
                tracks_master, layers where feature_gid = gid ) As f
GROUP BY zone_name

in the database manager SQL window. When I run the query it produces a table with a single feature for each value of zone_name. The only problem is that there is no integer id field so I can not load this as a layer. I.e. when I select "load as layer" there isn't anything valid to put in "column with unique integer values".

There must be a simple way of doing this but my SQL isn't up to it.

5

This should work:

SELECT row_number() OVER () AS id, 
     zone_name,
     ST_Multi(ST_Collect(f.the_geom)) as singlegeom
     FROM (SELECT layer_name, (ST_Dump(the_geom)).geom As the_geom
                FROM
                tracks_master, layers where feature_gid = gid ) As f
GROUP BY zone_name
  • And indeed it does! Thank, I keep forgetting about functions. – Russell Fulton Nov 11 '13 at 8:47

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.