MapInfo Pro

Sometimes, well often actually, I need a sub-selection of data on a map. To avoid making a new base layer which could become obsolete; here are the options I use:

1. Use an SQL query on a single table, adding it as a layer.
2. Use an SQL join (data or geographical) on two tables, adding it as a layer.
3. Conditional labelling: Left$(<label_expr>, Int(<condition_expr>)*Len(<label_expr>)))
4. ....... what have I missed, anyone got more?

Today I tried getting information from a geographical join, but only wanted the results that were inside an object from another layer. This gave an error. Here is a structure similar to what I tried. Is there a fix?

Select * from Table1 , Table2 where Table1.Obj intersects Table2.Obj within (Select obj from Squares where square="1") into MyResult

Hello Ross,

Can you give a plain language description of what you are trying to achieve? It might be easier to come up with something creative.

Here is an example with a Join and then sub-select. 

Select * from STATES,STATECAP where STATES.State = STATECAP.State AND OBJ Within (Select OBJ From WORLD Where Country = "United States") into Selection

I think the above is close to what you are trying to achieve.

Another thought is you may need to do this as two SQL's. Run the join first and then apply the filter. 

Here is a query where three tables are joined and then filtered.

Select * from US_CUSTG,STATES,STATECAP where STATES.STATE = STATECAP.STATE AND US_CUSTG.OBJ WITHIN STATES.OBJ AND US_CUSTG.ROWID = 1 into Selection

We have a few articles on using the sub-selecting technique which might be helpful: https://li360.pitneybowes.com/s/search/All/Home/sub-selecting 

Hope this helps! Thanks for participating in this community!

Here is a map screenshot showing my SQL and the error. I think, perhaps, that the geographical join is not compatible with a sub-select.

I've never used joins of three tables, or even sub-selects previously.

Hello again Ross,

What do you want return from this query? For the correct answer you seek, what would the Browser look like? How many rows would it have?

The problem you have is that after Table2.obj you will need an "AND" and have to say what (obj) is supposed to be evaluated against the within sub-query.

"Within" (or "Contains") returns whole objects, not portions of objects. if you are trying to return an object that is a portion of another object then you do not do this with geographic joins. Maybe you are trying to return the greenish area that is included in polygon A?

If that is the case what you might need to do is take a look at the AreaOverlap() function and maybe the ProportionOverlap() function.

Can you give a plain language description of the business problem you are solving? I'm not sure SQL Select is the best tool for the job here. What is your real data like?

There is a tool in the Tool Manager called Proportion Overlap. That might be useful here but I'm just guessing.

Hi Tom, here is a description of the result I'm looking for. I'll use colours instead of table names:

Give me the data of all blue objects, whose centroids are within Square #1, but only if they intersect yellow objects greater than 50 hectares in size.

I don't have real data to try this with so here is a go entirely from memory. Hopefully I have all my syntax correct. This is an attempt to do this in three sequential queries. You should be able to reproduce this in a workspace and thus meet your requirement of not saving any tables permanently.

Select * from Blue_objects_table where obj within (select Obj from square_table where square_ID = 1) into Blue_result

Select * from Yellow_objects_table where area(obj,"hectare") > 50 into Yellow_result

Select * from Blue_result where obj intersects any(select obj from yellow_result) into final_result

Do you feel it is proprietary or sensitive in any way to mention what sort of data you are really working with? Thinking in real terms instead of abstract terms might lead to different ideas for solutions.

Thanks Tom, this had interesting results.

If I put all three queries into a workspace using NotePad; I do get the desired result, being able to browse the final_result. However, if I resave the workspace, the third Select statement is automatically removed. Perhaps this is because it isn't using a base table as input?

Yes. You have that right. I should have warned you about that. Queries built on queries on not saved when you save a workspace so it requires some manual editing of the WOR file.