Hi Sebastian,
I tried to pull some code from existing applications I have, but in the end it was simpler to just write the code out as an example. So I've attached very basic code (NO DIALOGS) that will give you the results you need (with some tweaking on your end due to me not having any sample data from you). I've also supplied sample tables, so you can open the tables and run the program to see the results. It writes a temp table to your c:\temp directory, but removes it when finished. Other than that it just updates the sample table.
NOTE: I am not an expert programmer, nor do I claim to be. What I've written here I've taught myself over the years. But it does give the result required :)I've used 2016 AUS SA1 boundaries and a Point table. Instead of your sales data, I've used Private Dwellings in the SA1 to build a catchment required per site.
The program does the following:
1. Finds nearest Point to each SA1 boundary and updates the Distance
2. Using the Points table, stores the Name and Value required into a Type Array variable (in the code you can opt to use a Constant value instead)
3. Loops through the SA1 table and finds the closest until the Sum > Site Value
4. Updates a NEW Logical column added with a 1 if that SA1 is selected for the catchment. So afterward you can simply query the SA1 table where CATCH_SELECTION to get your final catchments.
As mentioned above, in the example it updates a new column in the SA1 table. You could change the code so that is saves and appends to a New table if that is preferred.
For SA1 "base" catchments (after closest points assigned) that are < Site Value required, the entire Catchment is selected.
This is basic logic, as in when the cumulative selection of SA1s > Site Value required, it stops. You could enhance this further by checking all other Intersecting SA1 boundaries for a closer match and also provide an acceptable Variance if required (+/- 500 for example).
My test result looks like this, where the Blue is the final Catchment Selection for each Site.
The tweaking I mentioned would be for you to change the Polygon (Source Table) and Point (Destination Table) names in the code. Along with appropriate column name changes.
This process does not create duplicates. If you need them, you would have to modify the code so that the Nearest command is called for each site, rather than the Nearest for each SA1.
Hope this helps.
Thanks
------------------------------
Simon Emmanuel
Spatial Solutions Manager
Salmat Digital
Prestons
------------------------------
Original Message:
Sent: 07-02-2019 03:10
From: Sebastian Simoniuk
Subject: Conditional Select Statement in MapInfo Pro with MapBasic
That code would be extremely useful for a project I'm currently working on that requires "exactly" that...
------------------------------
Sebastian Simoniuk
Business Analyst
Metcash
Macquarie Park
Original Message:
Sent: 06-18-2019 04:05
From: Simon Emmanuel
Subject: Conditional Select Statement in MapInfo Pro with MapBasic
Hi Sebastian
Given your description you may not need mapbasic. You could first assign all polygons to their closest store using distance calculator. Then export data to excel and use an IF statement to select rows given the quantity for each store (I assume you can calculate the required quantities for each store using the %).
Similarly with mapbasic you would store the quantities in an array then loop through the polygon list until criteria met for each store.
I've created a mapbasic app for basically the same process... Selecting x number of homes around each site.
Are you new to mapbasic? I'd be happy to share some code if you could supply some sample data to work with?
------------------------------
Simon Emmanuel
Spatial Solutions Manager
Salmat Digital
Prestons
Original Message:
Sent: 06-17-2019 21:07
From: Sebastian Simoniuk
Subject: Conditional Select Statement in MapInfo Pro with MapBasic
Hi All,
First time poster but a long time reader...
Looking for any guidance on something I thought sounded simple but later found hard to achieve.
I have 2 tables, one containing "point" objects - Table A (stores in our network) and the other "polygon" objects - Table B (statistical areas).
Table A has a sales per week and a penetration % column for each store (numeric), and Table B has a potential expenditure per week column for each statistical area (numeric).
I want to create theoretical catchments (made up of statistical areas from Table B) for all stores in Table A based on a simple condition.
For this example, let's assume that if store "x" does $100,000 per week in sales, it would need a catchment of at least $1,000,000 of potential expenditure. So this store has a 10% catchment penetration which has been predetermined. Other stores have different penetration %'s based on a number of variables. These penetration %'s are also stored in a column in table A.
So, I thought to first select the statistical area object directly below the store, then start selecting surrounding statistical area objects (in some clockwise fashion bordering the initial statistical area object selected) until the sum of the potential expenditure column in Table B is greater than or equal to the penetration % divided by the weekly sales in Table A, then repeat for every store in table A. Ideally all the results would be put into a new table and not a new table for each store.
Sounded simple at the time, but then I just couldn't work out how to get the "select" function to work conditionally across two tables in MapInfo Pro using MapBasic.
Any advice on how to even start to tackle this problem would be greatly appreciated.
Thanking you all kindly in advance...
------------------------------
Sebastian Simoniuk
Business Analyst
Metcash
Macquarie Park
------------------------------