JSONToFeatures_conversion: Cannot create a table with a duplicate column

2134
9
Jump to solution
08-16-2017 01:26 PM
RyanDavis1
Occasional Contributor

Hi,

I'm working on a Python script that bulk downloads layers from feature servers.  I download each layer as JSON and then use JSONToFeatures_conversion to convert it to a feature class.  The script works okay for the most part, but for some reason about a quarter of the layers fail to convert from JSON to Feature Class.

I consistently receive an Error 206: Cannot create FeatureClass, and it further elaborates that I "cannot create a table with a duplicate column."  While that makes sense, this seems odd to me because I'm basically copying valid layers, not adding any columns, and there shouldn't be any duplicate columns.  I've also manually converted individual layers using the same workflow just to see if it was a data issue, and I'm able to create feature classes with no problem.

Could someone give me a hint as to what I may be doing wrong?

Here's how I'm writing my JSON if it helps:

def write_json(url_response):
    """Writes JSON to a file."""
    with open('downloadFile.json', 'wb') as chunkTemp:
        for chunk in url_response.iter_content(chunk_size=1024):
            if chunk:
                try:
                    chunkTemp.write(chunk)
                except Exception as e:
                    print 'Yuck. ' + str(e)
                    break
        chunkTemp.seek(0) #Go back to the top of the file.
    return chunkTemp

If it's not obvious, I'm a relative novice with Python and a complete noob when it comes to working with JSON.

Thanks.

0 Kudos
1 Solution

Accepted Solutions
RandyBurton
MVP Alum

The JSONToFeatures Tool does not like the OBJECTID definition in the JSON file.

The JSON contains the following in the "fields" section:

  "fields" : [
    {
      "name" : "OBJECTID", 
      "type" : "esriFieldTypeInteger", 
      "alias" : "OBJECTID", 
      "sqlType" : "sqlTypeOther", 
      "domain" : null, 
      "defaultValue" : null
    }, 
    {‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Note the setting for "type" which is "esriFieldTypeInteger".  When testing it with ArcMap 10.5 using a file geodatabase, the tool wanted to duplicate this field as it wants the type of the OBJECTID to be "esriFieldTypeOID".

There are a couple of things you can do.  The easiest is to edit the JSON by changing the type on line 12 as indicated below.  Your script that processes the JSON could include some code to make the edit for you.

{
  "objectIdFieldName" : "OBJECTID", 
  "globalIdFieldName" : "", 
  "geometryType" : "esriGeometryPoint", 
  "spatialReference" : {
    "wkid" : 102100, 
    "latestWkid" : 3857
  }, 
  "fields" : [
    {
      "name" : "OBJECTID", 
      "type" : "esriFieldTypeOID", 
      "alias" : "OBJECTID", 
      "sqlType" : "sqlTypeOther", 
      "domain" : null, 
      "defaultValue" : null
    }, 
    {
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

A second option would  be to do a global replace of "OBJECTID" with something like "OBJ_ID" in the JSON file.  You would also need to set line 2 to an empty string ( "objectIdFieldName" : "" ).  Then the tool would create a new OBJECTID.  As this option is a bit more complicated, I would use the first suggestion - just change the type to "esriFieldTypeOID".

Hope this helps.

View solution in original post

9 Replies
DanPatterson_Retired
MVP Emeritus

What are the field names?

0 Kudos
RyanDavis1
Occasional Contributor

Well, the different layers have different field names, but here's an example of one of the downloads:

https://services2.arcgis.com/1cdV1mIckpAyI7Wo/ArcGIS/rest/services/All_Places_of_Worship/FeatureServ... 

I don't see any duplicates, but maybe I'm missing something obvious.

Thanks.

0 Kudos
MicahBabinski
Occasional Contributor III

Hi Ryan,

Can you post the .json and include the link to the service which you exported it from? That would help troubleshoot.

Micah

0 Kudos
RyanDavis1
Occasional Contributor

Thanks for the reply.

Here's an example of one of the layers that fails.

Service:  https://services2.arcgis.com/1cdV1mIckpAyI7Wo/ArcGIS/rest/services/All_Places_of_Worship/FeatureServ... 

JSON:  https://services2.arcgis.com/1cdV1mIckpAyI7Wo/ArcGIS/rest/services/All_Places_of_Worship/FeatureServ... 

This is just one example of maybe 50 or so layers that fail with this error.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

I took the worship layer above, downloaded the JSON output, and ran the following code just fine:

>>> json = # path to JSON file
>>> arcpy.JSONToFeatures_conversion(json,"in_memory/worship")
<Result 'in_memory\\worship'>
>>> 
0 Kudos
RyanDavis1
Occasional Contributor

I've also manually converted individual layers using the same workflow just to see if it was a data issue, and I'm able to create feature classes with no problem.

I think I was mistaken when I wrote this.  Now when I try to manually convert an individual layer through ArcToolbox, I'm getting an error claiming "The workspace is not connected."

0 Kudos
RandyBurton
MVP Alum

The JSONToFeatures Tool does not like the OBJECTID definition in the JSON file.

The JSON contains the following in the "fields" section:

  "fields" : [
    {
      "name" : "OBJECTID", 
      "type" : "esriFieldTypeInteger", 
      "alias" : "OBJECTID", 
      "sqlType" : "sqlTypeOther", 
      "domain" : null, 
      "defaultValue" : null
    }, 
    {‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Note the setting for "type" which is "esriFieldTypeInteger".  When testing it with ArcMap 10.5 using a file geodatabase, the tool wanted to duplicate this field as it wants the type of the OBJECTID to be "esriFieldTypeOID".

There are a couple of things you can do.  The easiest is to edit the JSON by changing the type on line 12 as indicated below.  Your script that processes the JSON could include some code to make the edit for you.

{
  "objectIdFieldName" : "OBJECTID", 
  "globalIdFieldName" : "", 
  "geometryType" : "esriGeometryPoint", 
  "spatialReference" : {
    "wkid" : 102100, 
    "latestWkid" : 3857
  }, 
  "fields" : [
    {
      "name" : "OBJECTID", 
      "type" : "esriFieldTypeOID", 
      "alias" : "OBJECTID", 
      "sqlType" : "sqlTypeOther", 
      "domain" : null, 
      "defaultValue" : null
    }, 
    {
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

A second option would  be to do a global replace of "OBJECTID" with something like "OBJ_ID" in the JSON file.  You would also need to set line 2 to an empty string ( "objectIdFieldName" : "" ).  Then the tool would create a new OBJECTID.  As this option is a bit more complicated, I would use the first suggestion - just change the type to "esriFieldTypeOID".

Hope this helps.

RyanDavis1
Occasional Contributor

This is extremely helpful.  Thank you.  I'll give it a try.

0 Kudos
RyanDavis1
Occasional Contributor

Thanks, rvburton  It's very appreciated.  I took your first suggestion and created a function to change the type to "esriFieldTypeOID".  I won't try to claim it's the most elegant code, but I'll post it here in case it's helpful to someone later.

def change_objectID_type(json_file):
    """Changes objectID type from esriFieldTypeInteger to esriFieldTypeOID"""
    with open(json_file, 'rb') as f:
        json_dict = json.load(f)
        oid = json_dict['objectIdFieldName']
        field_list = json_dict['fields']
        for field in field_list:
            if field['name'] == oid:
                field['type'] = 'esriFieldTypeOID'
            else:
                pass
    with open(json_file, 'wb') as json_out:
        json.dump(json_dict, json_out)