MapInfo Pro

Expand all | Collapse all

vector dot product - adding a column using an expression from two tables

  • 1.  vector dot product - adding a column using an expression from two tables

    Posted 26 days ago

    I'm trying to work out the potential demand for a new cycle route.  I have a map layer from the Propensity to Cycle website which gives me the origins and destination plus number of cyclists that could potential travel from each census area to every other census area.

    In mapinfo I can add the proposed routes and the propensity to cycle data.  I then place a buffer round each segment of proposed route and then get mapinfo to combine the cycle data for all the propensity to cycle links that intersect the buffer.  By doing a separate buffer for each segment and using the update column capability in Mapinfo I can get it to populate each segment of the proposed route – magic – all in one go. 

    The problem I have is that ideally I don't want just a sum of all the propensity to cycle data that intersect but instead would like to work out the magnitude of propensity to Cycle data in the direction of the proposed cycle route.   This would be equivalent to multiplying the Propensity to cycle value by the Cos of the angular between the proposed route and the Propensity to cycle route ( a bit like a vector dot product).   To do this I added the bearing to both sets of vectors and would like to use the add column capability to add a column to the proposed routes equal to Sum of Cos ph * Propensity to Cycle data field.  Where ph is the difference in the angles (Propensity to cycle link bearing and proposed cycle route bearing).

    Unfortunately although the add column capability exists and will look for the necessary intersection between the buffer and the PTC data it won't handle data from two tables so I can't incorporate ph nor will it enable me to calculate an expression.

    Hope that makes some sort of sense - any ideas or ideas of other approaches I could try.

    Richard Calton
    Knowledge Community Shared Account

  • 2.  RE: vector dot product - adding a column using an expression from two tables

    Posted 25 days ago
    Hi Richard

    Even though I don't quite follow your magic formula (I'm not really a heavy mathematician at heart), I do follow what you are asking for.

    When you are using the Update Column statement to update one table based on data in another table, MapInfo Pro joins the two tables for you. But you can do this directly instead and keep access to all the columns and do the math you need to do.

    You can do this using the Select statement.

    I can't give you the complete syntax of the Select statement but it would look something like this:
    Select <specifyTable>.<someID>, Sum(Cos(CycleLink.Bearing - SegmentBuffer.Bearing) * <specifyTable>.Propensity)
    From CycleLink, SegmentBuffer
    Where CycleLink.obj Intersect SegmentBuffer.obj
    Group By <specifyTable>.<someID>

    Where you will have to replace:
    <specifyTable> with the name where the column comes from
    <someID> with the name of a column with unique values
    - the two tables name with the correct table names
    - the names of the Bearing columns with the correct column names

    Now the result of the Select statement above gives you a list of IDs and the calculated magnitude of propensity. You can now use the Update Column command to update the proposed cycle routes using the column with the unique IDs.

    I also hope that you can follow my instructions here. If not, try sharing some more details about your tables and I'll create a more precise Select statement.

    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data