mheadd/get-data.md

## get-data.md

      
    Raw
  

              get-data.md
            
          
    Download date on vacancy licesnses by month (the API limits resonses to 1000 records per request). For example:
curl -s 'http://services.phila.gov/PhillyApi/Data/v1.0/licenses?$filter=(issued_datetime%20gt%20datetime%272015-01-01%27)%20and%20(issued_datetime%20lt%20datetime%272015-01-31%27)%20and%20(license_type_name%20eq%20%27RESIDENTIAL%20RES_VACANT%27)&$expand=locations&$format=json'

This will return a JSON response like this:
{
  "locations": {
    "locationhistories": {
      "__deferred": {
        "uri": "http://api.phila.gov/li/v1/locations(118395)/locationhistories"
      }
    },
    "violationdetails": {
      "__deferred": {
        "uri": "http://api.phila.gov/li/v1/locations(118395)/violationdetails"
      }
    },
    "licenses": {
      "__deferred": {
        "uri": "http://api.phila.gov/li/v1/locations(118395)/licenses"
      }
    },
    "permits": {
      "__deferred": {
        "uri": "http://api.phila.gov/li/v1/locations(118395)/permits"
      }
    },
    "cases": {
      "__deferred": {
        "uri": "http://api.phila.gov/li/v1/locations(118395)/cases"
      }
    },
    "appealhearings": {
      "__deferred": {
        "uri": "http://api.phila.gov/li/v1/locations(118395)/appealhearings"
      }
    },
    "buildingboardappeals": {
      "__deferred": {
        "uri": "http://api.phila.gov/li/v1/locations(118395)/buildingboardappeals"
      }
    },
    "lireviewboardappeals": {
      "__deferred": {
        "uri": "http://api.phila.gov/li/v1/locations(118395)/lireviewboardappeals"
      }
    },
    "zoningboardappeals": {
      "__deferred": {
        "uri": "http://api.phila.gov/li/v1/locations(118395)/zoningboardappeals"
      }
    },
    "ward": "40",
    "census_block": null,
    "city": "PHILADELPHIA",
    "street_suffix": "AVE",
    "street_name": "DICKS",
    "street_direction": " ",
    "postfix": " ",
    "street_number": " 07635",
    "location_id": 118395,
    "__metadata": {
      "type": "PhillyAPI.locations",
      "uri": "http://api.phila.gov/li/v1/locations(118395)"
    },
    "state": "PA",
    "zip": "19153-1816",
    "x": "2671292",
    "y": "218915",
    "census_tract": "055",
    "council_district": "02",
    "condo_unit": "0000000",
    "unit_number": "0000000"
  },
  "location_id": 118395,
  "pri_contact_zip": "45069",
  "pri_contact_state": "OH",
  "inactive_datetime": null,
  "expires_year": "2016",
  "expires_month": "FEBRUARY ",
  "issued_datetime": "/Date(1422092912000)/",
  "license_type_name": "RESIDENTIAL RES_VACANT",
  "license_type_code": "3219",
  "license_number": "618755",
  "__metadata": {
    "type": "PhillyAPI.licenses",
    "uri": "http://api.phila.gov/li/v1/licenses('618755')"
  },
  "status": "ACTIVE",
  "pri_contact_type": "APPLICANT",
  "pri_contact_company_name": null,
  "pri_contact_last_name": "PNC MORTGAGE",
  "pri_contact_first_name": " ",
  "pri_contact_address1": "7681 TYLERS PLACE BLVD",
  "pri_contact_address2": null,
  "pri_contact_city": "WEST CHESTER"
}
Pipe the curl response to a tool that can parse JSON - like jq - and store as CSV:
echo "license_number,license_type_code,license_type_name,issued_datetime,expires_month,expires_year,inactive_datetime,status,pri_contact_type,pri_contact_company_name,pri_contact_last_name,pri_contact_first_name,pri_contact_address1,pri_contact_address2,pri_contact_city,pri_contact_state,pri_contact_zip,location_id,locations_location_id,locations_street_number,locations_postfix,locations_street_direction,locations_street_name,locations_street_suffix,locations_city,locations_state,locations_zip,locations_x,locations_y,locations_census_tract,locations_council_district,locations_condo_unit,locations_unit_number,locations_census_block,locations_ward" > residential-vacancy-permits.csv

curl -s 'http://services.phila.gov/PhillyApi/Data/v1.0/licenses?$filter=(issued_datetime%20gt%20datetime%272015-01-01%27)%20and%20(issued_datetime%20lt%20datetime%272015-01-31%27)%20and%20(license_type_name%20eq%20%27RESIDENTIAL%20RES_VACANT%27)&$expand=locations&$format=json' \
| jq .d.results[] \
| jq '[.license_number, .license_type_code, .license_type_name, .issued_datetime, .expires_month, .expires_year, .inactive_datetime, .status, .pri_contact_type, .pri_contact_company_name, .pri_contact_last_name, .pri_contact_first_name, .pri_contact_address1, .pri_contact_address2, .pri_contact_city, .pri_contact_state, .pri_contact_zip, .location_id, .locations.location_id, .locations.street_number, .locations.postfix, .locations.street_direction, .locations.street_name, .locations.street_suffix, .locations.city, .locations.state, .locations.zip, .locations.x, .locations.y, .locations.census_tract, .locations.council_district, .locations.condo_unit, .locations.unit_number, .locations.census_block, .locations.ward]' \
| jq @csv \
| sed 's/\\//g;s/""/"/g' \
>> residential-vacancy-permits.csv

Upload CSV file to CartoDB and update geomoetry (in SQL console), like so:
UPDATE philadelphia_residential_vacancy_permits_2015 SET the_geom = ST_Transform(ST_SetSRID(ST_Point(locations_x, locations_y), 2272), 4326)

Map that jawn!