MapInfo Pro

 View Only
  • 1.  Query to find wards all streets pass through or within

    Posted 06-01-2020 12:04
    Hello group,

    I have a polygon table of wards (23 objects). I have a another table of a street network where one object = one named street. I want to know what ward each street is in.

    Whilst a simple column update intersect is useful for those streets fully within one ward, it's not so great for all the other streets that don't follow that rule. Some streets start in one ward and end in another whilst some pass through several wards. Some streets run along a boundary between two wards, some even runs along a boundary between two wards and then halfway along run between a different two wards. And some streets start in one ward, go into another and end up in the in the first ward.

    How can update a new ward value column(s) with the value(s) of all the wards (or ward) that a street is either within, intersects or bounds?


    Thanks,
    Stuart

    ------------------------------
    Stuart Carter
    Corporate GIS Manager
    Southwark Council
    London
    ------------------------------


  • 2.  RE: Query to find wards all streets pass through or within

    Posted 06-02-2020 08:56
    Hi Stuart,
    I've never found it pleasant working with street data like that! Somehow there are always annoying exceptions to whatever approach you take.

    I'd presume the ideal to end up with is a list of street USRN's, and a flag column for each ward, so you can filter on that for the streets in each ward?

    I'd probably use street.obj intersects ward.obj to get that data, which would give duplicate rows where streets cross wards (I'm assuming that's how it behaves, my remote network is so slow I'm not going to test that), and then suck it out into Excel and crosstab to get a more useful format with individual wards as columns? (the experts may say there's an easier way to do that within Mapinfo!)

    This is assuming you're using records where the street has a width, not just a line representing the midpoint of a street. That would be more challenging!

    hope that helps a little

    Martin

    ------------------------------
    Martin Burroughs
    Oldham Council
    ------------------------------



  • 3.  RE: Query to find wards all streets pass through or within

    Posted 06-02-2020 10:43

    Thanks for that, Martin.

     

     

     






  • 4.  RE: Query to find wards all streets pass through or within

    Posted 06-02-2020 09:47
    Stuart,

    If you just want a look up table then roads.obj intersects wards.obj will give you that.

    If you want a record for each road and then a field with every ward name in it, with perhaps a comma separator, then this is a bit more fiddly.

    I would do it a series of SQL + update column.  Below works for roads that touch 1, 2 or 3 wards.  4 or more and I'd probably end up doing them manually or give up completely if there's loads.....

    1) select roads that only fall in 1 ward using 'contains entire' operator.  update with ward name and save copy (New roads). delete records from original.

    2) select roads which intersect with wards on edge of area (Southwark?) using intersect operator, group_by and count.  Any roads with a count of 1 can be updated with ward name, appended to New roads and deleted from original.

    3)  select roads that intersect with wards.  this will give you 2 or more rows for each road. update query and one ward name will be added to the road data. select roads that intersect with wards AND road.wardname doesn't equal ward name already added to roads.  for these selected road records overwrite the road.wardname with road.wardname + 2nd selected ward name with a comma separator

    4) Now every road remaining should have 2 ward names in the desired column.  to find those that touch 3 wards  run an sql to select roads that intersect with wards but include a left string and right string query of the wards already accounted for, e.g.

    road.obj intersect ward.obj and (left$(road.wardname, len(ward.wardname)) <> ward.wardname or right$(road.wardname, len(ward.wardname))<> ward.wardname).  This should select any wards not already named in the road layer.  update road.wardname field again with 3rd ward name.

    This data then can then be appended to the new roads layer.  voila.   If a road intersects 4 or more regions then the SQL statement will be even trickier.

    I have just written these statements without checking I have all the correct brackets, commas, etc.  

    Happy to talk you through it if useful. number below.

    I have heard of users just doing an intersect SQL to find all road to ward relationships, exporting to excel and using pivot table to get the desired result then importing and joining back to the road data.  Pivot table is a mystery to  me though :)

    Apologies if above is confusing.

    Kind regards,

    Nick
    0161 773 5021

    ------------------------------
    Nick Hall
    Mapchester LTD
    nick.hall@mapchester.co.uk
    ------------------------------



  • 5.  RE: Query to find wards all streets pass through or within

    Posted 06-02-2020 10:30
    Hi Stuart,

    I might be missing something but the following might do the trick:

    First, create a field within the Street table that will contain your list of Ward Names or IDs.  In the below I'm assuming each Ward has an ID, and that the field you create in the Street Table is called "Ward_ID_List".  Then execute the following:

    Select * from Street, Ward where Street.Obj Intersects Ward.Obj into StreetWard_Join
    Update StreetWard_Join Set Ward_ID_List = Ward_ID_List + "," + Ward_ID

    This will give you a comma-separated list of Ward IDs for each street.

    ------------------------------
    James Nolet
    Dooley Mitchell & Morrison Pty Ltd
    Mentone, VIC, Australia
    ------------------------------



  • 6.  RE: Query to find wards all streets pass through or within

    Posted 06-02-2020 10:42

    Thanks for that, James. I'll give it a go.

     

     

     






  • 7.  RE: Query to find wards all streets pass through or within

    Posted 06-02-2020 11:06

    Where are you doing this bit?:

    Update StreetWard_Join Set Ward_ID_List = Ward_ID_List + "," + Ward_ID

     






  • 8.  RE: Query to find wards all streets pass through or within

    Posted 06-02-2020 21:39
    In the MapBasic Window.  Or you can carry out an Update Column:

    Ward/Street Update Column


    ------------------------------
    James Nolet
    Dooley Mitchell & Morrison Pty Ltd
    Mentone, VIC, Australia
    ------------------------------



  • 9.  RE: Query to find wards all streets pass through or within

    Posted 06-04-2020 07:39
    That's a neat trick James! I thought it would have complained about 'not being able to update a view' or similar.

    ------------------------------
    Timothy Mashford
    Melbourne, Australia
    ------------------------------



  • 10.  RE: Query to find wards all streets pass through or within

    Posted 06-04-2020 09:32
    Not in this case, Tim!  Thanks.

    ------------------------------
    James Nolet
    Dooley Mitchell & Morrison Pty Ltd
    Mentone, VIC, Australia
    ------------------------------