MapInfo Pro

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

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

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!)

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

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.districtnameFrom Addresses As "a", Districts As "d"Where a.Obj Within d.Obj Into _to_updateUpdate _to_update   Set CombinedName = IIf(CombinedName = "", districtname, CombinedName + ", " + districtname)

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!)

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

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.districtnameFrom Addresses As "a", Districts As "d"Where a.Obj Within d.Obj Into _to_updateUpdate _to_update   Set CombinedName = IIf(CombinedName = "", districtname, CombinedName + ", " + districtname)

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!)

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

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!)

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