Read and write python file using X and Y fields in the data.

[jaykapalczynski]

I have a json string that is being read from a word press API URL.... it looks like the attached.

I need to get this to a feature class.  In addition to all the fields, it only has an X and Y field which I have to convert to point geometry.  Any thoughts on how I can achieve this via Python?

So I need to read the URL for the json string then create a FC from it.

Python to Table and then Table to FC?  Any help would be appreciated.

[jaykapalczynski]

OK this is what I have and getting another error.....sorry for my ignorance...tryin to learn here.

Its rewriting to the X and Y field names so thats good...

But getting error

Traceback (most recent call last):
File "C:/Users/t/Desktop/JSONSamples/AddPoints.py", line 44, in <module>
arcpy.JSONToFeatures_conversion(json_object, os.path.join("outgdb.gdb", "myfeatures"))
File "C:\Program Files (x86)\ArcGIS\Desktop10.8\ArcPy\arcpy\conversion.py", line 602, in JSONToFeatures
raise e
ExecuteError: Failed to execute. Parameters are not valid.
ERROR 000814: Invalid file type
Failed to execute (JSONToFeatures).

1. Do I have to convert the JSON Object to something before I use the  JSONToFeatures_conversion
2. Maybe its my output location and gdb.  I have a file gdb called outgdb.gdb at the same location as where the python file is located.

import arcpy
import requests
import json
import uuid
import os

arcpy.env.workspace = "C:/Users/t/Desktop/JSONSamples/"
dataInput = r"C:/Users/t/Desktop/JSONSamples/JSON3.json"

f = open(dataInput)
data = json.load(f)
converted_list = []
for i in data['BirdTrailLocations']:

    i['x'] = i.pop('site_longitude')
    i['y'] = i.pop('site_latitude')
    converted_list.append(i)

    varsearchSiteID =  i["site_id"]
    varsearchSiteName =  i["site_name"]
    print ("site_id: " + varsearchSiteID)
    print ("site_name: " + varsearchSiteName)

    print ""

    print(i)

    print ""
  
# Closing file
f.close()

json_object = json.dumps(converted_list)

print json_object
print ""
print converted_list

outfeatureLocation = "C:/Users/t/Desktop/JSONSamples/OutputGeodatabase/"
arcpy.JSONToFeatures_conversion(json_object, os.path.join("outgdb.gdb", "myfeatures"))

[Anonymous User]

no worries... it looks like esri is pretty serious wanting an ESRI-JSON formatted JSON...

You can create this format similar to how we popped the out the site_long and site_lat.  Using your txt as an example but we build the geometry, fields, and attributes list:

converted_list = []
for json_dict in distr:
    feat_dict = {} 
    feat_dict["geometry"] = {"x": json_dict["site_longitude"], "y": json_dict["site_latitude"]}
    # the list created a value type is incompatible with the field type. [site_facilities] so we nee dto convert it to a string
    json_dict["site_facilities"] = str(json_dict["site_facilities"])

    feat_dict["attributes"] = json_dict
    converted_list.append(feat_dict)

then create the dict that you will convert to esri-JSON, setting the fields to an item from the json and converted_list to the features:

esri_json = {"geometryType" : "esriGeometryPoint",
             "spatialReference" : {"wkid" : 4326},
            'fields': [{"name": str(k), "type": "esriFieldTypeString", "alias": str(k), "length": 255} for k, v in distr[0].items()],
            'features': converted_list}

which should give you something like the format they want:

{'geometryType': 'esriGeometryPoint', 'spatialReference': {'wkid': 4326}, 

'fields': [{'name': 'site_id', 'type': 'esriFieldTypeString', 'alias': 'site_id', 'length': 255}, ...], 

'features': [{'geometry': {'x': '-77.581618', 'y': '37.44901'}, 'attributes': {'site_id': 'CAP01', 'site_name': 'Rockwood Park', 'site_loop': 'CAP', 'site_number': '1', ... }]}

write it to a txt file on disk, or assign to a variable:

with open(r'C:\Users\...\Documents\ArcGIS\cnvrted.json', 'w') as f:
   json.dump(esri_json, f)

json_object = json.dumps(esri_json)

Then pass it to the method as before.

I have another version that iterates over the json features using a an insert cursor that I can share but this method is more dynamic.

This gave me two points, and a lot of stuff in the attribute table.

[jaykapalczynski]

OK so great making progress here...

So I added/modified the code and its reading ok but not in the order you specified

I was wondering if that was OK????

You can see it writes Fields - geometry type - features - spatial reference  

But in the code I specified 

esri_json = {"geometryType" : "esriGeometryPoint",
             "spatialReference" : {"wkid" : 4326},
            'fields': [{"name": str(k), "type": "esriFieldTypeString", "alias": str(k), "length": 255} for k, v in data[0].items()],
            'features': converted_list}

{'fields': [{'alias': 'site_name', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_name'}, {'alias': 'insects', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'insects'}, {'alias': 'site_number', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_number'}, {'alias': 'site_id', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_id'}, {'alias': 'seabirds', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'seabirds'}, {'alias': 'black_bear', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'black_bear'}, {'alias': 'site_ebird_url', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_ebird_url'}, {'alias': 'shorebirds', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'shorebirds'}, {'alias': 'loop_name', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'loop_name'}, {'alias': 'last_updated', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'last_updated'}, {'alias': 'lizards', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'lizards'}, {'alias': 'hawks', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'hawks'}, {'alias': 'frogs', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'frogs'}, {'alias': 'site_ebird_site_id', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_ebird_site_id'}, {'alias': 'site_access', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_access'}, {'alias': 'site_loop', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_loop'}, {'alias': 'site_season', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_season'}, {'alias': 'carto_id', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'carto_id'}, {'alias': 'cms_id', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'cms_id'}, {'alias': 'general_birding', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'general_birding'}, {'alias': 'phase', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'phase'}, {'alias': 'site_contact', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_contact'}, {'alias': 'site_latitude', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_latitude'}, {'alias': 'marine_mammals', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'marine_mammals'}, {'alias': 'bald_eagle', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'bald_eagle'}, {'alias': 'site_longitude', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_longitude'}, {'alias': 'site_website', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_website'}, {'alias': 'site_facilities', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_facilities'}, {'alias': 'site_description', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_description'}, {'alias': 'waterfowl', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'waterfowl'}, {'alias': 'grassland_birds', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'grassland_birds'}], 'geometryType': 'esriGeometryPoint', 'features': [{'geometry': {'y': 37.44901, 'x': -77.581618}, 'attributes': {u'site_name': u'Rockwood Park', u'insects': u'Unknown', u'site_number': u'1', u'site_id': u'CAP01', u'seabirds': u'Na', u'black_bear': u'Unknown', u'site_ebird_url': u'https:ebird.orgbarchart?r=L617671&yr=all&m=', u'shorebirds': u'Na', u'loop_name': u'Appomattox Loop', u'last_updated': u'May 20, 2022', u'lizards': u'Unknown', u'hawks': u'Na', u'frogs': u'Unknown', u'site_ebird_site_id': u'L617671', u'site_access': u'Free, Daily', u'site_loop': u'CAP', u'site_season': u'All', u'carto_id': u'01', u'cms_id': u'4799', u'general_birding': u'Available', u'phase': u'Coastal Phase', u'site_contact': u'Chesterfield', u'site_latitude': 37.44901, u'marine_mammals': u'Na', u'bald_eagle': u'Unknown', u'site_longitude': -77.581618, u'site_website': u'www.chesterfield.gov3824Rockwood-Park', u'site_facilities': "[u'environmental_study_area', u'handicap_accessible', u'hiking_trails', u'information', u'interpretive_nature_program', u'parking', u'phone', u'picnic', u'restrooms']", u'site_description': u'This 160-acre', u'waterfowl': u'Na', u'grassland_birds': u'Unknown'}}], 'spatialReference': {'wkid': 4326}}

[jaykapalczynski]

OK you can see my result in the other post... the resulting JSON is out of order as to what is specified in the python script....I am using this code to try and create the Feature Class but getting an error...

I get this error:

Traceback (most recent call last):

  File "C:/Users/t/Desktop/JSONSamples/AddPoints.py", line 47, in <module>

    arcpy.JSONToFeatures_conversion(json_object, os.path.join("outgdb.gdb", "myfeatures"))

  File "C:\Program Files (x86)\ArcGIS\Desktop10.8\ArcPy\arcpy\conversion.py", line 602, in JSONToFeatures

    raise e

ExecuteError: Failed to execute. Parameters are not valid.

ERROR 000814: Invalid file type

Failed to execute (JSONToFeatures).

import arcpy
import requests
import json
import uuid
import os

arcpy.env.workspace = "C:/Users/t/Desktop/JSONSamples/"

dataInput = r"C:/Users/t/Desktop/JSONSamples/JSON4.json"

f = open(dataInput)
data = json.load(f)

data1 = {"BirdTrailLocations":  data }

converted_list = []
for i in data1['BirdTrailLocations']:
    
    feat_dict = {} 
    feat_dict["geometry"] = {"x": i["site_longitude"], "y": i["site_latitude"]}
    # the list created a value type is incompatible with the field type. [site_facilities] so we nee dto convert it to a string
    i["site_facilities"] = str(i["site_facilities"])

    feat_dict["attributes"] = i
    converted_list.append(feat_dict)

# Closing file
f.close()

esri_json = {"geometryType" : "esriGeometryPoint",
             "spatialReference" : {"wkid" : 4326},
            'fields': [{"name": str(k), "type": "esriFieldTypeString", "alias": str(k), "length": 255} for k, v in data[0].items()],
            'features': converted_list}

json_object = json.dumps(esri_json)

print json_object
print ""

arcpy.JSONToFeatures_conversion(json_object, os.path.join("outgdb.gdb", "myfeatures"))

[jaykapalczynski]

I just do not think I am getting to the outgdb.gdb correctly.... I think this is a pathing issue?  Not sure

c:/Users/t/Desktop/JSONSamples/outgdb.gdb

[Anonymous User · Accepted solution]

I found out this morning JSONToFeatures wants a file so you'll need to write it to disk and load it from there.  I did a little bit of data cleanup using an update cursor, which I assume would be your next step once you got the data loaded.  You can alter fields from TEXT to other datatypes as well if you want.

Edit to note: You'll need to import re for some regex to fix the values.  I think your gdb path is ok and the order is ok, I truncated my esri_json a bit to avoid redundancy.

converted_list = []
for json_dict in distr:
    feat_dict = {}
    feat_dict["geometry"] = {"x": json_dict["site_longitude"], "y": json_dict["site_latitude"]}
    # the list created a value type is incompatible with the field type. [site_facilities] so we nee dto convert it to a string
    json_dict["site_facilities"] = str(json_dict["site_facilities"])
    feat_dict["attributes"] = json_dict
    converted_list.append(feat_dict)

new_dict = {"geometryType" : "esriGeometryPoint",
             "spatialReference" : {"wkid" : 4326},
            'fields': [{"name": str(k), "type": "esriFieldTypeString", "alias": str(k), "length": 255} for k, v in distr[0].items()],
            'features': converted_list}

print(new_dict)

esri_json = r'C:\Users\...\Documents\ArcGIS\cnvrted.json'

with open(esri_json, 'w') as f:
    json.dump(new_dict, f)

output_fc = r'C:\Users\...\Documents\ArcGIS\Projects\Clerk_LocalData\Clerk_LocalData.gdb\test'

arcpy.JSONToFeatures_conversion(esri_json, output_fc)

#####  Clean up the fields.
flds = [f.name for f in arcpy.ListFields(output_fc)]

p_tag = re.compile(r'.*?>(.*)<')

def extract_p_tag(invalue):
    result = p_tag.search(invalue)
    newVal = result.group(1)
    arcpy.AddMessage(f'Fixing: {invalue} to {newVal}')
    return newVal

def fix_website(invalue):
    newVal = re.sub(r'/', r'\\', invalue)
    newVal = newVal.replace('\\\\', '\\')
    arcpy.AddMessage(f'Fixing: {invalue} to {newVal}')
    return str(newVal)

def fix_facilities(invalue):
    newVal = invalue.replace('[', '').replace(']', '').replace(r"'", r'')
    arcpy.AddMessage(f'Fixing: {invalue} to {newVal}')
    return newVal

def fix_na(invalue):
    newVal = invalue.replace('\/', '\\')
    arcpy.AddMessage(f'Fixing: {invalue} to {newVal}')
    return str(newVal)

with arcpy.da.UpdateCursor(output_fc, flds) as uCur:
    for row in uCur:
        row[flds.index('site_description')] = extract_p_tag(row[flds.index('site_description')])
        row[flds.index('site_website')] = fix_website(row[flds.index('site_website')])
        row[flds.index('site_ebird_url')] = fix_website(row[flds.index('site_ebird_url')])
        row[flds.index('site_facilities')] = fix_facilities(row[flds.index('site_facilities')])
        row[flds.index('seabirds')] = fix_na(row[flds.index('seabirds')])

        uCur.updateRow(row)

[jaykapalczynski]

@Anonymous User wow thanks man... very appreciated...

Can you elaborate on: You'll need to import re for some regex to fix the values

Not sure what this is.... a simple Import re or do I need to install something

[Anonymous User]

no problem, re is the regex module I used to extract text between the <p> tags in line 32 & 33 in that last post.

import arcpy
import requests
import json
import uuid
import os
import re

[jaykapalczynski]

OK so I am trying to clean up the data and all the reformats work fine except this one...

thoughts?

ERROR:

Traceback (most recent call last):
File "C:\Users\t\Desktop\JSONSamples\AddPoints.py", line 104, in <module>
row[flds.index('site_description')] = extract_p_tag(row[flds.index('site_description')])
File "C:\Users\tjv36463\Desktop\JSONSamples\AddPoints.py", line 77, in extract_p_tag
newVal = result.group(1)
AttributeError: 'NoneType' object has no attribute 'group'

#####  Clean up the fields.
flds = [f.name for f in arcpy.ListFields(output_fc)]

p_tag = re.compile(r'.*?>(.*)<')

def extract_p_tag(invalue):
    result = p_tag.search(invalue)
    newVal = result.group(1)
    #arcpy.AddMessage(f'Fixing: {invalue} to {newVal}')
    print 'Fixing p Tag ' + result + ' to ' + newVal
    print ""
    return newVal

[Anonymous User]

That means the regex didn't find a result in one of the values, (the value didn't have any <p> tags).  You'll need to check for Nulls, and return the original value.

if result:
    newVal = result.group(1)
else:
   newVal = invalue

or as one liner:

newVal = result.group(1) if result else invalue