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
"exemption_amount_county_6", -- The dollar amount of exemption #6 for county taxation purposes.
"county_taxable_value", -- Total taxable assessment for county taxation purposes.
"town_taxable_value", -- Total taxable assessment for city/town taxation purposes.
"school_taxable", -- Total taxable assessment for school taxation purposes.
"exemption_amount_county_7", -- The dollar amount of exemption #7 for county taxation purposes.
"additional_owner_3_first", -- Additional Owner #3 First Name
"additional_owner_3_mi", -- Additional Owner #3 Middle Initial
"additional_owner_2_last_name", -- Additional Owner #2 Last Name
"additional_owner_2_mi", -- Additional Owner #2 Middle Initial
"additional_owner_2_first", -- Additional Owner #2 First Name
"additional_owner_1_suffix", -- Additional Owner #1 Suffix
"additional_owner_1_mi", -- Additional Owner #1 Middle Initial
"primary_owner_suffix", -- Primary Owner Suffix
"primary_owner_mi", -- Primary Owner Middle Initial
"primary_owner_first_name", -- Primary Owner First Name
"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.
"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.
"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.
"bank", -- Identifier for the bank used in the latest sale transaction.
"depth", -- Measurement from the front to the rear line of the parcel in feet.
"front", -- The width of the parcel from the front in feet.
"parcel_address_suff", -- Street suffix associated with the physical location of the property.
"parcel_address_number", -- Street number associated with the physical location of the property.
"property_class_description", -- Literal text description of the property class assigned to the parcel.
"property_class", -- A three digit code, established by ORPS for categorizing property by use.
"additional_owner_3_last_name", -- Additional Owner #3 Last Name
"swis_code", -- Six-digit code that identifies subsections of a city or town, such as a village that resides within.
"school_district_code", -- Six-digit code that uniquely identifies each school district.
"municipality_name", -- The name of the city or town in which the parcel resides.
"roll_year", -- The calendar year in which the assessment roll was filed.
"exemption_amount_school_10", -- The dollar amount of exemption #10 for school taxation purposes.
"exemption_code_9", -- The exemption code associated with exemption #9.
"exemption_code_8", -- The exemption code associated with exemption #8.
"exemption_amount_city_6", -- The dollar amount of exemption #6 for city/town taxation purposes.
"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.
"assessment_land", -- The assessed value of only the land associated with the parcel.
"mailing_address_number", -- Mailing Address Street Number
"exemption_code_7", -- The exemption code associated with exemption #7.
"mailing_address_street", -- Mailing Address Street Name
"mailing_address_suff", -- Mailing Address Street Suffix
"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.
"exemption_amount_city_10", -- The dollar amount of exemption #10 for city/town taxation purposes.
"mailing_address_zip", -- Mailing Address Zip Code
"additional_owner_1_last_name", -- Additional Owner #1 Last Name
"additional_owner_1_first", -- Additional Owner #1 First Name
"primary_owner_last_name", -- Primary Owner Last Name
"parcel_address_street", -- Street name associated with the physical location of the property.
"exemption_amount_county_10", -- The dollar amount of exemption #10 for county taxation purposes.
"municipality_code", -- Six-digit code that uniquely identifies each municipality.
"exemption_amount_school_8", -- The dollar amount of exemption #8 for school taxation purposes.
"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.
"print_key_code", -- Unique identifier for the parcel within a given municipality.
"mailing_address_country", -- Mailing Address Country
"mailing_address_po_box", -- Mailing Address PO Box
"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.
"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_code_10", -- The exemption code associated with exemption #10.
"exemption_amount_school_9", -- The dollar amount of exemption #9 for school taxation purposes.
"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_amount_county_1", -- The dollar amount of exemption #1 for county taxation purposes.
"exemption_amount_city_1", -- The dollar amount of exemption #1 for city/town taxation purposes.
"school_district_name", -- The name of the school district in which the parcel resides.
"exemption_amount_school_1", -- The dollar amount of exemption #1 for school taxation purposes.
"exemption_amount_city_9", -- The dollar amount of exemption #9 for city/town taxation purposes.
"exemption_amount_county_9", -- The dollar amount of exemption #9 for county taxation purposes.
"exemption_amount_county_8", -- The dollar amount of exemption #8 for county taxation purposes.
"additional_owner_3_suffix", -- Additional Owner #3 Suffix
"mailing_address_city", -- Mailing Address City
"mailing_address_state", -- Mailing Address Street Name
"exemption_amount_city_4", -- The dollar amount of exemption #4 for city/town taxation purposes.
"exemption_amount_school_5", -- The dollar amount of exemption #5 for school taxation purposes.
"exemption_code_6", -- The exemption code associated with exemption #6.
"exemption_amount_school_7", -- The dollar amount of exemption #7 for school taxation purposes.
"exemption_amount_school_6", -- The dollar amount of exemption #6 for school taxation purposes.
"exemption_amount_city_7", -- The dollar amount of exemption #7 for city/town taxation purposes.
"exemption_application_number_6", -- The taxing purposes to which exemption #7 applies.
"exemption_application_number_5", -- The taxing purposes to which exemption #6 applies.
"exemption_application_number_7", -- The taxing purposes to which exemption #8 applies.
"exemption_application_number_4", -- The taxing purposes to which exemption #5 applies.
"exemption_application_number_3", -- The taxing purposes to which exemption #4 applies.
"mailing_address_prefix", -- Mailing Address Street Prefix
"tax_class", -- Tax class used in municipalities where class assessing is applicable.
"county_name", -- The name of the county in which the parcel resides.
"exemption_application_number_8", -- The taxing purposes to which exemption #9 applies.
"exemption_application_number_9", -- The taxing purposes to which exemption #10 applies.
"exemption_code_2", -- The exemption code associated with exemption #2.
"exemption_application_number_1", -- The taxing purposes to which exemption #2 applies.
"exemption_amount_county_2", -- The dollar amount of exemption #2 for county taxation purposes.
"exemption_amount_city_2", -- The dollar amount of exemption #2 for city/town taxation purposes.
"exemption_amount_school_2", -- The dollar amount of exemption #2 for school taxation purposes.
"exemption_code_3", -- The exemption code associated with exemption #3.
"exemption_application_number_2", -- The taxing purposes to which exemption #3 applies.
"exemption_amount_county_3", -- The dollar amount of exemption #3 for county taxation purposes.
"exemption_amount_city_3", -- The dollar amount of exemption #3 for city/town taxation purposes.
"exemption_amount_school_3", -- The dollar amount of exemption #3 for school taxation purposes.
"exemption_code_4", -- The exemption code associated with exemption #4.
"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.
"exemption_amount_county_5", -- The dollar amount of exemption #5 for county taxation purposes.
"exemption_amount_city_8", -- The dollar amount of exemption #8 for city/town taxation purposes.
"additional_owner_2_suffix", -- Additional Owner #2 Suffix
"exemption_amount_city_5" -- The dollar amount of exemption #5 for city/town taxation purposes.
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.
This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.ny.gov. When you queryny-gov/property-assessment-data-from-local-assessment-7vem-aaz7: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.ny.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 \
"ny-gov/property-assessment-data-from-local-assessment-7vem-aaz7" \
--handler-options '{
"domain": "data.ny.gov",
"tables": {
"property_assessment_data_from_local_assessment": "7vem-aaz7"
}
}'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, ny-gov/property-assessment-data-from-local-assessment-7vem-aaz7 is just another Postgres schema.