Query the Data Delivery Network
Query the DDNThe 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 protocol. 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_1_and_2_family_deprecated table in this repository, by referencing it like:
"cambridgema-gov/building-permits-1-and-2-family-deprecated-52p4-36ct:latest"."building_permits_1_and_2_family_deprecated"or in a full query, like:
SELECT
":id", -- Socrata column ID
"confirmedleedratingcredits", -- Number of Confirmed LEED rating credits (LEED means Leadership in Energy and Environmental Design)
"designedleedratingcredits", -- Number of Designed LEED rating credits (LEED means Leadership in Energy and Environmental Design)
"leedrating_coreshell", -- Type of LEEDS rating system (LEED means Leadership in Energy and Environmental Design)
"stretchcodeexempt", -- Is project stretch code exempt
"windturbines", -- Number of wind turbines
"leedratingslevel", -- LEED rating level (LEED means Leadership in Energy and Environmental Design)
"energyconservuvalueofwindows", -- U-Value of windows
"enerconservother", -- Other type of insulation
"energyconservblownincellulose", -- Blown in insulation
"energyconservsprayfoam", -- Spray foam insulation
"proposedworkenergyenv", -- Work does not access energy envelope
"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.
"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.
"pbcasenumber", -- Planning Board case number
"bzacasenumber", -- Board of Zoning Appeal case number
"zoningdistrict", -- Zoning district of property
"currentuse", -- Current use of property
"detaileddescriptionofwork", -- Description of work to be completed
"totalcostofconstruction", -- Total cost of all work to be completed
"hvaccostofconstruction", -- Cost of HVAC work
"gascostofconstruction", -- Cost of gas work
"plumbingcostofconstruction", -- Cost of plumbing work
"electricalcostofconstruction", -- Cost of electrical work
"buildingcostofconstruction", -- Cost of building construction
"changeinnumberofunitsquantity", -- The number of units added or subtracted
"bldcnstrtype_nc_value", -- Type of building construction
"applicant", -- Name of applicant
"complete_date", -- Plan application completion date.
"applicationdate", -- Date of Application
"unitorsuite", -- Unit/Suite Number
"address_zip",
"address_state",
"address_address",
"plannumber", -- Auto-created plan number
"exteriordemoworkareaonly", -- Extent of demolition - work area only
"windturbineslocation", -- Location of wind turbines
"cogenerationsystemlocation", -- Location of cogeneration systems
"proposeduse", -- Proposed use of property
"bldtype_accessorybuilding", -- Accessory Building
"parcelnumber", -- Property parcel number
"descriptionofdemolition", -- Description of demolition
"substdemostructuralelements", -- Extent of demolition - structural elements
"leedrating_comercialinteriors", -- Type of LEEDS rating system (LEED means Leadership in Energy and Environmental Design)
"bldtype_singlefamily", -- Single Family
"bldcnstrtype_mw_value", -- Type of building construction
"bldtype_townhouse", -- Town House
"substantialdemo", -- Extent of demolition - substantial
"confirmedleedratinglevel", -- Confirmed LEED rating level (LEED means Leadership in Energy and Environmental Design)
"address_city",
"leedrating_schools", -- Type of LEEDS rating system (LEED means Leadership in Energy and Environmental Design)
"confirmedhersrating", -- Confirmed HERS rating (HERS means Home Energy Rating System)
"twoyearvacancy", -- Was property vacant 2 years prior to application
"parkingspaces", -- Number of parking spaces
"changeinnumberofunits", -- Was there a change in the number of units
"leedrating_newconstruction", -- Type of LEEDS rating system (LEED means Leadership in Energy and Environmental Design)
"bldtype_twofamily", -- Two Family
"minorinteriordemo", -- Extent of demolition - minor
":@computed_region_rcj3_ccgu",
"leedrating_homes", -- Type of LEEDS rating system (LEED means Leadership in Energy and Environmental Design)
"solarpanels", -- Number of solar panels
"leedratingscredits", -- LEED rating credits (LEED means Leadership in Energy and Environmental Design)
"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.
"proposedworkreconst", -- Proposed work includes reconstruction of an existing exterior building element (porch, deck, etc.).
"exterioralteration", -- Is work to be performed on exterior
"bldcnstrtype_wood_value", -- Type of building construction
"class", -- Work class
"address", -- Address where work is to be performed
"designedleedratinglevel", -- Designed LEED rating level (LEED means Leadership in Energy and Environmental Design)
"enerconservfiberglass", -- Fiberglass insulation
":@computed_region_e4yd_rwk4",
":@computed_region_swkg_bavi",
":@computed_region_rffn_qbt6",
":@computed_region_v7jj_366k",
"cavitiesinsulatingmaterials", -- Type of insulation being used
":@computed_region_guic_hr4a",
"windturbinessize", -- Size of wind turbines
"solarpanelsuseforhotwater", -- Are solar panels used for hot water
"cogenerationsystem", -- Number of cogeneration systems
"cogenerationsystemsize", -- Size of cogeneration systems
"proposedhersrating", -- Proposed HERS rating (HERS means Home Energy Rating System)
"envelopeinsulationreq", -- Meets or exceeds energy code
"leedrating_retail" -- Type of LEEDS rating system (LEED means Leadership in Energy and Environmental Design)
FROM
"cambridgema-gov/building-permits-1-and-2-family-deprecated-52p4-36ct:latest"."building_permits_1_and_2_family_deprecated"
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-1-and-2-family-deprecated-52p4-36ct 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-1-and-2-family-deprecated-52p4-36ct: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
bash -c "$(curl -sL https://github.com/splitgraph/splitgraph/releases/latest/download/install.sh)"Read the installation docs.
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-1-and-2-family-deprecated-52p4-36ct" \
--handler-options '{
"domain": "data.cambridgema.gov",
"tables": {
"building_permits_1_and_2_family_deprecated": "52p4-36ct"
}
}'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-1-and-2-family-deprecated-52p4-36ct is just another Postgres schema.