Covid19 Cases Feature Layer Data and ESRI DUMPER

[Dunn_Jeremy]

Morning All

I want to download the Covid19 Daily Cases data from here

https://services1.arcgis.com/0MSEUqKaxRlEPj5g/ArcGIS/rest/services/Coronavirus_2019_nCoV_Cases/Featu... 

I have tried this way.

1. ESRI DUMPER - Works but then I have difficulty converting the ESRI geojson to something that is usable in a DataBase.

Any thoughts on the best way to convert this output to a SQL readable format ? CSV/TAB delimited flat file for example ? 

Many thanks 

Regards. 

Jeremy. 

[RandyBurton]

You can use the json module to process the geojson.  I was testing the following using python 2.7.  It makes the URL request and writes a tab delimited file.

import urllib, urllib2, json, sys, time, collections

fw = open("covidUpdate.txt", "w") # file to write

URL = "https://services1.arcgis.com/0MSEUqKaxRlEPj5g/ArcGIS/rest/services/Coronavirus_2019_nCoV_Cases/FeatureServer/1/query" # query this feature

# using GET method
query_dict = {
    "where" : "1=1", # something always true
    "outFields" : "*",
    "f": "geojson" }

# results in geojson format
jsonResponse = urllib.urlopen(URL, urllib.urlencode(query_dict))

features = json.loads(jsonResponse.read(),
                      object_pairs_hook=collections.OrderedDict)[u'features']# using the features section

# Header
fw.writelines("{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\n".format(
    'OBJECTID','Province_State','Country_Region','Last_Update','Latitude','Longitude','Confirmed','Recovered','Deaths'))

# Data rows
for f in features:
    fw.writelines("{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\n".format(
        f['properties']['OBJECTID'],
        f['properties']['Province_State'] if f['properties']['Province_State'] is not None else '',
        f['properties']['Country_Region'],
        # convert timestamp to local time
        # time.strftime('%c', time.localtime(f['properties']['Last_Update']/1000)) if f['properties']['Last_Update'] is not None else '',
        # convert timestamp to GMT
        time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime(f['properties']['Last_Update']/1000)) if f['properties']['Last_Update'] is not None else '',
        f['properties']['Lat'],
        f['properties']['Long_'],
        f['properties']['Confirmed'],
        f['properties']['Recovered'],
        f['properties']['Deaths']))

fw.close()
print 'Done.'‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Results:

OBJECTID	Province_State	Country_Region	Last_Update	Latitude	Longitude	Confirmed	Recovered	Deaths
1	Abruzzo	Italy	2020-05-22 23:32:40	42.35122196	13.39843823	3220	1647	394
2	Acre	Brazil	2020-05-22 23:32:40	-9.0238	-70.812	3343	0	80
3	Aguascalientes	Mexico	2020-05-22 23:32:40	21.8853	-102.2916	586	402	21
.....‍‍‍‍‍‍‍‍‍‍

I noticed that Provence_State is sometimes null/None, as was the timestamp on occasion.  I kept the time in GMT/UTC, but you can convert it to local time if desired.  It is also possible to use an insert cursor and create a feature layer if desired.

This should give you some ideas.

[RandyBurton]

If you requested json instead of geojson, you could use the JSONToFeatures  tool (Pro version) to import into ArcMap.

My test script using python 2.7:

import arcpy, urllib, urllib2, json

URL = "https://services1.arcgis.com/0MSEUqKaxRlEPj5g/ArcGIS/rest/services/Coronavirus_2019_nCoV_Cases/FeatureServer/1/query" # query this feature

# using GET method
query_dict = {
    "where" : "1=1",
    "outFields" : "*",
    "f": "json" }

# results in json format
response = urllib.urlopen(URL, urllib.urlencode(query_dict))

with open('covid_update.json', 'w') as f:
    f.write(response.read())
    
arcpy.JSONToFeatures_conversion(in_json_file="covid_update.json",
                                out_features="C:/Path/to/file.gdb/covid_update")

print 'Done.'‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[Dunn_Jeremy]

Good Afternoon Randy

Many thanks for the post. After a little bit of re-working I successfully created a script that worked.

Many many thanks. The only issue I have is that the feature layer I am querying has 3466 features in it and the Max Record Count is limited to 1000. Is there anyway to increase this ? 

Regards. 

Jeremy. 

[Dunn_Jeremy]

Morning Randy

I solved this by simply running a where statement on the ObjectID's. 

Where "ObjectID>1000"

Seemed to work. 

Regards. 

Jeremy.