MapInfo Pro Developers User Group

Expand all | Collapse all

Automation of Select by Region Shuffles Object Order

  • 1.  Automation of Select by Region Shuffles Object Order

    Posted 02-21-2020 15:07
      |   view attached
    Hello,

    I attempted to create a script that would automate the process of selecting by region (or manual selection) and Save Copy As. The intent was to split up large points files into smaller sections. The tool successfully selects and saves the points query, but the order of the points in the new table is shuffled.

    This issue only becomes apparent when in use with another tool I created previously, designed to highlight a range of sequential objects with a 'select' followed by 'shift + select' of two points (start and end of track segment).

    When using the tool (track_select.mbx) on the original point set it is possible to select point A and shift + select point B to highlight the range.
    After running the automated query and save copy as. Attempting to highlight the same range from the output brings a result like the one below.

    This type of shuffled order does not occur when manually selecting the points by region and saving. Only in the case of using MapBasic to automate the process do the points lose their relative order.

    I have attached the test data from the images above as well the two tools to recreate the process. The Track_Select tool will work on an editable table, but the Point_Split_Demo.mbx is hardcoded for this data set. I included a simplified version of the .mb file for the point split as well, but this is the splice of code doing work.


    Anyone have any ideas where or how this reording is occuring?

    Thank you



    ------------------------------
    Brandon Shepherd
    Knowledge Community Shared Account
    Shelton CT
    ------------------------------

    Attachment(s)

    zip
    AutoQueryDemo.zip   771K 1 version


  • 2.  RE: Automation of Select by Region Shuffles Object Order

    Posted 02-24-2020 03:36
    Hi Brandon

    By default, a table has no specific order to it. It has the order that the records have been inserted but when working with the data or browser the data, especially after querying the data, you can't trust that order unless you specifically request the data to be returned in a specific order using the Order By part of for example a Select statement.

    Typically, when query your data spatially the data is found using the spatial index which works differently than the normal record order you see in a browser.

    Would it be possible for you to specify a sort order so that you could make sure the data is in the correct order? This could be some sort of record ID that has been assigned to each record.

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



  • 3.  RE: Automation of Select by Region Shuffles Object Order

    Posted 02-24-2020 10:42
    Thank you for the quick response Peter.

    I must have glanced over the Order By parameter in the help file. Is it possible to order the selection simply by RowID, without making any changes to the table? I can experiment with this and report back some time later. Otherwise I could add a column and update it with the RowID before the queries.

    The part that interested me was that all methods of manual selection (polygon, marquee, boundary) mantained the record order. I assume now they just apply this Order By statement as default.

    ------------------------------
    Brandon Shepherd
    Knowledge Community Shared Account
    Shelton CT
    ------------------------------



  • 4.  RE: Automation of Select by Region Shuffles Object Order

    Posted 02-25-2020 02:24
    Hi Brandon

    I'd recommend adding a column to have a static ID that you can order by. Not even sure that you can order by the dynamic ROWID anyway.

    Yeah, it also surprised me that the manual tools were maintaining the order of the records. Maybe they work differently than the SQL based queries? I'd probably need our engineers to answer that question.

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



  • 5.  RE: Automation of Select by Region Shuffles Object Order

    Pitney Bowes
    Posted 02-25-2020 12:00
    ​Rowid is a bit of an odd pseudo-column in MapInfo Pro SQL.
    You can use it in a where clause using numeric operations (select * from table where rowid * 2 < 100)
    You can use use it in the column (projection list) with no error but we don't put it in the results.  (select col1,  rowId from table ) works fine but you won't see any rowId when you browse the result.
    Also if I do this query
    (select * from table where rowId > 100 and rowId < 200) into foo   (I apologize in advance if you see any "rowed" in my response. The autocorrect does not like "rowId")
    you have to realize that while this works correctly the RowID of the resulting query table is relative to the query. For example, in that query, if I then do this:
    fetch first from foo
    print foo.rowid
    I will get "1" and not 101 as it is the first record in foo
    RowId is relative to the query table.

    The other downside of Rowid that you probably know is that it is implicit. If you pack the table, RowiDs will change.
    As for the select tools, your observations seem correct to me. All those tools work the same way.  They don't explicitly use the Order By but that falls out of the way they work.
    Each tool does the appropriate spatial query. Even the arrow select or find tool is doing a spatial search just with a pretty small bounding box.
    The first part of the action creates a list of candidates. For arrow select or find, even the small bounding box may find data that is not "under the cursor". Or the marquee tool may find lines or polygons whose centroid is not within the box and the circle has to be processed separately. This first part list of candidates is sorted by RowID (they get inserted into the list that way) .
    Then the next part gets processed which is special for each tool. But any results which qualify are then kept in the list and that is why the  answers come back in rowID order when you use CTRL-Click for example.  Actually they come back in RowidOrder but also layer order. The list is therefore a list of layers with order RowIDs within them.  This allows CTRL-Click to drill down from each layer to each record in Rowid Order.
    I hope this explains what you are seeing well enough.
    I think your tool is behaving by the order in the 2 tables, regions and points.  But I have not had the chance to try it.



    ------------------------------
    Eric Blasenheim
    Spectrum Spatial Technical Product Manager
    Troy, NY
    ------------------------------



  • 6.  RE: Automation of Select by Region Shuffles Object Order

    Posted 02-26-2020 17:33
    Thank you to both of you for your input.

    Rather than to maintain the explicit RowID from the original table, the intention was just to keep the relative order of the points. So if a track segment from the selection started at 100 and continued to 140, where then it jumps to another segment at 180 and continues to 200, that the sequential points in these segments could be highlighted, even if the new order was now 1 to 40, 41 to 60...

    By just adding the static ID column as Peter suggested above, I was able to achieve this with Order By pretty quickly. Trying to order by RowID alone was not possible.

    Thanks again. I'm glad to see how responsive and informative this forum is as a resource for working with MapBasic.

    ------------------------------
    Brandon Shepherd
    Knowledge Community Shared Account
    Shelton CT
    ------------------------------