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------------------------------
Original Message:
Sent: 06-01-2020 12:03
From: Stuart Carter
Subject: Query to find wards all streets pass through or within
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
------------------------------