Split vector layer in PostgreSQL/PostGIS

Question

QGIS has this nice tool to Split a layer into multiple layers based on one attribute and create a new layer for each unique value within that attribute.

Is this also possible using PostgreSQL/PostGIS? What would the SQL code look like?

Edit based on comments:
I do understand this goes against the idea of a RDBMS. Creating views is indeed a more handsome way.

I do need the multiple layers for one single reason. I am building a webmap where the legend should show the individual names of the polygons that are part of a set (the all have the same value for atttribute1, consider regions). For example: The original layer has the features 1,2,3,4, etc. with attribute1 is A, B, C or D, etc.

The new layers consists only of those features that have the same value in attribute1, but I do not want these features to be dissolved into one new feature/polygon. Then I put all those separate layers in a new map and style each layer with 'categorize on attribute2' but all with the same color so each feature within a layer all have the same color and each layer has a different color. Then the legend of that map looks like this:

Layer A
- 1
- 2
- 4
- 7
Layer B
- 3
- 5
- 6

I can do this within QGIS by using Spilt Vector Layer, and QGIS the creates six new files for each layer. Doing this with views in a database would be more convenient.

very likely you wouldn't want that. there is a plethora of DB functionality to make e.g. selections based on 'attributes' as fast, or even faster, without the need to physically separate data. you need to be more specific about your scenario; please add (edit) that to your question. — geozelot, Apr 17 '19 at 21:17
If "layer" means "display object" within QGIS, then this can be useful, but if you mean "new table" or "new view", then you really ought to change meanings. — Vince, Apr 17 '19 at 22:29

score 3 · Answer 1 · answered Apr 18 '19 at 13:13

The requirement sounds strange... very strange, as it is conceptually the opposite of RDBMS theory...

That being said, if you really needs to do it, I would suggest creating views and not tables, so the data remains centralized.

You can make use of psql /gexec command that allows to dynamically build and execute a query. Note that you are stuck using double quotes in the name, as myAttribute could contain space, upper and lower cases etc.

SELECT format('CREATE VIEW "v_myLayer_%s"  AS SELECT * from myLayer where myAttribute= %L ;', myAttribute, myAttribute)
    FROM myLayer
    GROUP BY myAttribute;\gexec

Split vector layer in PostgreSQL/PostGIS

1 Answers1