Subscribe to this thread
Home - General / All posts - Best resources to learn SQL specifically for spatial manipulation for Manifold
chrisschmidt1 post(s)
#15-Nov-22 18:10

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

artlembo


3,285 post(s)
#15-Nov-22 18:39

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.

RonHendrickson
278 post(s)
#25-Nov-22 17:13

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.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.