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

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

jaykapalczynski — Wed, 03 Aug 2022 17:11:33 GMT

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.

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

Anonymous User — Wed, 03 Aug 2022 18:00:26 GMT

There is a JSON to features arcpy method that should get you started.

json-to-features

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

jaykapalczynski — Wed, 03 Aug 2022 18:03:45 GMT

@Anonymous User did you look at my attachment... I am not sure how to specify the X Y field to get this to work... there is no geometry in the json file.

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

Anonymous User — Wed, 03 Aug 2022 18:20:42 GMT

geometry_type is an optional parameter. From the docs:

'The geometry type to convert from GeoJSON to features. This option is only used when the input is a GeoJSON file. If the GeoJSON file does not contain any of the selected geometry type, the output feature class will be empty.'

Did you give the method a try with your json and see if the method is able to parse the x/y keys as long/lats?

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

Anonymous User — Wed, 03 Aug 2022 18:50:22 GMT

@jaykapalczynski wrote:
In addition to all the fields, it only has an X and Y field which I have to convert to point geometry.

ok, so the actual keys are 'site_longitude' and 'site_latitude' as the x/y and I assume that is what you are referencing. Thought you meant they were X and Y and didn't look that deeply.

After reading the initial JSON, iterate over the them to change the keys creating a new dictionary, and converted list. Something like this:

converted_list = [] for json_dict in distr: json_dict['x'] = json_dict.pop('site_longitude') json_dict['y'] = json_dict.pop('site_latitude') converted_list.append(json_dict)

convert back to JSON:

json_object = json.dumps(converted_list)

then into the method JSON to features...

or use an insert cursor to insert the feature during the first iteration, mapping the site_long and site_lat to a point geometry and inserting it into the fc.

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

jaykapalczynski — Wed, 03 Aug 2022 18:52:52 GMT

What is "myfeatures" referring to in the example you gave me? I thought that it took 3 parameters and the third would be POINT, LINE, POLYGON etc.

arcpy.conversion.JSONToFeatures(in_json_file, out_features, {geometry_type})

EXAMPLE

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

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

jaykapalczynski — Wed, 03 Aug 2022 18:59:05 GMT

Tried this

import arcpy import os arcpy.env.workspace = "C:/Users/t/Desktop/JSONSamples/" arcpy.JSONToFeatures_conversion("JSON.json", os.path.join("outgdb.gdb", "myfeatures"), {POINT})

Error:

Traceback (most recent call last):
File "C:/Users/tjv36463/Desktop/JSONSamples/AddPoints.py", line 4, in <module>
arcpy.JSONToFeatures_conversion("JSON.json", os.path.join("outgdb.gdb", "myfeatures"), {POINT})
NameError: name 'POINT' is not defined

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

Anonymous User — Wed, 03 Aug 2022 19:07:13 GMT

When reading the docs, items in {} mean they are optional. They also have (Optional) listed under them in the parameter table. If you don't have a value for it, you can omit it.

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

Since the x,y keys have different names in the txt you provided, you'll have to do some manipulation before this method will work.

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

jaykapalczynski — Wed, 03 Aug 2022 19:09:59 GMT

If I do this I get this error

import arcpy import os arcpy.env.workspace = "C:/Users/t/Desktop/JSONSamples/" outfeatureLocation = "C:/Users/t/Desktop/JSONSamples/OutputGeodatabase" arcpy.JSONToFeatures_conversion("JSON.json", outfeatureLocation)

Traceback (most recent call last):
File "C:/Users/t/Desktop/JSONSamples/AddPoints.py", line 7, in <module>
arcpy.JSONToFeatures_conversion("JSON.json", outfeatureLocation)
File "C:\Program Files (x86)\ArcGIS\Desktop10.8\ArcPy\arcpy\conversion.py", line 602, in JSONToFeatures
raise e
ExecuteError: ERROR 001558: Error parsing json file 'JSON.json'.
Failed to execute (JSONToFeatures).

If I do this:

Traceback (most recent call last):
File "C:/Users/tjv36463/Desktop/JSONSamples/AddPoints.py", line 7, in <module>
arcpy.JSONToFeatures_conversion("JSON.json", outfeatureLocation, 'POINT')
TypeError: JSONToFeatures() takes at most 2 arguments (3 given)

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

Anonymous User — Wed, 03 Aug 2022 19:24:12 GMT

Read the entire post- At the bottom it says "Since the x,y keys have different names in the txt you provided, you'll have to do some manipulation before this method will work." I provided an example to get you started on how to do it in an earlier post.

...

To answer why these two didnt work as expected:

The first attempt, you have to load the file in as JSON using json.loads() or it doesn't know its JSON. It's saying it can't read it.

The second attempt, is because that parameter is only used for GeoJSON files, which your json is not GeoJSON. The method expects only 2 parameters for JSON files, as noted in the docs and in previous posts.

'The geometry type (the third parameter) to convert from GeoJSON to features. This option is only used when the input is a GeoJSON file. If the GeoJSON file does not contain any of the selected geometry type, the output feature class will be empty.'

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

jaykapalczynski — Wed, 03 Aug 2022 20:22:55 GMT

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).

Do I have to convert the JSON Object to something before I use the JSONToFeatures_conversion
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"))

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

Anonymous User — Wed, 03 Aug 2022 22:19:05 GMT

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:

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.

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

jaykapalczynski — Thu, 04 Aug 2022 12:35:33 GMT

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

{'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}}

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

jaykapalczynski — Thu, 04 Aug 2022 12:42:58 GMT

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"))

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

jaykapalczynski — Thu, 04 Aug 2022 13:16:37 GMT

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

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

Anonymous User — Thu, 04 Aug 2022 13:24:14 GMT

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)

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

jaykapalczynski — Thu, 04 Aug 2022 13:29:46 GMT

@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

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

Anonymous User — Thu, 04 Aug 2022 13:32:35 GMT

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

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

jaykapalczynski — Thu, 04 Aug 2022 17:45:13 GMT

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

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

Anonymous User — Thu, 04 Aug 2022 17:57:53 GMT

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