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 property_assessment_data_from_local_assessment table in this repository, by referencing it like:
"ny-gov/property-assessment-data-from-local-assessment-7vem-aaz7:latest"."property_assessment_data_from_local_assessment"or in a full query, like:
SELECT
":id", -- Socrata column ID
"county_name", -- The name of the county in which the parcel resides.
"additional_owner_3_last_name", -- Additional Owner #3 Last Name
"mailing_address_prefix", -- Mailing Address Street Prefix
"additional_owner_3_mi", -- Additional Owner #3 Middle Initial
"tax_class", -- Tax class used in municipalities where class assessing is applicable.
"additional_owner_2_first", -- Additional Owner #2 First Name
"exemption_amount_city_3", -- The dollar amount of exemption #3 for city/town taxation purposes.
"exemption_amount_county_3", -- The dollar amount of exemption #3 for county taxation purposes.
"additional_owner_1_last_name", -- Additional Owner #1 Last Name
"school_district_code", -- Six-digit code that uniquely identifies each school district.
"primary_owner_last_name", -- Primary Owner Last Name
"primary_owner_suffix", -- Primary Owner Suffix
"exemption_application_number_2", -- The taxing purposes to which exemption #3 applies.
"additional_owner_2_mi", -- Additional Owner #2 Middle Initial
"swis_code", -- Six-digit code that identifies subsections of a city or town, such as a village that resides within.
"additional_owner_2_last_name", -- Additional Owner #2 Last Name
"property_class", -- A three digit code, established by ORPS for categorizing property by use.
"property_class_description", -- Literal text description of the property class assigned to the parcel.
"exemption_code_3", -- The exemption code associated with exemption #3.
"exemption_amount_school_2", -- The dollar amount of exemption #2 for school taxation purposes.
"exemption_amount_city_2", -- The dollar amount of exemption #2 for city/town taxation purposes.
"parcel_address_street", -- Street name associated with the physical location of the property.
"exemption_amount_county_2", -- The dollar amount of exemption #2 for county taxation purposes.
"roll_section", -- The section of the assessment roll on which the property resides. 1 - Ordinary taxable property including that which is partially exempt. 3 - Taxable State Owned Land. All property owned by the State of New York that is subject to general tax levies. 5 - Special Franchise. Real property owned by public utilities, such as cables, transmission or distribution wires, mains, etc., which is located under, above, or upon any public right of way. 6 - Utilities and Non-Ceiling Railroads. all property owned by public utility companies and privately held railroad companies except special franchise and ceiling railroad property. 7 - Ceiling Railroads. All railroad transportation property whose assessments are subject to a ceiling imposed by the Office of Real Property Tax Services. 8 - Wholly Exempt Property. includes all parcels which are entirely exempt from property taxes for the general levies. Many properties in this section are required to pay some special district taxes, however. Sections 2 and 4 are not currently in use.
"parcel_address_number", -- Street number associated with the physical location of the property.
"municipality_name", -- The name of the city or town in which the parcel resides.
"additional_owner_1_first", -- Additional Owner #1 First Name
"roll_year", -- The calendar year in which the assessment roll was filed.
"exemption_amount_city_9", -- The dollar amount of exemption #9 for city/town taxation purposes.
"exemption_amount_school_9", -- The dollar amount of exemption #9 for school taxation purposes.
"exemption_code_10", -- The exemption code associated with exemption #10.
"exemption_amount_county_10", -- The dollar amount of exemption #10 for county taxation purposes.
"exemption_amount_city_10", -- The dollar amount of exemption #10 for city/town taxation purposes.
"full_market_value", -- The hypothetical price that a property would bring if exposed for sale in the open market, in an arm's length transaction between a willing seller and a willing buyer, both of whom are knowledgeable concerning all the uses to which it is adapted and for which it is capable of being used.
"primary_owner_mi", -- Primary Owner Middle Initial
"primary_owner_first_name", -- Primary Owner First Name
"exemption_amount_school_10", -- The dollar amount of exemption #10 for school taxation purposes.
"additional_owner_1_mi", -- Additional Owner #1 Middle Initial
"additional_owner_1_suffix", -- Additional Owner #1 Suffix
"exemption_code_9", -- The exemption code associated with exemption #9.
"exemption_application_number_1", -- The taxing purposes to which exemption #2 applies.
"page", -- When deeds are recorded, they are assigned a page number which is used by the record office to locate the deed when it needs to be retrieved.
"parcel_address_suff", -- Street suffix associated with the physical location of the property.
"front", -- The width of the parcel from the front in feet.
"depth", -- Measurement from the front to the rear line of the parcel in feet.
"exemption_application_number_9", -- The taxing purposes to which exemption #10 applies.
"bank", -- Identifier for the bank used in the latest sale transaction.
"grid_coordinates_east", -- Information from the tax map process that represents an easting reading from the point of origin, of the appropriate zone, of the New York coordinate system to the visual center of each land parcel.
"exemption_code_2", -- The exemption code associated with exemption #2.
"exemption_application_number_8", -- The taxing purposes to which exemption #9 applies.
"municipality_code", -- Six-digit code that uniquely identifies each municipality.
"exemption_amount_school_8", -- The dollar amount of exemption #8 for school taxation purposes.
"exemption_amount_county_8", -- The dollar amount of exemption #8 for county taxation purposes.
"exemption_code_7", -- The exemption code associated with exemption #7.
"exemption_amount_school_1", -- The dollar amount of exemption #1 for school taxation purposes.
"grid_coordinates_north", -- Information from the tax map process that represents an northing reading from the point of origin, of the appropriate zone, of the New York coordinate system to the visual center of each land parcel.
"exemption_amount_city_1", -- The dollar amount of exemption #1 for city/town taxation purposes.
"exemption_amount_county_1", -- The dollar amount of exemption #1 for county taxation purposes.
"print_key_code", -- Unique identifier for the parcel within a given municipality.
"exemption_application_number", -- The taxing purposes to which exemption #1 applies. 0 - County, City/Town, and school. 1 - County and City/Town. 2 - County only. 3 - City/Town only. 4 - School only. 5 - County and school. 6 - City/Town and school purposes. 7 - Village only
"exemption_code_1", -- A four digit number assigned by the Office of Real Property Tax Services (ORPTS) to every exemption granted by statute in New York State. The exemption code associated with exemption #1.
"exemption_amount_county_4", -- The dollar amount of exemption #4 for county taxation purposes.
"exemption_amount_school_4", -- The dollar amount of exemption #4 for school taxation purposes.
"exemption_code_5", -- The exemption code associated with exemption #5.
"mailing_address_po_box", -- Mailing Address PO Box
"exemption_application_number_5", -- The taxing purposes to which exemption #6 applies.
"exemption_amount_school_5", -- The dollar amount of exemption #5 for school taxation purposes.
"exemption_amount_county_5", -- The dollar amount of exemption #5 for county taxation purposes.
"exemption_amount_county_9", -- The dollar amount of exemption #9 for county taxation purposes.
"school_district_name", -- The name of the school district in which the parcel resides.
"exemption_amount_city_8", -- The dollar amount of exemption #8 for city/town taxation purposes.
"additional_owner_3_first", -- Additional Owner #3 First Name
"exemption_amount_school_3", -- The dollar amount of exemption #3 for school taxation purposes.
"additional_owner_2_suffix", -- Additional Owner #2 Suffix
"exemption_amount_school_7", -- The dollar amount of exemption #7 for school taxation purposes.
"mailing_address_country", -- Mailing Address Country
"exemption_amount_city_4", -- The dollar amount of exemption #4 for city/town taxation purposes.
"mailing_address_state", -- Mailing Address Street Name
"mailing_address_city", -- Mailing Address City
"exemption_code_4", -- The exemption code associated with exemption #4.
"exemption_amount_city_7", -- The dollar amount of exemption #7 for city/town taxation purposes.
"mailing_address_zip", -- Mailing Address Zip Code
"exemption_amount_city_5", -- The dollar amount of exemption #5 for city/town taxation purposes.
"exemption_application_number_7", -- The taxing purposes to which exemption #8 applies.
"exemption_amount_county_7", -- The dollar amount of exemption #7 for county taxation purposes.
"mailing_address_suff", -- Mailing Address Street Suffix
"exemption_application_number_4", -- The taxing purposes to which exemption #5 applies.
"mailing_address_street", -- Mailing Address Street Name
"exemption_application_number_6", -- The taxing purposes to which exemption #7 applies.
"exemption_application_number_3", -- The taxing purposes to which exemption #4 applies.
"exemption_code_6", -- The exemption code associated with exemption #6.
"exemption_amount_county_6", -- The dollar amount of exemption #6 for county taxation purposes.
"exemption_amount_city_6", -- The dollar amount of exemption #6 for city/town taxation purposes.
"additional_owner_3_suffix", -- Additional Owner #3 Suffix
"deed_book", -- When deeds are recorded, they are assigned a book number which is used by the record office to locate the deed when it needs to be retrieved.
"exemption_code_8", -- The exemption code associated with exemption #8.
"assessment_land", -- The assessed value of only the land associated with the parcel.
"assessment_total", -- A figure in dollars, determined by an assessor, which reflects a property's worth in relation to other properties on an assessment roll and which; unless exempt; is used to compute a tax dollar obligation by multiplying it by a tax rate.
"county_taxable_value", -- Total taxable assessment for county taxation purposes.
"town_taxable_value", -- Total taxable assessment for city/town taxation purposes.
"exemption_amount_school_6", -- The dollar amount of exemption #6 for school taxation purposes.
"school_taxable", -- Total taxable assessment for school taxation purposes.
"mailing_address_number" -- Mailing Address Street Number
FROM
"ny-gov/property-assessment-data-from-local-assessment-7vem-aaz7:latest"."property_assessment_data_from_local_assessment"
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 ny-gov/property-assessment-data-from-local-assessment-7vem-aaz7 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 ny-gov/property-assessment-data-from-local-assessment-7vem-aaz7: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 ny-gov/property-assessment-data-from-local-assessment-7vem-aaz7Checkout 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 ny-gov/property-assessment-data-from-local-assessment-7vem-aaz7:latestThis will download all the objects for the latest tag of ny-gov/property-assessment-data-from-local-assessment-7vem-aaz7 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 ny-gov/property-assessment-data-from-local-assessment-7vem-aaz7: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 ny-gov/property-assessment-data-from-local-assessment-7vem-aaz7: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, ny-gov/property-assessment-data-from-local-assessment-7vem-aaz7 is just another Postgres schema.