MapInfo Pro

Expand all | Collapse all

The new Select By Location feature

  • 1.  The new Select By Location feature

    Employee
    Posted 01-22-2020 21:00

    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.

    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.

    You can find Select By Location in the new SQL dropdown on the TABLE, MAP and SPATIAL tabs. The shortcut key for the feature is Ctrl+Shift+X. If you use it a lot, you can also add it to your Map Mini Toolbar.


    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.


    You can always change the spatial relation if you want a different one. The dialog will not prevent you from selecting the specific spatial relation that you want to use. But it will preselect the spatial relation that it thinks would work best.

    Within a distance

    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.

    Using a Selection

    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.


    Notice that this is only possible in MapInfo Pro v2019. In earlier versions of MapInfo Pro, it wasn't possible to join a temporary table to another table.

    Viewing the result

    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.


    Joins based on the same base table is not possible

    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!



    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data
    ------------------------------


  • 2.  RE: The new Select By Location feature

    Posted 01-23-2020 05:09
    Hi Peter,

    Thanks for keeping us informed of the latest SQL and other improvements.  I like this one and many others.  Just wondering: if "Spherical is used for Lat/Long data and Cartesian is used for projected data", then could the Distance Method be determined automatically?

    I think that comparing objects from the same base table would definitely come in useful.  It is something I often do.  In this case you might want to compare each object in the table to all other objects in the table.  In such a spatial join I usually include a "Table1.ID <> Table2.ID" in the Where clause to make sure each object is not compared with itself.  I'm not sure how this would be achieved in this dialogue if the aim is simplicity though.  Some improvements however might be to allow the selection of multiple Relations and allow negation of Relations (e.g. Not Within).


    ------------------------------
    James Nolet
    Dooley Mitchell & Morrison Pty Ltd
    Mentone, VIC, Australia
    ------------------------------



  • 3.  RE: The new Select By Location feature

    Employee
    Posted 01-23-2020 05:47
    Thanks, James.

    Yeah, we should be able to determine that. We did consider it but I'm not sure why we didn't do it. Maybe @Michele Buselli knows?

    I think we want to keep this dialog simple. But maybe you can use the new SQL Window to build such a query​. But currently, the limitation basically is that we do not allow join between the same base table. I just select all records from one table and tried joining the Selection with the base table. That didn't work, as I feared. Maybe another limitation we need to take a look at removing? But you can still create a copy of the table and use that in your query.

    And Not in combination with a spatial operator is also still not possible. You can do this via a subselect, however.

    Can you give me an example of "multiple Relations"? Is that between more than two tables?


    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data
    ------------------------------



  • 4.  RE: The new Select By Location feature

    Posted 01-23-2020 08:46
    Looks great! I would echo James in saying that negation is the toughest bit to get your head round, so if that could be in this wizard in future that'd be great.

    ------------------------------
    Martin Burroughs
    Oldham Council
    ------------------------------



  • 5.  RE: The new Select By Location feature

    Posted 01-23-2020 09:55
    Hi James & Peter,

    When spherical is selected you'll notice in the MapBasic window that we actually use the ObjectDistance() function instead of SphericalObjectDistance().  The advantage of using ObjectDistance() is that it already has the support to determine whether it should use a Spherical or Cartesian calculation method under the hood.  Selecting Cartesian however explicitly uses CartesianObjectDistance() and if the CSys isn't projected  you'll get an error. This is documented in the help as well.

    ------------------------------
    Michele Buselli
    Pitney Bowes Software Inc.
    ------------------------------



  • 6.  RE: The new Select By Location feature

    Posted 01-27-2020 20:51
    Hi Peter,

    You might like to find objects that intersect but are not entirely within, as in Bob's script.  Or objects that are within a distance but not intersecting.  But this can be left to the advanced SQL Window I imagine (or an Advanced version of the Select By Location?)

    ------------------------------
    James Nolet
    Dooley Mitchell & Morrison Pty Ltd
    Mentone, VIC, Australia
    ------------------------------



  • 7.  RE: The new Select By Location feature

    Employee
    Posted 01-28-2020 06:03

    Hi James,

    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 

    Need to verify if you can use NOT for the second spatial condition.

    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data
    ------------------------------



  • 8.  RE: The new Select By Location feature

    Posted 01-28-2020 08:10
    Thanks Peter.  I'll have a go when I come across a situation that requires this (and when I upgrade).

    ------------------------------
    James Nolet
    Dooley Mitchell & Morrison Pty Ltd
    Mentone, VIC, Australia
    ------------------------------



  • 9.  RE: The new Select By Location feature

    Employee
    Posted 01-23-2020 09:49

    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
    UnDim selAlias
    UnDim selObj
    


    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



    ------------------------------
    Bob Fortin
    Software Architect and Distinguished Engineer
    MapInfo Pro Development Team
    ------------------------------



  • 10.  RE: The new Select By Location feature

    Posted 01-27-2020 20:33
    Hi Bob,

    Thanks for your script.  I am unable to test because I have yet to upgrade, but this may fail where you have intersecting polygons that happen to fall entirely within the combined selection but are not actually part of the initial selection.  They will be left out of the results won't they?

    ------------------------------
    James Nolet
    Dooley Mitchell & Morrison Pty Ltd
    Mentone, VIC, Australia
    ------------------------------



  • 11.  RE: The new Select By Location feature

    Employee
    Posted 01-28-2020 06:06

    Hi James,

    Well spotted.

    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.



    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data
    ------------------------------



  • 12.  RE: The new Select By Location feature

    Employee
    Posted 01-28-2020 18:02
    That is correct. I will take a look at overlapping regions.

    ------------------------------
    Bob Fortin
    Software Architect and Distinguished Engineer
    MapInfo Pro Development Team
    ------------------------------



  • 13.  RE: The new Select By Location feature

    Posted 01-23-2020 14:01
    Peter - This is exactly what I was looking for in the new version - it was very cumbersome to have to save a temp table, re-open and then use in a SQL.  One question is whether spherical or Cartesian best represents a route between 2 airports (e.g., could I look at which airports are within xx flight miles of an airport)?  A couple other features of that would be useful (and may be available) are:  (1) linking 3 tables within a single SQL (I can in other apps but couldn't figure out in old versions); and (2) selecting shapes within another shape based on the share of the area within that shape.  For the latter, I often select zip codes within xx minutes of an airport and often have problems with the border areas as zip code shapes/centroids can be odd.  Think it would require a function that calculates the share of an object's entire area within the larger area.  Thanks again.

    ------------------------------
    Rex Edwards
    CAMPBELL AVIATION GROUP
    Alexandria VA
    ------------------------------



  • 14.  RE: The new Select By Location feature

    Employee
    Posted 01-24-2020 02:02
    Hi Rex

    A few suggestions for you:

    1. With the new SQL Window, that we will get back to in multiple posts, you can write a small script that wraps it all up. This makes it possible to easily 1. Save a copy of a table, 2. Open that table and finally, 3. do the query. You will be able to execute all statements by running the scripts from the SQL WIndow or from the new SQL dropdown.

    2. Cartesian distance is only used with a projected coordinate system. I'm assuming you have your data in a table using Latitude/Longitude. Here Spherical calculation is the only, and probably also best, option.

    3. You can already join multiple tables. Currently, the limitation is that you need to join the first table to the second table, the second table to the third and so on:

    Select *
    From SomeTable, AnotherTable, TheFinalTable
    Where SomeTable.ID = AnotherTable.ID
    And AnotherTable.AnotherID = TheFinalTable.ID

    4. It's already possible to select records based on the percentage of the overlap. We have a function called ProportionOverlap() that give you a decimal value depending on the size of the overlap:

    Syntax
    ProportionOverlap( object1, object2 )
    object1 is the bottom object, and it is a closed object.
    object2 is the top object, and it is a closed object.
    Return Value
    A float value equal to AreaOverlap( object1, object2 ) / Area( object1 ).

    Do note that the order of the objects matter.

    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data
    ------------------------------



  • 15.  RE: The new Select By Location feature

    Posted 01-31-2020 14:38
    Thanks for this Peter and thanks to all who have provided feedback!

    We've made some additional improvements for version 2019.1 based on feedback we received near the end of the 2019 release (but too late to make it into 2019) so I wanted to let you know what's coming - hopefully these improvements will be really useful even though they haven't (yet) been suggested in this thread.  Here's a screenshot with new controls circled:


    Starting with version 2019.1 you'll be able to specify a name for the query results using the "Into table:" field. This field will default to "Selection" (same idea as the SQL Select dialog). 

    Under the "Into table:" field is a series of buttons for different output options (these should look familiar if you've been using the new SQL Window in 2019). They are in this order:
    • Browse - to create a new browser for the query
    • Map - to add the query as a layer in the front map window
    • Color Override - to apply a color override to the query layer being added
    • Find Selection - to zoom the map to the new selection extents or make sure the selection is visible
    • No Select - to let the query be created without changing the current selection

    The new controls remember their last state so if you always map the query and apply a color override you don't have to keep selecting the same controls each time the dialog is opened. 

    I hope these improvements will make spatial joins even easier.

    ------------------------------
    Michele Buselli
    Pitney Bowes Software Inc.
    ------------------------------



  • 16.  RE: The new Select By Location feature

    Posted 02-03-2020 07:13
    Hi Michele
    Looks good - when is v2019.1 scheduled for release?

    ------------------------------
    John Ievers
    CDR Group
    Hope Valley, United Kingdom
    ------------------------------



  • 17.  RE: The new Select By Location feature

    Posted 02-03-2020 08:54
    HI John,
    We're targeting the end of March for 2019.1.

    ------------------------------
    Michele Buselli
    Pitney Bowes Software Inc.
    ------------------------------



  • 18.  RE: The new Select By Location feature

    Posted 02-03-2020 09:37
    Great, thanks Michele

    ------------------------------
    John Ievers
    CDR Group
    Hope Valley, United Kingdom
    ------------------------------