Can I save a newly created Intersection layer directly into a Spatialite database in QGIS without importing?

Question

I have a point layer (xyz_coordinates_UTM33N) and a polygon layer (section1) in a SpatiaLite database.

I wish to extract the points within the polygon to a new layer, and I want that layer stored in the database.

I know I can create a new layer by using the Intersection tool, but how can I save the new point file without first saving it as a shapefile and then importing it into the database?

Using the "Save to SpatiaLite table" only creates a new database! How can I accomplish this without all the extra steps?

I tried and set the sqlite db as output, and I get an error ProgrammingError: Cannot operate on a closed database. is it same as you ? — Hugo Roussaffa, Nov 01 '17 at 11:28
No. When I chose the sqlite db as output I did get an output that appears in the Layers Panel but no points are visible in the Map View. — Alexander B, Nov 01 '17 at 11:57
2.18.13. No features from the original point file is in the new one. — Alexander B, Nov 01 '17 at 12:07

score 1 · Answer 1 · answered Nov 07 '17 at 09:03

You use the QspatiaLite plugin for QGIS. If will handle the creation of the spatial view. I use a building as an example of a polygon.

In the dialog you can write the SQL code doing the spatial join:

select *
from point p
inner join building b on st_within(p.geometry, b.geometry);

The spatiale view is dynamic in regard to it base layers. If you add point or a building the spatial view will be updated when you hit the QGIS refresh button.

score 0 · Answer 2 · answered Nov 01 '17 at 11:16

0

You got to create a view (="dynamic" query corresponding to your intersection) directly in the spatialite database.

Check Can QGIS read spatialite views ?

answered Nov 01 '17 at 11:16

Snaileater

5,743
1
15
26

Can I save a newly created Intersection layer directly into a Spatialite database in QGIS without importing?

2 Answers2