cambridgema-gov/building-permits-commercial-and-multifamily-24a8-ex6a

Query the Data Delivery Network

Query the DDN

The easiest way to query any data on Splitgraph is via the "Data Delivery Network" (DDN). The DDN is a single endpoint that speaks the PostgreSQL wire procotol. Any Splitgraph user can connect to it at data.splitgraph.com:5432 and query any version of over 40,000 datasets that are hosted or proxied by Splitgraph.

For example, you can query the building_permits_commercial_and_multifamily table in this repository, by referencing it like:

"cambridgema-gov/building-permits-commercial-and-multifamily-24a8-ex6a:latest"."building_permits_commercial_and_multifamily"

or in a full query, like:

SELECT 
    ":id", -- Socrata column ID
    "hvaccostofconstruction", -- Cost of HVAC work
    "gascostofconstruction", -- Cost of gas work 
    "plumbingcostofconstruction", -- Cost of plumbing work 
    "electricalcostofconstruction", -- Cost of electrical work 
    "grosssquarefootage", -- Gross square footage area 
    "applicant", -- Name of applicant 
    "complete_date", -- Plan application completion date 
    "location_1_address",
    "parcelnumber", -- Property parcel number 
    "plannumber", -- Auto-created plan number. "BLDC" signifies commercial buildings, and "BLDM" multifamily buildings. 
    "leedrating_coreshell", -- Type of LEEDS rating system (LEED means Leadership in Energy and Environmental Design) 
    "solarpanels",
    "leedratingscredits", -- LEED rating credits (LEED means Leadership in Energy and Environmental Design) 
    "existingoffstbicycleparking", -- Off street bicycle parking associated with site
    "buildingheight", -- Building height
    "buildingcostofconstruction", -- Cost of building construction 
    "bldcnstrtype_nc_value", -- Type of building construction 
    "unitorsuite", -- Unit/Suite Number 
    "location_1_state",
    "leedrating_comercialinteriors", -- Type of LEEDS rating system (LEED means Leadership in Energy and Environmental Design) 
    "proposedworkenergyenv", -- Work will affect building's energy envelope
    "existingoffstbicycleparkingst",
    "bldgcommsprinklersystem", -- Building has a commercial sprinkler system
    "bldtype_accessorybuilding", -- Accessory Building
    "bldtype_twofamily", -- Two Family 
    "bldtype_singlefamily", -- Single Family 
    "confirmedleedratinglevel", -- Type of LEEDS rating system (LEED means Leadership in Energy and Environmental Design) 
    "proposedworknotreg", -- Proposed work does not fall under any of the above categories (reconstruction, porch/deck/stairs, new construction). The proposed work is not of the types listed above and is not regulated by the zoning ordinance. 
    "detaileddescriptionofwork", -- Description of work to be completed
    "cogenerationsystemlocation",
    "proposedworknewconst", -- Proposed work includes - new construction, additions, dormers, bays, balconies, covered stairs/landings and/or porches, decks at the 2nd floor level or higher, roof decks over the 3rd floor or higher, or to excavate a basement, change any floor or ceiling height, change the use or increase the number of dwelling units of a building, erect an outbuilding, or to do any similar work. 
    "designedleedratinglevel", -- Type of LEEDS rating system (LEED means Leadership in Energy and Environmental Design) 
    "leedrating_retail",
    "sizeoftents", -- Number of tents on building site
    "substantialdemo", -- Extent of demolition - substantial 
    "descriptionofdemolition", -- Description of Demolition
    "datetoberemoved", -- Date to be removed
    "datetobeerected", -- Date tents to be erected
    "numberoftents", -- Number of tents on building site
    "designedleedratingcredits", -- Type of LEEDS rating system (LEED means Leadership in Energy and Environmental Design) 
    "leedrating_homes",
    "proposedhersrating", -- Rating on the HERS scale for energy performance
    "cogenerationsystem",
    "stretchcodeiecc", -- The stretch code applies a performance-based code to commercial buildings, with the option of a prescriptive code for small and medium-sized commercial buildings. Buildings smaller than 5,000 square feet are exempt, as are building renovations, and “specialty” buildings – supermarkets, laboratories, and warehouses – below 40,000 square feet in size, due to their widely differing energy needs. These exempt buildings remain subject to the „base‟ Massachusetts energy code (IECC 2009 and ASHRAE 90.1-2007). 
    "pbcasenumber", -- Planning Board case number 
    "bzacasenumber", -- Board of Zoning Appeal case number 
    "zoningdistrict", -- Zoning district of property 
    "proposeduse", -- Proposed use for the facility
    "currentuse", -- Current use for the site
    "numberofproposedloadingbays",
    "proposedloadingfacilities", -- Proposed number of loading facilities. 
    "proposedoffstbicycleparkingst",
    "proposedoffstbicycleparking", -- Change in number of off street bicycle spots
    "existingoffstvehicleparking", -- Number of off street parking spots associated with site
    "totalcostofconstruction", -- Total cost of construction
    "fireprotectionsyscostofconst", -- Cost of fire protection system construction work. 
    "proposednumdwellingunits", -- Proposed number of dwelling units 
    "location_1", -- Address where work is to be performed 
    "confirmedleedratingcredits", -- Type of LEEDS rating system (LEED means Leadership in Energy and Environmental Design) 
    "proposedworkprchstrsdeck", -- Proposed work includes - enclose a covered porch, build uncovered exterior stairs, build decks at the 1st floor level, build roof decks over existing 1st or 2nd floors, create new windows (including moving existing windows), doors, or skylights. 
    "confirmedhersrating",
    ":@computed_region_swkg_bavi",
    ":@computed_region_rffn_qbt6",
    ":@computed_region_v7jj_366k",
    ":@computed_region_guic_hr4a",
    ":@computed_region_e4yd_rwk4",
    "exteriordemoworkareaonly", -- Extent of demolition - work area only 
    "bldtype_townhouse", -- Town House
    "numberofexistingloadingbays", -- Number of existing loading bays
    "windturbines",
    "windturbinessize",
    "class",
    "bldcnstrtype_mw_value", -- Type of building construction 
    "numberofstories", -- Number of stories
    "proposedoffstvehicleparking", -- Change in number of off street parking spots
    "leedratingslevel", -- LEED rating (LEED means Leadership in Energy and Environmental Design) 
    ":@computed_region_rcj3_ccgu",
    "location_1_zip",
    "stretchcodeoptions", -- The stretch code has two options: a performance approach (HERS) and a prescriptive approach
    "bldgcommfirealarm", -- Building has a commercial file alarm
    "windturbineslocation",
    "bldgcommlightingsystemchanges", -- Changes to building's lighting system
    "leedrating_newconstruction", -- Type of LEEDS rating system (LEED means Leadership in Energy and Environmental Design) 
    "leedrating_schools", -- Type of LEEDS rating system (LEED means Leadership in Energy and Environmental Design) 
    "solarpanelsuseforhotwater",
    "cogenerationsystemsize",
    "proposedworkreconst", -- Proposed work includes reconstruction of an existing exterior building element (porch, deck, etc.). 
    "twoyearvacancy", -- Was property vacant 2 years prior to application 
    "minorinteriordemo", -- Extent of demolition - minor 
    "energyconservuvalueofwindows", -- Energy conservation value of windows
    "morethan25000sqft", -- Building is more than 25,000 square feet in area. 
    "currentnumdwellingunits", -- Current number of dwelling units
    "bldcnstrtype_wood_value", -- Type of building construction 
    "applicationdate", -- Date of Application 
    "location_1_city",
    "stretchcodeexempt",
    "bldgcommsmokedetection", -- Building has a commercial smoke detector 
    "substdemostructuralelements" -- Extent of demolition - structural elements 
FROM
    "cambridgema-gov/building-permits-commercial-and-multifamily-24a8-ex6a:latest"."building_permits_commercial_and_multifamily"
LIMIT 100;

Connecting to the DDN is easy. All you need is an existing SQL client that can connect to Postgres. As long as you have a SQL client ready, you'll be able to query cambridgema-gov/building-permits-commercial-and-multifamily-24a8-ex6a with SQL in under 60 seconds.

This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.cambridgema.gov. When you querycambridgema-gov/building-permits-commercial-and-multifamily-24a8-ex6a:latest on the DDN, we "mount" the repository using the socrata mount handler. The mount handler proxies your SQL query to the upstream data source, translating it from SQL to the relevant language (in this case SoQL).

We also cache query responses on the DDN, but we run the DDN on multiple nodes so a CACHE_HIT is only guaranteed for subsequent queries that land on the same node.

Query Your Local Engine

Install Splitgraph Locally
bash -c "$(curl -sL https://github.com/splitgraph/splitgraph/releases/latest/download/install.sh)"
 

Splitgraph Cloud is built around Splitgraph Core (GitHub), which includes a local Splitgraph Engine packaged as a Docker image. Splitgraph Cloud is basically a scaled-up version of that local Engine. When you query the Data Delivery Network or the REST API, we mount the relevant datasets in an Engine on our servers and execute your query on it.

It's possible to run this engine locally. You'll need a Mac, Windows or Linux system to install sgr, and a Docker installation to run the engine. You don't need to know how to actually use Docker; sgrcan manage the image, container and volume for you.

There are a few ways to ingest data into the local engine.

For external repositories (like this repository), the Splitgraph Engine can "mount" upstream data sources by using sgr mount. This feature is built around Postgres Foreign Data Wrappers (FDW). You can write custom "mount handlers" for any upstream data source. For an example, we blogged about making a custom mount handler for HackerNews stories.

For hosted datasets, where the author has pushed Splitgraph Images to the repository, you can "clone" and/or "checkout" the data using sgr cloneand sgr checkout.

Mounting Data

This repository is an external repository. It's not hosted by Splitgraph. It is hosted by data.cambridgema.gov, and Splitgraph indexes it. This means it is not an actual Splitgraph image, so you cannot use sgr clone to get the data. Instead, you can use the socrata adapter with the sgr mount command. Then, if you want, you can import the data and turn it into a Splitgraph image that others can clone.

First, install Splitgraph if you haven't already.

Mount the table with sgr mount

sgr mount socrata \
  "cambridgema-gov/building-permits-commercial-and-multifamily-24a8-ex6a" \
  --handler-options '{
    "domain": "data.cambridgema.gov",
    "tables": {
        "building_permits_commercial_and_multifamily": "24a8-ex6a"
    }
}'

That's it! Now you can query the data in the mounted table like any other Postgres table.

Query the data with your existing tools

Once you've loaded the data into your local Splitgraph engine, you can query it with any of your existing tools. As far as they're concerned, cambridgema-gov/building-permits-commercial-and-multifamily-24a8-ex6a is just another Postgres schema.

Related Documentation: