Help with intersections in QGIS

Question

I have a shapefile which defines for me the area of each oil field in the North Sea (~300 fields). I also have a shapefile which defines the location of every well that has been drilled (~13000) wells. I am looking for a way to associate the wells to the fields.

I have managed to do this for a single field by splitting the oil fields shapefile into a shapefile for each oil field, then taking the intersection with the wells shapefile, and copy-pasting the attributes table out. It will take me a very long time to do this for 300 fields!

Is there a better way?

Thanks.

Answer 1

I like to use Spatialite's virtual tables for tasks like this. Here's a guide on how to set them up:

http://www.gaia-gis.it/gaia-sins/spatialite-tutorial-2.3.1.html#t6

Once you make virtual tables you can run any spatial sql query you want on them. You'd end up with something like:

UPDATE wells SET oil_field_name = (SELECT fields.name 
FROM wells, fields WHERE ST_Intersects(fields.geometry, wells.geometry)