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 board_of_zoning_appeal_requests table in this repository, by referencing it like:
"cambridgema-gov/board-of-zoning-appeal-requests-urfm-usws:latest"."board_of_zoning_appeal_requests"or in a full query, like:
SELECT
":id", -- Socrata column ID
"ordinance_lot_area_dwelling_unit", -- As per Article 5 in Cambridge Zoning Ordinance.
"requested_lot_area_dwelling_unit", -- Lot area divided by proposed number of dwelling units.
"literal_enforcement", -- Reason for substantial hardship.
"ordinance_size_of_lot_depth", -- As per Article 5, Cambridge Zoning Ordinance
"board_of_appeals_special_permit", -- Special Permit: Process where applicant is not violating, but is restricted.
"zoning_ordinance_cited_article_field6", -- Board of Zoning Appeal Ordinance, continued. (Petitioners were allowed to list up to seven ordinances applicable to their case)
"exisiting_lot_area", -- Square footage of lot.
"desirable_relief_public_good", -- Substantial detriment to the public good for the following reason.
"desirable_relief_nullifying", -- Relief may be granted without nullifying or substantially derogating from the intent or purpose of this ordinance.
":@computed_region_e4yd_rwk4", -- This column was automatically created in order to record in what polygon from the dataset 'Census Blocks 2010' (e4yd-rwk4) the point in column 'coordinates' is located. This enables the creation of region maps (choropleths) in the visualization canvas and data lens.
":@computed_region_swkg_bavi",
":@computed_region_rffn_qbt6",
":@computed_region_v7jj_366k",
":@computed_region_guic_hr4a",
":@computed_region_rcj3_ccgu",
"existing_no_of_parking_spaces",
"ordinance_distance_to_nearest_building",
"trafficpatterns", -- Traffic generated or patterns of access or egress would not cause congestion hazard, or substantial change in established neighborhood character.
"type", -- Special Permit, Variance, Appeal
"variance_hearing_deadline", -- Date by which the Board must hear the case.
"zoning_ordinance_cited_article_field2", -- Board of Zoning Appeal Ordinance, continued. (Petitioners were allowed to list up to seven ordinances applicable to their case)
"zoning_ordinance_cited_section_field3", -- Relevant Section of the Board of Zoning Appeal Ordinance
"zoning_ordinance_cited_section_field7", -- Relevant Section of the Board of Zoning Appeal Ordinance
"existing_setbacks_left_side", -- Distance from property line to closest portion of building on the given side of the structure.
"existing_size_of_building_height",
"decisiondate", -- The date the BZA made the decision.
"description_of_other_occupancies", -- Buildings on same lot.
"requested_no_of_loading_areas", -- Number of existing loading areas plus additional requested loading areas.
"ordinance_no_of_dwelling_units",
"existing_no_of_dwelling_units", -- Number of existing dwelling units on property.
"requested_setbacks_right_side", -- Distance from property line to closest portion of building on the given side of the structure.
"existing_setbacks_right_side", -- Distance from property line to closest portion of building on the given side of the structure.
"ordinance_setbacks_front", -- Distance from property line to closest portion of building on the given side of the structure.
"requested_setbacks_front", -- Distance from property line to closest portion of building on the given side of the structure.
"existing_setbacks_front", -- Distance from property line to closest portion of building on the given side of the structure.
"requested_size_of_lot_depth",
"existing_lot_area_dwelling_unit", -- Lot area divided by existing number of dwelling units.
"ordinance_gross_floor_area_ratio", -- As per Article 5 in Cambridge Zoning Ordinance.
"requested_gross_floor_area_ratio", -- Ratio of gross floor area plus additional gross floor area to lot area
"zoning_ordinance_cited_section_field4", -- Relevant Section of the Board of Zoning Appeal Ordinance
"zoning_ordinance_cited_section_field2", -- Relevant Section of the Board of Zoning Appeal Ordinance
"zoning_ordinance_cited_section_field1", -- Relevant Section of the Board of Zoning Appeal Ordinance
"variance_decision_deadline", -- Date by which the board must decide on the case
"special_permit_decision_deadline", -- Date the decision must be filed with City Clerk.
"special_permit_hearing_deadline", -- Date by which the Board must hear the case.
"current_use", -- How property is being used
"location_address",
"total_gross_floor_area", -- Total gross floor area before addition.
"ordinance_useable_open_space_ratio", -- Per Article 5, Cambridge Zoning Ordinanace
"land_topography_hardship", -- The hardship is owing to the following circumstances relating to the soil, conditins, shape or topography of such land or structures and especially affecting such land or structures but not affecting generally the zoning district in which it is located.
"status", -- Pending Payment, Under Review, Approved, Withdrawn
"location_city",
"zoning_ordinance_cited_article_field1", -- Board of Zoning Appeal Ordinance
"existing_gross_floor_area_ratio", -- Ratio of gross floor area of building to lot area.
"other_reasons", -- The proposed use would not impair the integrity of the district or adjoining district or otherwise derogate from the intent or purpose of this ordinance.
"petitioner_state",
"petitioner_city",
"existing_usablek_open_space_ratio", -- Ratio of all areas except building foot print, parking & driveway to total lot area.
"requested_size_of_building_length",
"existing_size_of_building_length",
"ordinance_setbacks_rear", -- Distance from property line to closest portion of building on the given side of the structure.
"requested_lot_area", -- Area of lot after the requested variance
"ordinance_total_gross_flr_area", -- As per article 5 in Cambridge Zoning Ordinance.
"requested_total_gross_flr_area", -- Total gross floor area after addition.
"zoning_ordinance_cited_section_field6", -- Relevant Section of the Board of Zoning Appeal Ordinance
"zoning_ordinance_cited_article_field4", -- Board of Zoning Appeal Ordinance, continued. (Petitioners were allowed to list up to seven ordinances applicable to their case)
"zoning_ordinance_cited_article_field3", -- Board of Zoning Appeal Ordinance, continued. (Petitioners were allowed to list up to seven ordinances applicable to their case)
"plan_number", -- Number given at time of application
"applicationdate", -- Date application was filed with the City.
"zoning_ordinance_cited_article_field7", -- Board of Zoning Appeal Ordinance, continued. (Petitioners were allowed to list up to seven ordinances applicable to their case)
"desc_of_relief_req", -- Relief petitioner is asking the Board to grant.
"ordinance_lot_area", -- As per Article 5, Cambridge Zoning Ordinance.
"requested_setbacks_left_side", -- Distance from property line to closest portion of building on the given side of the structure.
"ordinance_setbacks_right_side", -- Distance from property line to closest portion of building on the given side of the structure.
"zoning_district", -- What the property is zoned per Cambridge zoning map.
"proposed_use", -- How petitioner is proposing to use the property
"variance_board_of_appeals", -- Variance: Violating the requirements of the ordinance.
"reason_for_petition_other", -- Reason for petition
"ordinance_size_of_building_width",
"location_zip",
"ordinance_no_of_parking_spaces", -- Per Article 5, Cambridge Zoning Ordinance,
"existing_size_of_lot_depth",
"zoning_ordinance_cited_section_field5", -- Relevant Section of the Board of Zoning Appeal Ordinance
"location_state",
"location", -- Property in question
"zoning_use_classification", -- Residential/Commercial/Use
"applied_online", -- 1 means yes
"summary_for_publication", -- Description of request for newspaper publication.
"reason_for_petition",
"ordinance_size_of_lot_width", -- As per Article 5, Cambridge Zoning Ordinance
"description", -- Description of requested change to property.
"requirements_of_ordinance", -- Requirements of the Ordinance can or will be met.
"requested_distance_to_nearest_bldg",
"existing_no_of_loading_areas", -- Number of existing loading areas on property.
"requested_no_of_dwelling_units", -- Number of existing dwelling units plus additional requested units.
"requested_usable_open_space_ratio", -- Ratio of all areas except building foot print, parking & driveway including proposed addition to total lot area.
"requested_size_of_building_width",
"ordinance_size_of_building_length",
"requested_size_of_building_height",
"ordinance_setbacks_left_side", -- Distance from property line to closest portion of building on the given side of the structure.
"existing_size_of_lot_width",
"requested_size_of_lot_width",
"zoning_ordinance_cited_article_field5", -- Board of Zoning Appeal Ordinance, continued. (Petitioners were allowed to list up to seven ordinances applicable to their case)
"nuisance_or_hazard", -- Nuisance or hazard would not be created to the detriment of the health, safety and/or welfare of the occupant of the proposed use or the citizens of the City.
"continued_operation", -- The continued operation of or the development of adjacent uses as permitted in the Zoning Ordinance would not be adversely affected by the nature of the proposed use.
"existing_distance_to_nearest_bldg",
"ordinance_no_of_loading_areas", -- Per Article 5, Cambridge Zoning Ordinance.
"requested_no_of_parking_spaces", -- Existing parking spaces plus additional parking spaces requested.
"existing_size_of_building_width",
"ordinance_size_of_building_height",
"requested_setbacks_back", -- Distance from property line to closest portion of building on the given side of the structure.
"existing_setbacks_back" -- Distance from property line to closest portion of building on the given side of the structure.
FROM
"cambridgema-gov/board-of-zoning-appeal-requests-urfm-usws:latest"."board_of_zoning_appeal_requests"
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/board-of-zoning-appeal-requests-urfm-usws with SQL in under 60 seconds.
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, 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 (like this repository), where the author has pushed Splitgraph Images to the repository, you can "clone" and/or "checkout" the data using sgr cloneand sgr checkout.
Cloning Data
Because cambridgema-gov/board-of-zoning-appeal-requests-urfm-usws:latest is a Splitgraph Image, you can clone the data from Spltgraph Cloud to your local engine, where you can query it like any other Postgres database, using any of your existing tools.
First, install Splitgraph if you haven't already.
Clone the metadata with sgr clone
This will be quick, and does not download the actual data.
sgr clone cambridgema-gov/board-of-zoning-appeal-requests-urfm-uswsCheckout the data
Once you've cloned the data, you need to "checkout" the tag that you want. For example, to checkout the latest tag:
sgr checkout cambridgema-gov/board-of-zoning-appeal-requests-urfm-usws:latestThis will download all the objects for the latest tag of cambridgema-gov/board-of-zoning-appeal-requests-urfm-usws and load them into the Splitgraph Engine. Depending on your connection speed and the size of the data, you will need to wait for the checkout to complete. Once it's complete, you will be able to query the data like you would any other Postgres database.
Alternatively, use "layered checkout" to avoid downloading all the data
The data in cambridgema-gov/board-of-zoning-appeal-requests-urfm-usws:latest is 0 bytes. If this is too big to download all at once, or perhaps you only need to query a subset of it, you can use a layered checkout.:
sgr checkout --layered cambridgema-gov/board-of-zoning-appeal-requests-urfm-usws:latestThis will not download all the data, but it will create a schema comprised of foreign tables, that you can query as you would any other data. Splitgraph will lazily download the required objects as you query the data. In some cases, this might be faster or more efficient than a regular checkout.
Read the layered querying documentation to learn about when and why you might want to use layered queries.
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/board-of-zoning-appeal-requests-urfm-usws is just another Postgres schema.