2

I have a feature class called AO15_16 where the field id needs to be updated. The update will contain a unique id based on a query of another table where I am querying issueddate, issuedtime and `zipcode. The ids are already in the the 2nd table based on a unique date, zip code and 1 hour increment per zip code.

The table below shows the hour increments from 6pm-7pm per zip code on the date of 7/9/2016

500 7/9/2016    6:42:00 PM  7:06:00 PM  0:24    GARDEN GROVE                92840   11  Variable    5.8 3707, 3709, 3736    


501 7/9/2016    7:00:00 PM              92840       Variable    5.8     

502 7/9/2016    6:42:00 PM  7:06:00 PM  0:24    GARDEN GROVE                92841   11  Variable    5.8 3707, 3709, 3736    

503 7/9/2016    7:00:00 PM              92841       Variable    5.8     


504 7/9/2016    6:42:00 PM  7:06:00 PM  0:24    GARDEN GROVE                92844   11  Variable    5.8 3707, 3709, 3736    

505 7/9/2016    7:00:00 PM              92844       Variable    5.8     

The table below shows unique rows for 7/10/16

506 7/10/2016   3:13:00 PM  5:56:00 PM  2:43    GARDEN GROVE                92840   25  SSW 8.1 3707, 3736, 3915, 3918

507 7/10/2016   4:00:00 PM              92840       S   9.2 

508 7/10/2016   5:00:00 PM              92840       S   9.2 

509 7/10/2016   6:00:00 PM              92840       S   8.1 


510 7/10/2016   3:13:00 PM  5:56:00 PM  2:43    GARDEN GROVE                92841   25  SSW 8.1 3707, 3736, 3915, 3918

511 7/10/2016   4:00:00 PM              92841       S   9.2 

512 7/10/2016   5:00:00 PM              92841       S   9.2 

513 7/10/2016   6:00:00 PM              92841       S   8.1 

The feature class contains an issueddate, issuedtime and zipcode which meets the conditions of the above table.

The goal is to update the field id in the feature class by reading the query from the table. How can this be done with the query programatically changing so it takes into account the next greatest date and hour increment per zip code?

i.e. where issueddate = n + 1 AND issuedtime = n + 1 BETWEEN the hours that are in the rows?

What I have to use the table as a lookup table is below, how can this be changed to increment next greatest date and time in the where clause and update the 'id' field in the feature class?

whereClause = '''WHERE ISSUEDTIME BETWEEN x AND x (increment) and DATEADD(d,ROW_NUMBER() OVER (ORDER BY ID ASC), GETDATE())'''

 tableSet= set()
            with arcpy.da.SearchCursor(table, fields="Time Start; date; zipcode") as cursor:
                for row in cursor:
                    tableSet= .add(row[0])

            with arcpy.da.UpdateCursor(fc, "id", whereClause) as cursor:
                for row in cursor:
                    if row[0] in tableSet:
                        row[1] = row[0]
                    cursor.updateRow(row)
  • Have you looked into pandas? Convert table to DataFrame and then query that to update your fc. Might be simpler to write a date query on python datetime objects than determine proper SQL syntax, which is DBMS specific. – Paul Feb 13 '17 at 20:55
  • I am looking into Pandas now, how can I iterate through dates where n+ 1 is accounted for each row though? – Geoffrey West Feb 13 '17 at 21:48
  • 2
    Hard to tell looking at your data, but it sounds like you might want to apply a delta to the field values, eg datetime.timedelta(hours=1) – Paul Feb 13 '17 at 22:01

Your Answer

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

Browse other questions tagged or ask your own question.