Latest Contributions by BenjaminBarton

Greetings, Using python and ArcPy search cursors, I've extracted list(s) of dictionaries containing normalized key value pairs originating from specific tables but residing in a denormalized database layer. In the same script, I am now creating a JSON string with an object containing field & value pair arrays [] that that are to contain the keys and values (key value pairs) I've already distilled into lists of dictionary structures. There is an 'inserts' object created for each source table that the data is distilled from so I end up with a structure having field & value arrays contained within objects contained within 'inserts' arrays contained within objects contained within 'tableInserts' arrays contained within the JSON object as follows: { "tableInserts": [ {"inserts": [ { 'fields":[], "values": []    }   ]    }     }.  I've got my python script to kick out the structure I am after but I am having difficulty separating the key value pairs contained in my lists of dictionary structures into their respective field & value target JSON arrays.  Python dictNorm = { 'place': placeNorm, 'place_type': place_TypeNorm, 'place_nm': nameNorm, 'place_geom_p': geomNorm, } constJSON = [] i = 0 for key, val in dictNorm.iteritems(): constJSON.append({"table":key, "inserts":[]}) for p in val: constJSON[i]["inserts"].append({"fields":p}) i += 1 ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ Portion of produced JSON string structure: {     "table": "place_nm",      "inserts": [                                           {                                     "fields": [                                           {                                        "lu_stat_id": 1,                                        "lu_use_desig_id": 1,                                         "place_nm": "<name>"                                           }                                       ]                                  }                      ] }, Whereas I need it to produce (specifically, breaking key value pairs being parsed from dictionaries into "fields": & "values": arrays): {    "table": "place_nm",    "inserts": [                               {                                     "fields": [                                        "lu_stat_id",                                        "lu_use_desig_id",                                         "place_nm"                                       ],                                        "values": [                                              1,                                              1,                                              <name>                                        ]                                  }                         ] }, The challenge is that the dictNorm dictionary structure references lists of dictionaries for the individual key value pairs I want to separate out so I need to unpack key value pairs existing in dictionaries wrapped in lists that are inside of a dictionary.  I'm thinking I need another for loop iterator below line 13 to parse through these sub lists of dictionaries called in the dictNorm structure which if you were to print out dictNorm, a specific dictionary contained within would look like:  'place_nm': [                         {'lu_stat_id': 1, 'lu_use_desig_id': 1, 'place_nm': u'National Monument'},                         {'lu_stat_id': 1, 'lu_use_desig_id': 1, 'place_nm': u'Joe's Bar'},                         {'lu_stat_id': 1, 'lu_use_desig_id': 1, 'place_nm': u'Pike's Peak'},                         {'lu_stat_id': 1, 'lu_use_desig_id': 1, 'place_nm': u'Niagara Falls'},                         {'lu_stat_id': 1, 'lu_use_desig_id': 1, 'place_nm': u'Dulles Airport'},                         {'lu_stat_id': 1, 'lu_use_desig_id': 1, 'place_nm': u'Bay Bridge'},                         {'lu_stat_id': 1, 'lu_use_desig_id': 1, 'place_nm': u'Bagram Airfield'},                         {'lu_stat_id': 1, 'lu_use_desig_id': 1, 'place_nm': u'McDonals'},                      ] Any assistance would be appreciated as I know I have a lot going on here before getting to the JSON construction piece.  ... View more

Greetings, I am running into a small problem. SearchCursor documentation states that search cursors can handle multiple field_names yet the below script only returns the first field name explicitly stated in the field_names parameter of the search cursor. If I use an asterisk, it returns the first field name in the list. I need it to return the location_id and lat and long attributes respectively from the view I am querying. Is it possible for a searchcursor to return more than one column / field?  #Explicitly defined object IDs known to be conflated in conflation data model objectIDList = (55555,44444) #small test #gets distinct location_ids def getUniqueLocIDs(): tableRegistry = os.path.join(conn, "myView") fields = ['objectid', 'location_id', 'latitude', 'longitude'] whereExp = "objectid IN {0}".format(objectIDList) uniqueLocIDs = list(set("{0}".format(row[0]) for row in arcpy.da.SearchCursor(tableRegistry, ['location_id', 'latitude'], whereExp))) return list(uniqueLocIDs) print(getUniqueLocIDs())‍‍‍‍‍‍‍‍‍‍ ... View more

Greetings, I'm new to GIS and new to Python. Trying to return a distinct array (or list or dictionary) of place_ids where objectid matches predefined list (will eventually be provided by user). There is a 1:m relationship between objectids and place_ids and script currently only returns objectids when what I need is distinct place_ids. I'm essentially trying to extract normalized key elements (and eventually attributes) from a table that is not normalized. Note, I could bring in all rows for objectid and place_id from my view and then compare or join on my objectIDFilter list, but that would be millions of rows so how can I apply the filter as I'm calling data from my tableRegistry to limit the data I am bringing in to just those tuples that match against my list? To demonstrate using SQL, the end state I am after and how I am thinking through the logic in my head, it would be written as (assuming my list was a table or view, which its not): SELECT DISTINCT v.place_id FROM mView v INNER JOIN objectIDFilter F ON v.objectid = f.objectid‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ This is wrong but it's all I've got... objectIDFilter = (12345,54321,55555,44444,33333,22222,...) # Set overwrite to true, for in memory table view arcpy.env.overwriteOutput = True tableRegistry = os.path.join(conn, "myView") fields = ['objectid','place_id'] uniquePlaceIDs = {} with arcpy.da.SearchCursor(tableRegistry, fields) as cursor: occurances = [row[0] for row in cursor] for i in set(objectIDFilter): print(occurances) #icount = occurances.count(i) #print("{} has {} records".format(i, icount))‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ Thanks in advance for any assistance that can be provided. ... View more