Get state, county, grid square for P2P and S2S QSOs

[hcarter333]

Use the reverse geocoding api to get at least: city county state for each P2P or S2S call. (For calls to home stations, his information is already available.) Also, if possible get the grid square, although I suspect this is math, not reverse geocoding.

Look at the API at https://developers.google.com/maps/documentation/javascript/geocoding#ReverseGeocoding

And here's the (actually useful for this project server side version) https://developers.google.com/maps/documentation/geocoding/requests-reverse-geocoding Especially look at these for the side benefit of finding out where we're about to camp in less than well-defined instances like this:

[hcarter333]

A quick runthrough was not particularly satisfying for knowing where we were camping. (Spoiler: the site was on BLM property.) Here are the returned results:

{'plus_code': 
    {'global_code': '85F69XXG+JP4'}, 
    'results': [{
        'address_components': [{
            'long_name': '85F69XXG+JP', 'short_name': '85F69XXG+JP', 'types': ['plus_code']}], 
        'formatted_address': 
            '85F69XXG+JP', 
        'geometry': {'bounds': {'northeast': {'lat': 39.399125, 'lng': -115.023125}, 
                                'southwest': {'lat': 39.399, 'lng': -115.02325}}, 
                     'location': {'lat': 39.3990105, 'lng': -115.0231567}, 
                     'location_type': 'GEOMETRIC_CENTER', 
                     'viewport': {'northeast': {'lat': 39.4004114802915, 'lng': -115.0218385197085}, 
                                  'southwest': {'lat': 39.3977135197085, 'lng': -115.0245364802915}}}, 
                      'place_id': 'GhIJWyGsxhKzQ0ARuks9ZnvBXMA', 
                      'plus_code': {'global_code': '85F69XXG+JP'}, 
                      'types': ['plus_code']}, 
                      {'address_components': [{'long_name': 'Unnamed Road', 'short_name': 'Unnamed Road', 'types': ['route']}, 
                      {'long_name': 'Ely', 'short_name': 'Ely', 'types': ['locality', 'political']},
                      {'long_name': 'White Pine County', 'short_name': 'White Pine County', 'types': ['administrative_area_level_2', 'political']}, 
                      {'long_name': 'Nevada', 'short_name': 'NV', 'types': ['administrative_area_level_1', 'political']}, 
                      {'long_name': 'United States', 'short_name': 'US', 'types': ['country', 'political']}, 
                      {'long_name': '89301', 'short_name': '89301', 'types': ['postal_code']}], 
                      'formatted_address': 'Unnamed Road, Ely, NV 89301, USA', 
                      'geometry': {'bounds': {'northeast': {'lat': 39.40128199999999, 'lng': -115.0240071}, 
                      'southwest': {'lat': 39.3960956, 'lng': -115.030464}}, 
                      'location': {'lat': 39.3999007, 'lng': -115.0278111}, 
                      'location_type': 'GEOMETRIC_CENTER', 
                      'viewport': {'northeast': {'lat': 39.40128199999999, 'lng': -115.0240071}, 
                                   'southwest': {'lat': 39.3960956, 'lng': -115.030464}}}, 
                      'place_id': 'ChIJp-xCdQxJsIAROqGjnuMmftA', 'types': ['route']}, 
                      {'address_components': [{'long_name': '89301', 'short_name': '89301', 'types': ['postal_code']}, 
                      {'long_name': 'Ely', 'short_name': 'Ely', 'types': ['locality', 'political']}, 
                      {'long_name': 'White Pine County', 'short_name': 'White Pine County', 'types': ['administrative_area_level_2', 'political']}, 
                      {'long_name': 'Nevada', 'short_name': 'NV', 'types': ['administrative_area_level_1', 'political']}, 
                      {'long_name': 'United States', 'short_name': 'US', 'types': ['country', 'political']}], 
                      'formatted_address': 'Ely, NV 89301, USA', 'geometry': 
                          {'bounds': {'northeast': {'lat': 40.12275289999999, 'lng': -114.2294831}, 
                                      'southwest': {'lat': 38.7651119, 'lng': -115.4084979}},
                          'location': {'lat': 39.5699721, 'lng': -114.8092691}, 
                          'location_type': 'APPROXIMATE', 'viewport': {'northeast': {'lat': 40.12275289999999, 'lng': -114.2294831}, 
                                                                       'southwest': {'lat': 38.7651119, 'lng': -115.4084979}}}, 
                          'place_id': 'ChIJ-0eTVo5JsIAR7OHbZlcQ200', 'types': ['postal_code']}, {'address_components': [{
                          'long_name': 'White Pine County', 
                          'short_name': 'White Pine County', 'types': ['administrative_area_level_2', 'political']}, 
                          {'long_name': 'Nevada', 'short_name': 'NV', 'types': ['administrative_area_level_1', 'political']}, 
                          {'long_name': 'United States', 'short_name': 'US', 'types': [
                          'country', 'political']}], 'formatted_address': 'White Pine County, NV, USA', 
                          'geometry': {'bounds': {'northeast': {'lat': 40.12855, 'lng': -114.046385}, 
                          'southwest': {'lat': 38.677298, 'lng': -115.907659}}, 
                          'location': {'lat': 39.5564495, 'lng': -114.524264}, 
                          'location_type': 'APPROXIMATE', 
                          'viewport': {'northeast': {'lat': 40.12855, 'lng': -114.046385},
                          'southwest': {'lat': 38.677298, 'lng': -115.907659}}}, 
                          'place_id': 'ChIJT0dsM3dlpYARsqdrf0iJCRQ', 'types': 
                          ['administrative_area_level_2', 'political']}, {
                          'address_components': [{'long_name': 'Nevada', 'short_name': 'NV', 'types': ['administrative_area_level_1', 'political']}, 
                          {'long_name': 'United States', 'short_name': 'US', 'types': ['country', 'political']}], 
                          'formatted_address': 'Nevada, USA', 'geometry': {'bounds': {
                          'northeast': {'lat': 42.001842, 'lng': -114.039461}, 
                          'southwest': {'lat': 35.001857, 'lng': -120.0064729}}, 
                          'location': {'lat': 38.8026097, 'lng': -116.419389}, 'location_type': 'APPROXIMATE', 'viewport': {
                          'northeast': {'lat': 42.001842, 'lng': -114.039461}, 
                          'southwest': {'lat': 35.001857, 'lng': -120.0064729}}}, 
                          'place_id': 'ChIJcbTe-KEKmYARs5X8qooDR88', 'types': ['administrative_area_level_1', 'political']}, {'address_components': [{
                          'long_name': 'United States', 'short_name': 'US', 'types': ['country', 'political']}], 'formatted_address': 'United States', 'geometry': 
                          {'bounds': {'northeast': {'lat': 74.071038, 'lng': -66.885417}, 'southwest': {'lat': 18.7763, 'lng': 166.9999999}}, 
                          'location': {'lat': 37.09024, 'lng': -95.712891}, 
                          'location_type': 'APPROXIMATE', 'viewport': {
                          'northeast': {'lat': 74.071038, 'lng': -66.885417}, 
                          'southwest': {'lat': 18.7763, 'lng': 166.9999999}}}, 
                          'place_id': 'ChIJCzYy5IS16lQRQrfeQ5K5Oxw', 'types': ['country', 'political']}], 'status': 'OK'}

[hcarter333]

Walk through a list of QSOs. Record all the states in a list. Read through rm_rnb_history_pres.csv. If the call id is found in the states list, then replace the line with the same line containing a state. If it's not, simply output it. Copy the resulting output file back into the original and revision control commit.

[hcarter333]

It's looking like it will be easier to use the datasette counties example at https://github.com/simonw/us-counties-datasette/blob/main/README.md to achieve the goal of finding all the county and state names. I have the counties example up and running after modifying the code a bit to run on my Windows machine. e](https://github.com/simonw/datasette/issues/2198) a bit to run on my Windows machine.

Reference: extend issue: simonw/datasette#2198

[hcarter333]

Now, I need to figure out how to pass each QSO location into the counties example query as the two named parameters for latitude and longitude.

I'm using the crossdb example. I may, however, wind up just calling the query from a URL, or use some other mechanism.\

Crossdb queuries, (and for that matter any query) cannot be used if the metadata.json file is included when the counties example is started up. However, if the metadata file is left out of the input example, you can write SQL queries. The .json file restricts authoring queries to the user simon.

[hcarter333]

This small example serves as the kernel for what I want to do. Once the datasette server is up and running, Python can get csv results of the county query like this:

#now test just calling a datasette query with requests
ds_geo = requests.get('http://127.0.0.1:8001/counties.csv?sql=select%0D%0A++STATEFP+as+state_fips%2C%0D%0A++states.abbreviation+as+state%2C%0D%0A++STATEFP+%7C%7C+COUNTYFP+as+county_fips%2C%0D%0A++counties.NAME+as+county_name%2C%0D%0A++COUNTYNS%2C%0D%0A++AFFGEOID%2C%0D%0A++GEOID%2C%0D%0A++LSAD%2C%0D%0A++ALAND%2C%0D%0A++AWATER%0D%0Afrom%0D%0A++counties+join+states+on+counties.STATEFP+%3D+states.fips%0D%0Awhere%0D%0A++within%28%0D%0A++++MakePoint%28cast%28%3Alongitude+as+float%29%2C+cast%28%3Alatitude+as+float%29%29%2C%0D%0A++++counties.Geometry%0D%0A++%29+%3D+1+and+counties.rowid+in+%28%0D%0A++++select%0D%0A++++++rowid%0D%0A++++from%0D%0A++++++SpatialIndex%0D%0A++++where%0D%0A++++++f_table_name+%3D+%27counties%27%0D%0A++++++and+search_frame+%3D+MakePoint%28cast%28%3Alongitude+as+float%29%2C+cast%28%3Alatitude+as+float%29%29%0D%0A++%29%0D%0Alimit%0D%0A++1&longitude=-122.42293617440531&latitude=37.72489522009444&_size=max')
print(ds_geo.text)

Similarly, it can get json results by using the URL for the 'This data as json, CSV' links

We can also use this to get the rows that are QSOs via Python like so:

ds_geo_json = requests.get('http://127.0.0.1:8001/counties.json?sql=select%0D%0A++STATEFP+as+state_fips%2C%0D%0A++states.abbreviation+as+state%2C%0D%0A++STATEFP+%7C%7C+COUNTYFP+as+county_fips%2C%0D%0A++counties.NAME+as+county_name%2C%0D%0A++COUNTYNS%2C%0D%0A++AFFGEOID%2C%0D%0A++GEOID%2C%0D%0A++LSAD%2C%0D%0A++ALAND%2C%0D%0A++AWATER%0D%0Afrom%0D%0A++counties+join+states+on+counties.STATEFP+%3D+states.fips%0D%0Awhere%0D%0A++within%28%0D%0A++++MakePoint%28cast%28%3Alongitude+as+float%29%2C+cast%28%3Alatitude+as+float%29%29%2C%0D%0A++++counties.Geometry%0D%0A++%29+%3D+1+and+counties.rowid+in+%28%0D%0A++++select%0D%0A++++++rowid%0D%0A++++from%0D%0A++++++SpatialIndex%0D%0A++++where%0D%0A++++++f_table_name+%3D+%27counties%27%0D%0A++++++and+search_frame+%3D+MakePoint%28cast%28%3Alongitude+as+float%29%2C+cast%28%3Alatitude+as+float%29%29%0D%0A++%29%0D%0Alimit%0D%0A++1&longitude=-122.42293617440531&latitude=37.72489522009444')
z = json.loads(ds_geo_json.text)
for result in z["rows"]:
    print(result)

Here are both formats printed by a script:

[hcarter333]

But, what happens if the point is not in a county? With the end of the json URL as

&longitude=0&latitude=37

we get an epmty array back for rows:

{"ok": true, "database": "counties", "query_name": null, "rows": [], "truncated": false, "columns": ["state_fips", "state", "county_

[hcarter333]

Now, we take all the QSOs using the same technique and pass them to the county query, then output the results. Here's the basics of the code as found in get_stctcty.py:

#Call a datasette query with requests
ds_geo = requests.get('http://127.0.0.1:8001/counties.csv?sql=select%0D%0A++STATEFP+as+state_fips%2C%0D%0A++states.abbreviation+as+state%2C%0D%0A++STATEFP+%7C%7C+COUNTYFP+as+county_fips%2C%0D%0A++counties.NAME+as+county_name%2C%0D%0A++COUNTYNS%2C%0D%0A++AFFGEOID%2C%0D%0A++GEOID%2C%0D%0A++LSAD%2C%0D%0A++ALAND%2C%0D%0A++AWATER%0D%0Afrom%0D%0A++counties+join+states+on+counties.STATEFP+%3D+states.fips%0D%0Awhere%0D%0A++within%28%0D%0A++++MakePoint%28cast%28%3Alongitude+as+float%29%2C+cast%28%3Alatitude+as+float%29%29%2C%0D%0A++++counties.Geometry%0D%0A++%29+%3D+1+and+counties.rowid+in+%28%0D%0A++++select%0D%0A++++++rowid%0D%0A++++from%0D%0A++++++SpatialIndex%0D%0A++++where%0D%0A++++++f_table_name+%3D+%27counties%27%0D%0A++++++and+search_frame+%3D+MakePoint%28cast%28%3Alongitude+as+float%29%2C+cast%28%3Alatitude+as+float%29%29%0D%0A++%29%0D%0Alimit%0D%0A++1&longitude=-122.42293617440531&latitude=37.72489522009444&_size=max')
print(ds_geo.text)

qso_rx_geo = requests.get('http://127.0.0.1:8001/rm_toucans_23_10_07.json?sql=select+Spotter%2C+rx_lng%2C+rx_lat+from+rm_rnb_history_pres+where+%22dB%22+%3E+%3Ap0+order+by+rowid&p0=99')
#print(qso_rx_geo.text)
q = json.loads(qso_rx_geo.text)
for result in q["rows"]:
    #print(result)
    lng = result[1]
    lat = result[2]
    ds_geo_json = requests.get('http://127.0.0.1:8001/counties.json?sql=select%0D%0A++STATEFP+as+\
                               state_fips%2C%0D%0A++states.abbreviation+as+state%2C%0D%0A++STATEFP+%7C%7C+\
                               COUNTYFP+as+county_fips%2C%0D%0A++counties.NAME+as+county_name%2C%0D%0A++\
                               COUNTYNS%2C%0D%0A++AFFGEOID%2C%0D%0A++GEOID%2C%0D%0A++LSAD%2C%0D%0A++ALAND%2C%0D%0A++\
                               AWATER%0D%0Afrom%0D%0A++counties+join+states+on+counties.STATEFP+%3D+states.fips\
                               %0D%0Awhere%0D%0A++within%28%0D%0A++++MakePoint%28cast%28%3Alongitude+as+\
                               float%29%2C+cast%28%3Alatitude+as+float%29%29%2C%0D%0A++++counties.Geometry%0D%0A++\
                               %29+%3D+1+and+counties.rowid+in+%28%0D%0A++++select%0D%0A++++++rowid%0D%0A++++from%0D%0A++++++\
                               SpatialIndex%0D%0A++++where%0D%0A++++++f_table_name+%3D+%27counties%27%0D%0A++++++and\
                               +search_frame+%3D+MakePoint%28cast%28%3Alongitude+as+float%29%2C+cast%28%3Alatitude+\
                               as+float%29%29%0D%0A++%29%0D%0Alimit%0D%0A++1&longitude='+str(lng)+'&latitude='+str(lat))
    #print(ds_geo_json.text)
    z = json.loads(ds_geo_json.text)
    for localities in z["rows"]:
        print(result[0] + ':' + localities[1] + ' ' + localities[3])

[hcarter333]

Here are some of the results for now:

K7ULM:MT Cascade
KL7EE:AK Anchorage
N7WPO:WA Clallam
W4IDX:AZ Mohave
WM6T:CA San Bernardino
K7NK:WA Island
nn0d:MO Greene
N7LFO:OR Washington
N5URL:NM Santa Fe
KK6ZHK:AZ Yavapai
W7MTB:OR Deschutes
NM5D:CO Arapahoe

[hcarter333]

Next: output the modified rows of the rm_rbn_history_pres.csv as explained above in the issue description or first comment.