46 lines
1.2 KiB
Plaintext
46 lines
1.2 KiB
Plaintext
Open farmersmarket_2024-42231059.xlsx
|
|
Name it: usda_farmers_markets
|
|
|
|
Geotag: Lon - Y; Lat - X
|
|
|
|
Geoplot
|
|
- 1. too much data
|
|
- 2. oops, flipped
|
|
|
|
Alter geotag. See geoplot rerun
|
|
|
|
Still too much data. Have a .shp file, but vizier doesn't support an adaptor. Python:
|
|
-------------------
|
|
# Extract County Shapes
|
|
import shapefile
|
|
|
|
with shapefile.Reader("cb_2018_us_county_500k.zip") as sf:
|
|
#for field in sf.fields:
|
|
# print(field)
|
|
# Get object containing an empty dataset.
|
|
ds = vizierdb.new_dataset()
|
|
ds.insert_column("county")
|
|
ds.insert_column("zip")
|
|
ds.insert_column("geometry", "geometry")
|
|
for entry in sf.shapeRecords():
|
|
if entry.record[0] == '36': # 36 is NYS
|
|
row = [ entry.record[5], entry.record[4], entry.shape ]
|
|
#print(row)
|
|
ds.insert_row( row )
|
|
ds.save("nys_counties")
|
|
ds.show()
|
|
-------------------
|
|
|
|
Spatial join
|
|
-------------------
|
|
SELECT *
|
|
FROM nys_counties nys
|
|
JOIN wny_counties wny ON nys.county = wny.county
|
|
-------------------
|
|
|
|
Add below, and name: usda_farmers_markets
|
|
-------------------
|
|
JOIN usda_farmers_markets f ON ST_CONTAINS(nys.geometry, f.geometry)
|
|
-------------------
|
|
|
|
Watch updated chart |