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 privately_owned_public_spaces_pops table in this repository, by referencing it like:
"cityofnewyork-us/privately-owned-public-spaces-pops-rvih-nhyn:latest"."privately_owned_public_spaces_pops"or in a full query, like:
SELECT
":id", -- Socrata column ID
"borough_name", -- The Borough in which the public space is located. The City has 5 geographically defined boroughs.
":@computed_region_f5dn_yrer",
"longitude", -- The longitude of the development.
"community_district", -- The community district in which the public space is located. The City has 59 geographically defined community districts overseen by community boards. The community boards play a role in the City’s Uniform Land Use Review Procedure (ULURP), as well as in the review of other City Planning Commission and Board of Standards and Appeals actions.
"xcoordinate", -- X coordinate in NAD 1983 State Plane New York Long Island FIPS 3104 (US Feet)
"year_completed", -- Year Completed is the year the building was completed, according to published information and discussions with the owner.
"zip_code", -- The zip code in which the public space is located.
"tax_block", -- Tax Block is the block number for the development’s relevant zoning lots. The block refers to a city block, within which are a number of lots. The source for this information is the Department of Finance records and published information such as the Sanborn Manhattan Land Book.
"borocode", -- Borough Code is a single digit that represents the borough in which the public space is located. The City has 5 geographically defined boroughs.
"permitted_amenities", -- Permitted Required is the permitted amenities at the public space(s) at the development.
"ycoordinate", -- Y coordinate in NAD 1983 State Plane New York Long Island FIPS 3104 (US Feet)
"public_space_type", -- Public Space Type is the type of spaces required to be provided at the development.
"council_district", -- The City Council district in which the public space is located.
"latitude", -- The latitude of the development.
"hour_of_access_required", -- Hours of Access Required is the legally required hours that the public space(s) at the development must be open to the public.
"building_address_with_zip", -- Building Address with Zip Code is the street address as displayed on the building’s exterior or as recorded in Department of Finance records.
"building_constructed", -- Building Construction is the construction status of the building, according to published information and discussions with the owner.
"developer", -- Developer is the name of the legal entity or a person or persons participating in the legal entity that has developed the building and its public space(s). The sources for this field are varied, including legal records, published information, and discussions with the current owner.
"nta", -- The Neighborhood Tabulaton Area (NTA) in which the public space is located. NTAs were created by aggregating census tracts. NTAs were delineated with the need for both geographic specificity and statistical reliability in mind. Though NTA boundaries and their associated names roughly correspond with many neighborhoods commonly recognized by New Yorkers, NTAs are not intended to definitively represent neighborhoods, nor are they intended to be exhaustive of all possible names and understandings of neighborhoods throughout New York City.
"bin", -- The Building Identification Number (BIN) is a unique number assigned by the Department of City Planning (DCP) and used by the Department of Buildings (DOB) for each building in NYC. This field consists of the borough code followed by six digits unique for buildings within that borough.
"tax_lot", -- Tax Lot is the lot number(s) for the development’s relevant zoning lots. The block refers to a city block, within which are a number of lots. With condominium buildings, each condominium unity has its own lot number. The source for this information is the Department of Finance records and published information such as the Sanborn Manhattan Land Book.
":@computed_region_sbqj_enih",
"physically_disabled", -- Physically Disabled is a description of whether or not the public spaces, taken as a whole, are wheelchair accessible to some degree to the physically disabled. Full/Partial means that the spaces are fully or partially accessible.
"building_name", -- The name of the building as displayed on the building’s exterior or as generally presented to the public. If there is no building name, the field is left blank.
"address_number", -- The address number as displayed on the building’s exterior or as recorded in Department of Finance records.
":@computed_region_92fq_4b7q",
"pops_number", -- POPS Number is the record number the Department of City Planning uses to identify a building address within the POPS Database.
"building_architect", -- Building Architect is the architect of the building. The sources for this field are varied, including legal records, published information, and discussions with the current owner.
"principal_public_space", -- Principal Public Space Designer is the designer who designed or redesigned the public space, and frequently is the building architect. The sources for this field are varied, including legal records, published information, and discussions with the current owner.
"size_required", -- Size Required is the legally required size of the public space(s) at the development.
":@computed_region_yeji_bk3q",
"building_location", -- The location of the main entrance or entrances to the building, and describes the building rather than the public space, location.
"geocoded_column",
"street_name", -- The street name as displayed on the building’s exterior or as recorded in Department of Finance records.
"bbl", -- A concatenation of the borough code, tax block and tax lot. This field consists of the borough code followed by the tax block followed by the tax lot. The borough code is one numeric digit. The tax block is one to five numeric digits, preceded with leading zeros when the block is less than five digits. The tax lot is one to four digits and is preceded with leading zeros when the lot is less than four digits.
":@computed_region_efsh_h5xi",
"amenities_required", -- Amenities Required is the legally required amenities at the public space(s) at the development.
"other_required", -- Other Required is the other legally required amenities at the public space(s) at the development.
"census_tract" -- The census tract in which the public space is located.
FROM
"cityofnewyork-us/privately-owned-public-spaces-pops-rvih-nhyn:latest"."privately_owned_public_spaces_pops"
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 cityofnewyork-us/privately-owned-public-spaces-pops-rvih-nhyn 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 cityofnewyork-us/privately-owned-public-spaces-pops-rvih-nhyn: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 cityofnewyork-us/privately-owned-public-spaces-pops-rvih-nhynCheckout 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 cityofnewyork-us/privately-owned-public-spaces-pops-rvih-nhyn:latestThis will download all the objects for the latest tag of cityofnewyork-us/privately-owned-public-spaces-pops-rvih-nhyn 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 cityofnewyork-us/privately-owned-public-spaces-pops-rvih-nhyn: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 cityofnewyork-us/privately-owned-public-spaces-pops-rvih-nhyn: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, cityofnewyork-us/privately-owned-public-spaces-pops-rvih-nhyn is just another Postgres schema.