2

I'm currently working on a large dataset of roads. In which i have to check if the roads in 1:250 000 have the same road number as those in 1:50 000. Because of alot of generalization I have to make a radius serch in 1:250 000 to be sure i have gotten the road into the spatial join. For this to work, i used field mapping parameter "join" with "," as a delimiter. Since this doesen't work with numbers, I converted the field to text.

The problem I'm having now is that I cannot select specific numbers from the new field. It will only work on the first, so when i try to check if the road number from 1:250 000 is equal to 1:50 000 it will only go through the 1st number in the column and not the rest that follows after the comma delimiter. Does anyone have any idea how i might check all the numbers that has been converted to text, against one specific number?

enter image description here

  • Is this one specific number changing? If the number is a constant you can calculate a new field with a IF-ELSE construct and a list comparison with python in the field calculator. If the specific number depends on another query or something else it might not be possible with the limited SQL of the File-Geodatabase. – Matte Aug 22 '17 at 9:36
  • What do you check? That all the numbers in the VEGNUMMER are the same for one row or do you compare the VEGNUMMER column to Another column? – BERA Aug 22 '17 at 9:43
  • Yes, i want to check these numbers with another column that has only 1 number, and check that they are identica to one of the numbers shown. – proxmo Aug 22 '17 at 9:57
  • I can also add to the comment earlier made, that yes the speficic number I'm looking for is changing so a simple select one number will not work. – proxmo Aug 22 '17 at 11:54
3

Script below will select rows where there is a match between any of the comma-delimited numbers in one field and an integer in another field. Execute code in the Python window.

def checkNumbers(layer,textfield,checkfield):
    oidlist=[]
    with arcpy.da.SearchCursor(layer,['OID@',textfield,checkfield]) as cursor:
        for row in cursor:
            templist=row[1].replace(" ","").split(',')
            if str(row[2]) in templist:
                oidlist.append(row[0])
    sql="{0} IN({1})".format(arcpy.AddFieldDelimiters(layer,'OBJECTID'),", ".join(map(str,oidlist)))
    arcpy.SelectLayerByAttribute_management(in_layer_or_view=layer, 
                                           where_clause=sql)

Call the function with for example:

checkNumbers("Points111","VEGNUMMER","IntegerField")

enter image description here

Or as standalone script:

import arcpy

feature_class = r'C:\database.gdb\points' #Change to match your data
textfield='VEGNUMMER' #Change to match your data
checkfield='IntegerField' #Change to match your data

arcpy.MakeFeatureLayer_management(in_features=feature_class, out_layer='lyr')

oidlist=[]
with arcpy.da.SearchCursor('lyr',['OID@',textfield,checkfield]) as cursor:
    for row in cursor:
        templist=row[1].replace(" ","").split(',')
        if str(row[2]) in templist:
            oidlist.append(row[0])
sql="{0} IN({1})".format(arcpy.AddFieldDelimiters('lyr','OBJECTID'),", ".join(map(str,oidlist)))
arcpy.SelectLayerByAttribute_management(in_layer_or_view='lyr', 
                                       where_clause=sql)
#Add more code here to continue to work with the selection
  • Hello, and thank you for this, but it seems it will only still check the first number in the row. I saw several examples of places where the correct number where after the first and was not in the selectet attributes. So im still looking for that solution. – proxmo Aug 22 '17 at 11:46
  • @proxmo: I forgot about the whitespaces between comma and next digit in your text field. I have modified the code, try again! – BERA Aug 22 '17 at 12:00
  • imgur.com/a/byXBc I didnt get to show the image directly so i uploaded it to imgur. – proxmo Aug 22 '17 at 12:01
  • Thank you so much, that got the code working and it selected the numberes i needed.This will be helpfull in the future as well. – proxmo Aug 22 '17 at 12:10
  • 1
    You need to import arcpy, create a feature layer etc. I've added an example above. – BERA Aug 24 '17 at 13:16
3

If you are wanting to select by attribute then you can just treat the numbers as text in a string and search using wildcards. You can read about constructing SQL queries in arc here Say you were looking for number 6 you would enter something like this into the select by attribute dialog box:

"VEGNUMMER" LIKE '6' OR
"VEGNUMMER" LIKE '% 6' OR
"VEGNUMMER" LIKE '% 6, %'

The first line selects for only 6 the second line selects where 6 is the last number in the list, and the final line selects when 6 is in the middle.

If this is a one off then this should be a reasonable solution. If your shapefile is very large you'll probably be better off with a numeric solution, but that would require a different approach from the beginning.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.