Greetings all,

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.

For example:

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

post a small .map, and we can take a look.

To answer your question about learning, well, that just takes time. And, I've found that sometimes you have to start with something basic, ensure it works, and then start adding more complexity. It will take time, but you'll eventually get it.

Also, I have a bunch of training materials on artlembo.com that might help. Just poke around there. On the blog page, I have a whole bunch of posts that replicate ARC/INFO commands using SQL.

As a consumer of many of Art's tutorials and training materials, I can testify that they are excellent. SQL is the way to go, and worth your effort to learn. As with anything like it, as you learn more and more the effort gets easier and really builds on itself quickly.

Thank you so much. I have been 'hacking' at SQL queries since 2007 now, and I feel like I can't get beyond a certain point. I will definitely look into Arts courses, I think that would be a worthy investment of my time.

Late answer....

Thanks so much Art, I had been on your website before and hoping to find some time where i can do this, and also support what you do.

I wanted to take you up on your offer and post the map, but in my field most of my projects are confidential (not a spy or anything, but our job sites tend to get attacked by conspiracy theory type people) to make sure anything i upload has any identifying features removed. And i havent had the time yet. The task i needed this for is long past, but I hope you dont mind if i upload something in the next few weeks (when i have time) and ask again.

feel free to contact me offline.

A useful resource for learning SQL within Manifold is the "Edit Query" button that's found on many panes and dialogs, for example, the Join dialog. Templates in the Select pane and Transform pane, and the function of some dialogs like the Join dialog, are implemented behind the scenes in SQL. The Edit Query button pops open the SQL being used.

What you can do is set up something similar to what you want (or parts of what you want) in the Select pane, the Transform pane, or the Join dialog, and then press the Edit Query button and a Command window pops open that is loaded with the SQL that accomplishes how you setup the template or dialog.

Sometimes that SQL can be a bit opaque to beginners, because of the way it generalizes the query to support varying situations in templates, but almost always it provides a very useful real life example of how to use specific functions to get a specific job done.

When you see how to do parts of what you want in SQL, you can often combine parts of those "edit query" examples to achieve what you want to do.