For years I have been hacking my way through writing queries in Manifold, but I can never seem to get to the level some of you are at (and some of my coworkers). I decided to sit down and really try to teach myself but i keep coming to the same road blocks. Examples in the help files are very simple, yet advanced SQL help online elsewhere dont specifically address spatial information.
I can write a query like this:
SELECT fields.* from fields
INNER JOIN barns ON Distance(fields.id, barns.id) < 500;
But thats as far as I can get in terms of complication. What i really wanted to do was insert into the Barns drawing, how many field parcels were within 500 m (both containing and touching or partially intersecting). I also wanted to know what percentage of the 500 m around the barn was occupied by field parcels (as opposed to say forest or something else) so i could find the barns with the highest percentage of fields in their 500 m radius.
So what I did do was really quite stupid but worked: I made a buffer around the barn centroid of 500 m, converted it to a line, split the fields drawing by that buffer line and then added a column for both that said "count" with values of 1 for all field parcels. Then I did a spatial overlay using the Sum transfer so that the field parcels that were in the original buffer polygon would add up to however many were within it, and then related that value back to the original barn drawing from the buffer drawing. This could have been very easily handled through a query - but my SQL skills are clearly lacking, and therefore, I am looking for ways to improve them.
Does anyone have any suggestions for me? I am wondering how one gets good at these things on their own - even in this forum going back many pages, I have seen lots of SQL questions, but very few involve spatial geometries
Thank you so much for any advice