1

I have two tables in my SpatiaLite/SQLite3 database. One contains a number of points with no other data, and the other a number of polygons with associated columns of values.

ID GEOMETRY
1 point geometry blob
2 point geometry blob
3 point geometry blob
... ...
ID field GEOMETRY
x value1 polygon geometry blob
y value2 polygon geometry blob
z value3 polygon geometry blob
... ...

I want to add a new column to the table with the points, into which the value for one of the columns from the other table is added for each point, based on the polygon with which the point intersects. Essentially, in more GIS-y terms, I want to point sample my polygon layer.

so if point 1 intersects with polygon x, and points 2 and 3 with polygon y, I want the result to be:

ID GEOMETRY field
1 point geometry blob value1
2 point geometry blob value2
3 point geometry blob value2
... ...

(The reason to do this in Python + SpatiaLite instead of QGIS is because I need to be able to automate it so I can apply it in a QGIS-plugin I'm building, and the point sampling in QGIS is, in my opinion, unacceptably slow for the simple thing I'm trying to do. With some research, I found SQL would be the fastest way without essentially having to switch to C/C++- but feel free to tell me if I'm wrong.)

I know I will likely need some combination of ADD COLUMN with a condition based on INTERSECTS or perhaps WITHIN, however I have no idea of how to string this together so I can pass it to my cursor.execute() in Python to be processed. I have not been able to find an example of anyone attempting this.

Versions are Python 3.7 (with SQLite3) and SpatiaLite 0.0.3

UPDATE: I have managed to add a column with the right name to the database with points using ALTER TABLE table1 ADD COLUMN [field] [field type] . So my first table now looks like this:

ID GEOMETRY field
1 point geometry blob
2 point geometry blob
3 point geometry blob
... ...

all I need now is to UPDATE the column with the values from table2 based on an intersection between the points of table1 and polygons of table 2. Something like UPDATE table 1 SET table1.field = table2.field WHERE INTERSECTS(table1.geometry, table2.geometry) This obviously doesn't work, but I hope I'm on the right track.

UPDATE 2: The following fills the previously made column, however it is so slow that it effectively does not function, and desperately needs the inclusion of a spatial index, which I am still working out:

UPDATE table1 SET field = (SELECT field FROM table2 WHERE INTERSECTS(table2.GEOMETRY, table1.GEOMETRY));

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.