There's more to experience when you log in!
Over the coming weeks, I'll write a #series of articles about the #SQL improvements we have made in MapInfo Pro v2019. This will, of course, look at the Select statement but I'll also dive into the other improvements.
It's not all about the SQL syntax improvements, in this article, we'll dive into the new Select By Location feature.
Some, or maybe even many, users might struggle a bit building a spatial join using the SQL Window and the classic SQL Select dialog. It can be difficult to get it right, especially if you also are new to writing SQL in general.
That why we created a dedicated dialog to help you build your spatial joins. With this dialog, you don't need to worry about the SQL syntax, the dialog will create this for you. The dialog will also guide you to pick the right spatial operator, that is the way to compare the two tables spatially.
When you activate Select By Location, this dialog will be shown
As you start selecting tables from the two dropdown lists, the Relation dropdown will also change to match the most common object types in the two tables. It will preselect the best match depending on the object types in the two tables and the data bounds of the objects in the tables. This will help you select the best spatial relation and help you avoid selecting a relation where you want the points in one table to contain the polygons in another table.
The dialog will also give you access to a spatial relation that hasn't been supported in earlier versions of MapInfo Pro: Within a Distance. This will make it possible for you to find relations between objects that don't touch each other but just are nearby.
When you use the Within a Distance spatial relation, you will need to specify the distance used, the distance unit and also the Distance Calculation Method that can be Spherical or Cartesian. Spherical is used for Lat/Long data and Cartesian is used for projected data, which is data based on for example a UTM coordinate system.
Notice that this is only possible in MapInfo Pro v2019. Earlier you could only build a join based on a spatial operator or the equal sign. And you would have to use existing columns or the object on both sides of the comparison.
If you have selected one or multiple records before accessing the Select By Location dialog, the Compared to objects in dropdown will be prefilled with the selection. The dialog assumes that you want to use the selected objects to find objects in another table.
All you have to do now is to select the table you want to "compare" to the selected objects and the spatial relation.
When you click on the OK button MapInfo Pro will build the SQL Select statement and run it. The result will get selected into the current Selection and highlighted on the map.
If you have the MapBasic window open, you can also see the SQL Select statement here. You can modify the statement and rerun the statement directly from the MapBasic window. In the example above do notice that the join is based on a temporary query, Query1.
You can, however, also access the query in the new SQL Window. You can find the Select statement in the Scripts dropdown at the top of the window. The statement will be named using the two tables and the spatial relation used. You can select it from the dropdown, and then edit and rerun it from the SQL Window.
You need to have two base tables open for the Select By Location control to get enabled. It will not allow you to build a join on the same table or queries based on the same base table. This might change in the future. If you think it would be really valuable to be able to do this, let us know, for example via a comment to this post.
If you try to build a join based on the same table or queries of the same table, the dialog will ask you to build a different join.
I hope you can see the value of this new feature. Do let us know how you are using it and also give us some feedback if there are things you think could work even better.
Stay tuned for the next article on the SQL improvements in MapInfo Pro v2019!
Yeah, I suspect we need to consider this wisely. We don't want to make this too complicated to use with too many options.
As you said, you can always take the initial query into the SQL Window and here make it more advanced. I haven't tried it but I'd think you can add an additional condition to your statement like this
Select * From Table1 As "A", Table2 As "B"
Where A.OBJ Intersects B.OBJ
And NOT A.OBJ Entirely Within B.OBJ
I might write up a more detailed posting on this, but here is a mapbasic script I created in the SQL window.It selects all the object that intersect the selection not including the selected objects themselves.It basically achieves the same effect as a spatial relation on the same table.It uses the new spatial aggregate to combine the objects into a variable and then uses that in the main select statement.It then add a layer into the map and zooms to it.
Dim selAlias As String
Dim selObj As Object
selAlias = SelectionInfo(SEL_INFO_TABLENAME)
Select AggregateCombine(obj) From Selection Into tempSelection NoSelect Hide
Fetch First From tempSelection
selObj = tempSelection.obj
Select * From selAlias
Where obj Intersects selObj And Not obj Entirely Within selObj
Into selIntersects NoSelect
Add Map Auto Layer selIntersects
Set Map Layer "selIntersects" Display Global
Set Map Layer "selIntersects" Global Pen (3,2,11559167) Global Brush (5,11030783)
Global Line (3,2,11030783)
FriendlyName "Neighboring Polygons"
Set Map Zoom Entire Layer "selIntersects"
Close Table tempSelection
Save this to a .mbs file in your Saved Scripts location and then you can run from the sql window or mark it as a favorite to easily access it from the Ribbon dropdown.-Bob
Dim selAlias As StringDim selObj As ObjectselAlias = SelectionInfo(SEL_INFO_TABLENAME)Select AggregateCombine(obj) From Selection Into tempSelection NoSelect HideFetch First From tempSelectionselObj = tempSelection.objSelect * From selAlias Where obj Intersects selObj And Not obj Entirely Within selObjInto selIntersects NoSelectAdd Map Auto Layer selIntersectsSet Map Layer "selIntersects" Display GlobalSet Map Layer "selIntersects" Global Pen (3,2,11559167) Global Brush (5,11030783) Global Line (3,2,11030783) FriendlyName "Neighboring Polygons"Set Map Zoom Entire Layer "selIntersects"Close Table tempSelectionUnDim selAliasUnDim selObj
I think Bob was using a non-overlapping dataset when he created this script. A dataset like post areas or admin areas. I guess for these it will work.
From SomeTable, AnotherTable, TheFinalTable
Where SomeTable.ID = AnotherTable.ID
And AnotherTable.AnotherID = TheFinalTable.ID
ProportionOverlap( object1, object2 )
AreaOverlap( object1, object2 ) / Area( object1 )