My task is to work with bird observation data in an area, that is divided into zones as different polygons (A1, A2, etc.).

I have the hand-written lists of observations and want to bring all entries into a database. My idea was to create a point feature somewhere in the corresponding zone for each line, and then enter all data that is provided (Species, number, begin of observation, end of observation, etc ...) with a custom form.

I also need to have the polygon ID in the data, which I of course could enter manually, but I would rather prefer to learn how to include it automagically by querying to position of the point over the polygon and extract the polygon ID somehow.

As I want to work with a spatialite database, i thought about creating a trigger that queries the polygon the new point is created above and include the ID in my point data.

My problem: I have no idea how to start and can't find examples on that.

Anyone some ideas?

Thanx Bernd

My areas and some points

  • does your observations have x,y coordinates? – vinayan Aug 6 '13 at 10:52
  • The subject is a little more complicated: Our bird guys are observing the area from different viewpoints outside of the area and try to write down where they have seen the birds for how long. In addition to that, they try to mark the routes on a paper map, but as this all has to be done very quickly and with multiple birds at the same time, all this stuff is not very accurate. The idea is to calculate the presence of species in an area with relation of the time seen and the time observing in total. My points represent just an observation with a vage position. – Bernd V. Aug 6 '13 at 17:50

Here you are: If you have a polygon table in a SpatiaLite database named "polys" with an id column, "poly_id", and a points table "pts", also with a column "poly_id, then add a trigger as follows:

UPDATE pts SET poly_id=(SELECT poly_id FROM polys WHERE ST_Contains(polys.geometry, pts.geometry));

Now, each time you insert into the point layer (and save edits), the poly_id column will automatically get the value of the poly_id from it's containing polygon.

HTH, Micha

  • Thanx a lot Micha. Tryied it quickly yesterday evening, and distroyed my database ;) So as a quick feedback and as a reminder for myself: Always back up your databases ;) No harm done, it was just quick test db. Will report back after being successfull. – Bernd V. Aug 6 '13 at 10:07
  • Phew: Hours of testing, but i can't make it work. Always get this error "Could not commit changes to layer testlayer Errors: ERROR: 3 feature(s) not added." My trigger looks like this: CREATE TRIGGER update_poly_id AFTER INSERT ON testlayer FOR EACH ROW BEGIN UPDATE testlayer SET poly_id = (SELECT poly_id FROM quadrant WHERE ST_Contains(quadrant.geometry, testlayer.geometry)); END – Bernd V. Aug 12 '13 at 16:07

I would recommend to use a view instead of a database trigger. The simple reason is that when the polygon layer is updated, all point attributes in the view will still be correct. Instead, if you use a trigger, changes will not be updated. Of course it could be that that's the behavior you want - then triggers would be fine - but it still causes duplication of information in your database.

  • I can imagine that views would be better, but my knowledge on this topic is even less than on triggers and i was happy that i knew what triggers are, that i tried to go this way. Changing of polygons is not likely in my case, otherwise all survey data would become uncomparable, with the duplication of data i can live. I am just at the beginning to learn how to use all this stuff, so every little success i great for me, no matter how unelegant it might appear to pros. Already ordered a book on sqlite to hopefully get the non-spatial background explained at least. – Bernd V. Aug 6 '13 at 10:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.