2

Background: I have a bunch of (huge) SHP-files that I would like to merge to one big SHP-file that I can use for geo-processing (clip, etc.). Now I was able to merge a big part of the SHP-files to one big ~ 400 MB SHP-file (Note: This big SHP-file is the result of merging many smaller SHP-files). But this is still incomplete, I would need to merge that big file with a couple of other SHP-files. Unfortunately this does not work in QGIS, it keeps crashing (minidump). I suppose it's because of the file format (not able to handle that amount of data). So I thought maybe it would be possible to add these SHP-files to a Spatialite DB in QGIS and then merge them there somehow. But I don't know exactly how to continue. Maybe there is an easier way to achieve the result

Question: Is there a way to merge these (SHP-)files in QGIS-Spatialite or just QGIS so I can use them with other Layers (SHP-files) for exampling for clipping? I know that theoretically I could use the SHP-files that I want to clip with other SHP-layers one by one, but it would be nicer to have all the data in one layer/file/DB and only clip once. All the SHP-files contain polygons. Edit: Basically the goal is to be able to geoprocess the data comfortably (clip, difference, etc.).

Below is some information from the attribute table from two huge SHP-files that I would like to merge (Merging SHP in Qgis does not work, it crashes):

SHP-file 1: Has one row, file size is 700 kB SHP-file 2:Around 35,000 rows, file size is ~ 400 MB

Important Edit 14.8.18:

So, now I loaded all the many and partly big SHP-files (or the data from them) into a Spatialite data base (or a *.sqlite file). I have them there, I guess they are called "tables" now? I still would like to join/merge/dissolve (what's the right word?) all these different layers/tables into one layer/table that I can use to run geoprocessing commands like clip or difference with another layer in QGIS (in this case a SHP-file layer). I could do it one by one and use the result layer for each layer/table, but the goal is to have it done in one round. I thought that's what all this fancy software is made for? If it helps all the tables/files/layers in my Spatialite DB contain a "geom" -column that says "MULTIPOLYGON" in each row.

I try to do something with st-Union as described for example here, but I can't get it to work. Can anybody help or give a hint?

Edit 15.8.18: I was able to merge two layers in Spatialite that contain the same amount of columns with this SQL code (found it here):

CREATE TABLE merged AS SELECT * FROM layer1 UNION ALL SELECT * FROM layer2;

But I still have other layers that I want to merge and they have a different number of columns which creates this error: "SELECTs to the left and right of UNION ALL do not have the same number of result columns".

Also I'm not sure how to add the other layers, I tried this code and then got the error message aobve:

CREATE TABLE merged AS SELECT * FROM layer1 UNION ALL SELECT * FROM layer2 UNION ALL Select * FROM layer3;

The final layer (merged from all the individual layers) can continue all columns. Important is that it can be used with QGIS' geo-processing tools with other (SHP)-layers.

Improve this question
3
  • 1
    In doing Merge vector layers on shapefiles, haven't you selected temp layer as output and/or kept the tick mark Open output file after running algorithm on? (Sorry if this is an obvious question. The file size 400MB is big but I feel QGIS-merge tool is still workable if we simply output the merged file.) Commented Aug 15, 2018 at 7:21
  • Thanks for the reply. I selected to create a new shape file and tried it wit ticking and unticking the box "Add to map Canvas". So I used Vector > Data Management > Merge Shapefiles to one. Edit: Right now I'm trying to merge the layers in Spatialite. Commented Aug 15, 2018 at 7:40
  • Edited the original question with some additional information regarding my attempts in Spatialite. Commented Aug 15, 2018 at 7:57

1 Answer 1

Reset to default
2

Seems there is no automatic/comfortable way. In the end the solution was to create a new table and manually select which columns I want to take from each layer/table. The highest amount of columns was 7, so I made the other tables with less columns create empty columns so that the program does understand it. That was what I thought and I applied the code below:

CREATE TABLE merged AS SELECT column1 AS column1, column2 AS column2, column3 AS column3 FROM layer1 UNION SELECT column1 AS column1, column2 AS column2, column3 AS column3 FROM layer2 UNION SELECT column1 AS column1, column2 AS column2, null AS column3 FROM layer3;

Then I recovered the geometries so that it can be loaded to QGIS (polygons):

SELECT RecoverGeometryColumn('merged','geom',[EPSG-Code], 'MULTIPOLYGON', 'XY');

Note: 'merged' is the name of the created layer/table; remove the brackets [] and enter the desired EPSG-code.

The problem with this new layer is that it has somehow only the columns from layer1 and the data in the columns isn't in correct order/correctly assigned. So this didn't help to transfer the data from the columns to the new table.

Anyway, I have a layer that can be used for geo-processing commands. So just merging/inserting the id and the geom column (occurs in all my source layers) of my layers is actually enough (without the nulls).

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.