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
------------------------------
Original Message:
Sent: 02-24-2020 10:42
From: Brandon Shepherd
Subject: Automation of Select by Region Shuffles Object Order
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
Original Message:
Sent: 02-24-2020 03:36
From: Peter Horsbøll Møller
Subject: Automation of Select by Region Shuffles Object Order
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
Original Message:
Sent: 02-21-2020 15:07
From: Brandon Shepherd
Subject: Automation of Select by Region Shuffles Object Order
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
------------------------------