| window.testCallback = () => { | |
| let startTime; | |
| const duration = 10000; | |
| const frame = (time) => { | |
| if (!startTime) startTime = time; | |
| const animationPhase = (time - startTime) / duration; | |
| // Reduce the visible length of the line by using a line-gradient to cutoff the line |
| // Bulk download of IntakeQ forms | |
| // This script will iterate over the list of intakes in IntakeQ, downloading | |
| // each as a PDF. It also pulls the customer's name from the list and uses it | |
| // to create the filename | |
| // this script does not paginate. To use navigate to a list of intakes, open | |
| // developer tools, paste into the console, press enter | |
| // if you have more than one page, manually navigate to the next page and repeat | |
| // select all table row elements |
| load("encoding/csv.star", "csv") | |
| load("encoding/json.star", "json") | |
| load("http.star", "http") | |
| def download(ctx): | |
| # send an http get request to the elevator outages page | |
| raw = http.get('https://traintime.lirr.org/api/ElevEsc?api_key=dwrxfpcsp42pcwxfbpd90zde5fjeb05pcdaqjw3j').body() | |
| data = json.loads(raw) | |
| stations = data['Stations'] |
| # an example of pulling in a CSV string from a local qri repo, | |
| # and parsing it into a dataframe with pandas.read_csv() | |
| import pandas as pd | |
| import subprocess | |
| from io import StringIO | |
| # this Qri class should eventually have all the methods we would need for reading, | |
| # writing, pushing, pulling, committing, etc. | |
| class Qri: |
| ## Problem | |
| When troubleshooting another bug at qri.io, we noticed that hitting the same download URL from the NYC Open Data portal (powered by Socrata) generated csv files with different md5 hashes. This was causing our version control software to consider the csv "changed". On further investigation, the actual data was the same, but the ordering of the rows was different. This is likely due to the fact that the files are being generated on the fly from a database query (PostgreSQL in the case of Socrata open data portals) returning unpredictable row orders when no `ORDER BY` is specified in the query. | |
| To reproduce: | |
| - curl the same download URL several times and save to csv, such as this one for popular baby names: | |
| ``` | |
| $curl https://data.cityofnewyork.us/resource/25th-nujf.csv > 0.csv | |
| $curl https://data.cityofnewyork.us/resource/25th-nujf.csv > 1.csv |
| // Use papa parse to download and parse a CSV | |
| Papa.parse('data/nyc_zoning_lots.csv', { | |
| download: true, | |
| header: true, | |
| complete: ({ data }) => { | |
| // transform array of objects to geojson FeatureCollection | |
| const FC = { | |
| type: 'FeatureCollection', | |
| features: data.map((row) => { // map csv rows to geojson features | |
| const geometry = wellknown.parse(row.geom) // WKT to geojson geometry |
The absolute easiest way to get a CSV into a postgresql table is to use ogr2ogr with AUTODETECT_TYPE=YES.
I learned a while back that this is what cartoDB uses to import your CSV into postgis (with a lot of other parameters added)
ogr2ogr -f PostgreSQL PG:"host=localhost user=postgres dbname=postgres password=password" docs.csv -oo AUTODETECT_TYPE=YES
| plaza,boro_community_district_1,boro_community_district_2,level,area_sf,on_street,cross_street_1,cross_street_2,partner,partner_info_url,longitude,latitude,cecm_map | |
| Albee Square Plaza,BK02,,C,25200,Dekalb Ave,Bond Street,Fulton Mall,Fulton Mall Improvement Association,http://downtownbrooklyn.com/about/fulton-mall-improvement-association,-73.983171,40.689973,https://www1.nyc.gov/assets/cecm/downloads/plaza-maps/bk-albee-square.pdf | |
| Ave C Plaza,BK12,,D,5600,McDonald Ave,Avenue C,Church Ave,Kensington Stewards,https://bklyner.com/want-a-new-pedestrian-plaza-in-kensington-attend-this-public-workshop-on-wednesday-april-29-kensington/,-73.979094,40.640571,https://www1.nyc.gov/assets/cecm/downloads/plaza-maps/bk-avenue-c-plaza.pdf | |
| Brooklyn Plaza,BK02,,B,50000,Jay Street,High Street,Manhattan Bridge,DOT,https://www1.nyc.gov/html/dot/html/home/home.shtml,-73.986569,40.699203,https://www1.nyc.gov/assets/cecm/downloads/plaza-maps/bk-brooklyn-plaza.pdf | |
| Cadman Plaza East,BK02,,,,Cadman Plaza East,Tillary St,Johnson St,Down |