topic Re: Extract Data from Open Data in Python Questions

Extract Data from Open Data

kapalczynski — Tue, 07 Mar 2023 19:56:24 GMT

I have this URL: https://geohub-vadeq.hub.arcgis.com/datasets/57759688e4944bb987add68c4f0c5ada_104/explore?location=37.930449%2C-79.486800%2C7.65

https://geohub-vadeq.hub.arcgis.com/datasets/57759688e4944bb987add68c4f0c5ada_104/about

I want to use python/arcpy and download all its data. If I use the RestEndpoint there is a limit of 1000 records. I cant seem to get all 50k.

Any ideas on how I can programmatically ? Looking to get a GeoJSON or JSON and write it to a JSON file locally...

Re: Extract Data from Open Data

kapalczynski — Tue, 07 Mar 2023 20:23:41 GMT

I can manually do it here but I want to do this programmatically

Re: Extract Data from Open Data

AaronCole1 — Tue, 07 Mar 2023 20:52:34 GMT

I do this kind of thing all the time! Love data mining. You have to group your requests in batches of whatever the max number of records that will be returned is, in this case 1000. Here's a code snippet I modified from something I use regularly. You can set it up to run on task to be fully automated. You'll have to put in your desired output paths and it should run.

import arcpy import requests import json import os import sys import traceback from datetime import date arcpy.env.overwriteOutput = True class DataScraper(): def __init__(self): # URL to map service you want to extract data from self.service_url = 'https://apps.deq.virginia.gov/arcgis/rest/services/public/EDMA/MapServer/104' def getServiceProperties(self, url): URL = url PARAMS = {'f' : 'json'} r = requests.get(url = URL, params = PARAMS) service_props = r.json() return service_props def getLayerIds(self, url, query=None): URL = url + '/query' PARAMS = {'f':'json', 'returnIdsOnly': True, 'where' : '1=1'} if query: PARAMS['where'] = "ST = '{}'".format(query) r = requests.get(url = URL, params = PARAMS) data = r.json() return data['objectIds'] def getLayerDataByIds(self, url, ids): # ids parameter should be a list of object ids URL = url + '/query' field = 'OBJECTID' value = ', '.join([str(i) for i in ids]) PARAMS = {'f': 'json', 'where': '{} IN ({})'.format(field, value), 'returnIdsOnly': False, 'returnCountOnly': False, 'returnGeometry': True, 'outFields': '*'} r = requests.post(url=URL, data=PARAMS) layer_data = r.json() return layer_data # UTILITIES def chunks(self, lst, n): # Yield successive n-sized chunks from list for i in range(0, len(lst), n): yield lst[i:i + n] if __name__ == '__main__': # Can use date for naming iterative runs... todays_date = date.today().strftime("%Y_%m_%d") # Instantiate DataScraper class ds = DataScraper() # Specify where you want to save output JSON and feature class on your machine out_json_path = rf"e:/data{todays_date}.json" out_fc_path = rf"e:/data{todays_date}.shp" # Function call to DataScraper class to get number of records, chunk up OIDs # and send requests to serivce. Results are compiled into single JSON and then # converted to shapefile/feature class at the end. def scrapeData(out_json_path, out_fc_path): try: service_props = ds.getServiceProperties(ds.service_url) max_record_count = service_props['maxRecordCount'] layer_ids = ds.getLayerIds(ds.service_url) id_groups = list(ds.chunks(layer_ids, max_record_count)) for i, id_group in enumerate(id_groups): print(' group {} of {}'.format(i+1, len(id_groups))) layer_data = ds.getLayerDataByIds(ds.service_url, id_group) if i==0: # If first iteration of id_groups layer_data_final = layer_data else: layer_data_final['features'].extend(layer_data['features']) print('Writing JSON file...') with open(out_json_path, 'w') as out_json_file: json.dump(layer_data_final, out_json_file) print('Converting JSON to features...') arcpy.conversion.JSONToFeatures(out_json_path, out_fc_path) except Exception: # Handle errors accordingly...this is generic tb = sys.exc_info()[2] tb_info = traceback.format_tb(tb)[0] pymsg = f'PYTHON ERRORS:\n\tTraceback info:\t{tb_info}\n\tError Info:\t{str(sys.exc_info()[1])}\n' msgs = f'ArcPy ERRORS:\t{arcpy.GetMessages(2)}\n' print(pymsg) print(msgs) # Run the function to get your data scrapeData(out_json_path, out_fc_path)

Re: Extract Data from Open Data

kapalczynski — Tue, 07 Mar 2023 23:33:12 GMT

@AaronCole1 Dang man.... thats awesome.... this one is going into Source Safe..... Cheers Dude

Re: Extract Data from Open Data

DonMorrison1 — Wed, 08 Mar 2023 00:09:37 GMT

That's a very nice program @AaronCole1 . I've been doing a lot of scraping recently and have had good luck using FeatureClassToFeatureClass. It only works on one layer at a time, you have to feed it the REST service URL, and doesn't write to JSON like you want....

arcpy.conversion.FeatureClassToFeatureClass(in_url, os.path.dirname(out_fc), os.path.basename(out_fc))

Re: Extract Data from Open Data

AaronCole1 — Wed, 08 Mar 2023 00:29:22 GMT

@DonMorrison1 lol!!! 100 lines of code...or one? Thanks for posting. The simplicity of this versus iterating over the service and compiling the JSON responses is striking. I have the above code for an old customer that needed GeoJSON and it's faster to poll the service, but in the end who's counting!? In this case, if you just want the output, brevity wins! Well played sir!

Re: Extract Data from Open Data

kapalczynski — Wed, 08 Mar 2023 01:07:05 GMT

Update: Dont mind this remark

Re: Extract Data from Open Data

AaronCole1 — Wed, 08 Mar 2023 00:51:48 GMT

You should just be able to replace the out_fc_path variable with the path to your data...that could be an sde feature class. You'd point to your connection file, something like "P:/Folder/Folder/ConnectionFile.sde/GIS.DBO.FeatureClassName"

Re: Extract Data from Open Data

kapalczynski — Wed, 08 Mar 2023 01:01:59 GMT

@DonMorrison1 that was even slicker... I love both options and learning a ton .... I thank both of you.... Didn't think I could do this that way...

I know I can get rid of some of the imports.... from the last script....

import arcpy import requests import json import os import sys import traceback from datetime import date arcpy.env.overwriteOutput = True def copyData(): # Set local variables inFeatures = 'https://apps.deq.virginia.gov/arcgis/rest/services/public/EDMA/MapServer/102' outLocation = r"C:\Users\xx\Desktop\GIS_projects\TestingExportData\output.gdb" outFeatureClass = "TEST_PetTankFac" ## Add Expression #delimitedField = arcpy.AddFieldDelimiters(arcpy.env.workspace, "NAME") #expression = delimitedField + " = 'Post Office'" # Run FeatureClassToFeatureClass arcpy.conversion.FeatureClassToFeatureClass(inFeatures, outLocation, outFeatureClass) if __name__ == '__main__': copyData()

Re: Extract Data from Open Data

kapalczynski — Wed, 08 Mar 2023 01:09:11 GMT

Got it working... Rocking...

Just wrote it to a FGDB for now... but SDE next per your last comment.... CHEERS

import arcpy import requests import json import os import sys import traceback from datetime import date arcpy.env.overwriteOutput = True def copyData(): # Set local variables inFeatures = 'https://apps.deq.virginia.gov/arcgis/rest/services/public/EDMA/MapServer/102' outLocation = r"C:\Users\xxx\Desktop\GIS_projects\TestingExportData\output.gdb" outFeatureClass = "TEST_PetTankFac" ## Add Expression #delimitedField = arcpy.AddFieldDelimiters(arcpy.env.workspace, "NAME") #expression = delimitedField + " = 'Post Office'" # Run FeatureClassToFeatureClass arcpy.conversion.FeatureClassToFeatureClass(inFeatures, outLocation, outFeatureClass) if __name__ == '__main__': copyData()

Re: Extract Data from Open Data

Anonymous User — Wed, 08 Mar 2023 01:10:07 GMT

That is something else when the method named 'FeatureClassToFeatureClass' does what 'JSONToFeatures_conversion' sounds like it should do...

It looks like conversion.FeatureClassToFeatureClass is deprecated and transitioned to conversion.ExportFeatures at 3.1

Re: Extract Data from Open Data

kapalczynski — Wed, 08 Mar 2023 01:11:23 GMT

My question is the path and name are in one... its looking for 3 parameters

"P:/Folder/Folder/ConnectionFile.sde/GIS.DBO.FeatureClassName"

arcpy.conversion.FeatureClassToFeatureClass(inFeatures, outLocation, outFeatureClass

Re: Extract Data from Open Data

Anonymous User — Wed, 08 Mar 2023 01:36:58 GMT

it would be like this:

outLocation = "P:/Folder/Folder/ConnectionFile.sde"

outFeatureClass = 'FeatureClassName'

Re: Extract Data from Open Data

kapalczynski — Wed, 08 Mar 2023 17:21:48 GMT

@Anonymous User

One more question... this works in python3.x but what about 2.7. We have not updated our servers and application on out main servers yet and still need to run in py 2.7 for the time being.... Thoughts?

arcpy.FeatureClassToFeatureClass_conversion(inFeatures, outLocation, outFeatureClass)

arcpy.conversion.FeatureClassToFeatureClass(inFeatures, outLocation, outFeatureClass)

Running from Python 3.7.11 Shell

NO Errors

Running from Python 2.7.18 Shell

Traceback (most recent call last):
File "C:\Users\xxDesktop\GIS_projects\PythonScripts\ImportData104.py", line 18, in <module>
copyData()
File "C:\Users\xxDesktop\GIS_projects\PythonScripts\ImportData104.py", line 13, in copyData
arcpy.FeatureClassToFeatureClass_conversion(inFeatures, outLocation, outFeatureClass)
File "C:\Program Files (x86)\ArcGIS\Desktop10.8\ArcPy\arcpy\conversion.py", line 1914, in FeatureClassToFeatureClass
raise e
ExecuteError: Failed to execute. Parameters are not valid.
ERROR 000732: Input Features: Dataset https://apps.deq.virginia.gov/arcgis/rest/services/public/EDMA/MapServer/104 does not exist or is not supported
Failed to execute (FeatureClassToFeatureClass).

Re: Extract Data from Open Data

Anonymous User — Wed, 08 Mar 2023 18:36:12 GMT

I suspect the 2.7 doesn't support url's as input... From the docs:

Converts a shapefile, coverage feature class, or geodatabase feature class to a shapefile or geodatabase feature class

vs Pro:

Converts a feature class or feature layer to a feature class

Re: Extract Data from Open Data

kapalczynski — Wed, 08 Mar 2023 18:40:33 GMT

Per @AaronCole1 script.... So If I create the shapefile like we were doing... what is my mechanism to get those records to a Feature Class in SDE? Delete Rows and Append? something like that?

I tried Copy Management and got this:

arcpy.Copy_management(in_data, toFeatureClass)
File "C:\Program Files (x86)\ArcGIS\Desktop10.8\ArcPy\arcpy\management.py", line 4311, in Copy
raise e
ExecuteError: Failed to execute. Parameters are not valid.
ERROR 000979: Cannot copy between different workspaces.
Failed to execute (Copy).

Re: Extract Data from Open Data

AaronCole1 — Wed, 08 Mar 2023 18:56:21 GMT

You could try to truncate first, then append. Working in an enterprise database there are often more constraints in terms of permissions, locks, versions, etc.

Re: Extract Data from Open Data

kapalczynski — Wed, 08 Mar 2023 19:11:26 GMT

@AaronCole1 than whats the difference between truncate and delete rows.

Is one faster than the other one?

UPDATE: Never mind:

Truncate commands do not use database transactions and are unrecoverable. This improves performance over row-by-row deletion.

Re: Extract Data from Open Data

Anonymous User — Thu, 09 Mar 2023 00:13:54 GMT

Just something to keep in mind with truncate, it doesn't work on versioned datasets.

If the Featureclass exists in the sde geodatabase, the delete and append method would probably work best (inplace of the truncate) because it will keep the privileges, versioning, etc.,. that are set on that Featureclass.

Re: Extract Data from Open Data

jschuckert — Mon, 09 Sep 2024 23:39:40 GMT

Have you had in cases applying this method with a secured map service? I was testing connecting with credential first then running the tool but so far no luck. I will keep testing.

Jared