MapInfo Pro

Expand all | Collapse all

Conditional Select Statement in MapInfo Pro with MapBasic

  • 1.  Conditional Select Statement in MapInfo Pro with MapBasic

    Posted 06-17-2019 21:08
    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
    ------------------------------


  • 2.  RE: Conditional Select Statement in MapInfo Pro with MapBasic

    Posted 06-18-2019 04:05
    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
    ------------------------------



  • 3.  RE: Conditional Select Statement in MapInfo Pro with MapBasic

    Posted 07-02-2019 03:11
    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
    ------------------------------



  • 4.  RE: Conditional Select Statement in MapInfo Pro with MapBasic

    Posted 07-03-2019 02:29
    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.
    Catchment Creator Sample
    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
    ------------------------------



  • 5.  RE: Conditional Select Statement in MapInfo Pro with MapBasic

    Posted 06-18-2019 09:44
    Have you looked at using the Redistrict tool in MapInfo?  I've not really used it that much but from your description, I think you could use it to build your catchment areas from the statistical units. More details here.

    ------------------------------
    Greg Driver
    Knowledge Community Shared Account
    Shelton CT
    ------------------------------



  • 6.  RE: Conditional Select Statement in MapInfo Pro with MapBasic

    Pitney Bowes
    Posted 06-18-2019 18:33
    Great spatial problem !

    Redistrict tool will work but is then a manual process for each catchment, might not be practical with a large dataset.

    I like the suggestion from @Simon Emmanuel to start by tagging the boundaries with the nearest store, this gives a baseline and then you can reallocate as required (using the IF statement etc). One thing I wondered, from the description of the 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.

    It's possible some stores won't have enough boundaries to meet this condition before they intersect with adjoining catchments. What happens at this point? Can a statistical area boundary only be in one catchment? Or would you consider overlapping catchment to meet the criteria of potential expenditure?

    If overlapping then the array option from Simon (either via Excel or MapBasic) works very nicely. If not overlapping there are two potential conditions to consider:
    1. Catchment can't increase to include sufficient statistical areas to meet the condition
    2. Some statistical areas aren't required in any catchment. these become "orphans". What happens to these?

    Last comment - don't forget there is "Combine Using Column" function that will take the tagged statistical boundaries and build the catchment boundaries as the last step.

    Look forward to hearing how it progresses...

    ------------------------------
    Ashley Crane
    Regional Director, Software Support
    Pitney Bowes
    ------------------------------



  • 7.  RE: Conditional Select Statement in MapInfo Pro with MapBasic

    Posted 07-02-2019 03:13
    It doesn't matter if the catchments overlap...

    ------------------------------
    Sebastian Simoniuk
    Business Analyst
    Metcash
    Macquarie Park
    ------------------------------



  • 8.  RE: Conditional Select Statement in MapInfo Pro with MapBasic

    Pitney Bowes
    Posted 06-18-2019 19:44
    Hi,

    I did not fully understand your problem, but I wanted to make you aware of two recent functions added to mapbasic:
    IIF() and Cond()

    Check out the mapbasic help and see if they can assist you in your problem.

    -Bob

    ------------------------------
    Bob Fortin
    Software Architect and Distinguished Engineer
    ------------------------------



  • 9.  RE: Conditional Select Statement in MapInfo Pro with MapBasic

    Posted 07-02-2019 03:14
    Thanks Bob... will do...

    ------------------------------
    Sebastian Simoniuk
    Business Analyst
    Metcash
    Macquarie Park
    ------------------------------