MapInfo Pro

 View Only

  • 1.  Returning multiple spatial results for single row of a table

    Posted 12-01-2023 05:59

    Hi all

    I have a mapinfo layer for school pupils (points) and a layer for school catchments (polygons). In the catchment layer most of the catchment areas are for a single school but some areas relate to two or more schools. In these cases the are multiple polygons objects covering the same area, each with a different catchment name. When I do a table\update column after adding a new column in the pupil table for catchment area I get only one result in areas where there are multiple matches. How can I return a result that gives all catchments that a pupil point falls in?

    In Power BI for example I would use a DAX expression with [concatenatex] to bring back multiple results in this way

    Thanks

    Chris



    ------------------------------
    Chris Dickinson
    North Yorkshire County Council
    ------------------------------


  • 2.  RE: Returning multiple spatial results for single row of a table

    Employee
    Posted 12-01-2023 08:02

    Hi Chris

    Can you try if this work:

    Instead of using the SCHOOL_DISTRICT column only, try wrapping into an expression like this:

    IIf(DISTRICT = "", SCHOOL_DISTRICT, DISTRICT + ", " + SCHOOL_DISTRICT)

    In the expression,  DISTRICT is the column in the point table that you want to update with the name of the School districts.

    SCHOOL_DISTRICT is the name of the school district from the polygon table.

    I can't recall if I have tried this myself.

    Keep in mind that the DISTRICT column must be empty before running the statement.



    ------------------------------
    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data
    ------------------------------



  • 3.  RE: Returning multiple spatial results for single row of a table
    Best Answer

    Posted 12-01-2023 08:06

    Hi Chris,

    First, join your two tables together:

    Select * from pupils, School_Catchment where pupils.Obj Within School_Catchment.Obj into JoinTable

    Then perform the update using the below expression.  In the following, 'Catchments' is the name of the new column you have created in the pupil table, and 'Catchment_name' is the column from the school catchments table.
    Update JoinTable Set Catchments = Catchments + "," + Catchment_name

    The above will place commas at the start of each string.  If you are using a later version of MapInfo, you can use IIf for a neater result:

    Update JoinTable Set Catchments = IIf(Catchments="",Catchment_name,Catchments + "," + Catchment_name)

    (I just saw that Peter got to this before me, but my answer includes the initial table join which might come in handy!)



    ------------------------------
    James Nolet
    GIS Manager
    Caf Consulting
    Mentone office, VIC, Australia
    ------------------------------



  • 4.  RE: Returning multiple spatial results for single row of a table

    Employee
    Posted 12-01-2023 08:32

    James,

    Your solution added the trick that made it work: joining the table before running the update.

    I tried it myself and ran into issues. I couldn't refer back to a column in the point table when building the expression used to update that column.

    I ended up with your solution in the SQL Window:

    Select a.CombinedName, d.districtname
From Addresses As "a", Districts As "d"
Where a.Obj Within d.Obj 
Into _to_update

Update _to_update
   Set CombinedName = IIf(CombinedName = "", districtname, CombinedName + ", " + districtname)
    IIf() was added back in MapInfo Pro v17.0.


    ------------------------------
    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data
    ------------------------------

    Original Message


  • 5.  RE: Returning multiple spatial results for single row of a table

    Posted 12-01-2023 08:36

    Thanks to you too Peter, much appreciated. If only our IT service desk was as responsive as the forum!

    Cheers

    Chris 



    ------------------------------
    Chris Dickinson
    North Yorkshire County Council
    ------------------------------

    Original Message


  • 6.  RE: Returning multiple spatial results for single row of a table

    Posted 12-03-2023 19:13

    Hi Peter,

    Yes, it is not possible to join the tables and carry out the required update in a single Update statement.  The join needs to be done separately.

    For anyone using pre-v17 MapInfo, the leading comma can be removed as follows (going back to the table and column names used in my example):

    Select * From pupils Where Catchments Like ",%" Into removeComma
    Update removeComma Set Catchments = Right$(Catchments, Len(Catchments)-1)



    ------------------------------
    James Nolet
    GIS Manager
    Caf Consulting
    Mentone office, VIC, Australia
    ------------------------------

    Original Message


  • 7.  RE: Returning multiple spatial results for single row of a table

    Posted 12-01-2023 08:34

    Excellent! Thanks very much James

    Chris



    ------------------------------
    Chris Dickinson
    North Yorkshire County Council
    ------------------------------

    Original Message