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 condition_of_tidal_wetlands table in this repository, by referencing it like:
"delaware-gov/condition-of-tidal-wetlands-rgup-nusm:latest"."condition_of_tidal_wetlands"or in a full query, like:
SELECT
":id", -- Socrata column ID
"h1_ditching_excavation_omwm", -- Metric rating based on the percent of the AA that had excavated ditches or OMWM pools
"h1_ditching_draining_rating", -- Metric rating based on the extent of ditching and draining in the AA (none, low, moderate, or severe)
"other4", -- Midpoint of percent cover class for an additional plant community in the AA other than those previously listed
"condition_category", -- Condition category that the wetland falls within (minimally, moderately, or severely stressed) based on final score
"purpose", -- Reason for field visit (assessment or reference)
"other3", -- Midpoint of percent cover class for an additional plant community in the AA other than those previously listed
"spartina_patens_distichlis", -- Midpoint of percent cover class for mixed community of Spartina patens and Distichlis spicata in the AA
"b1_with_5m_buffer_rating", -- Metric rating based on percent of AA perimeter that has at least 5m of natural or semi-natural condition land cover
"hydro_attribute_score", -- Overall score of all hydrology attributes combined based on protocol formula
"other12_1", -- Description of additional plant community in the AA other than those previously listed
"hab4_6_invasive_co_dominant", -- Percent of co-dominant non-native, invasive species in the AA
"other11_1", -- Description of additional plant community in the AA other than those previously listed
"tide_stage", -- Tidal stage the best represents the AA during the site visit, where 5 is highest and 1 is lowest
"other4_1", -- Description of additional plant community in the AA other than those previously listed
"b2_avg_buffer_width_rating", -- Metric rating based on average buffer width (m) surrounding the AA that is in natural or semi-natural condition
"hab5_7_invasive", -- Percent cover of invasive plant species in the AA
"distance_to_upland_m", -- Estimated distance (m) from the edge of the AA to the closest major upland body that is not an island
"spartina_patens", -- Midpoint of percent cover class for Spartina patens in the AA
"other10_1", -- Description of additional plant community in the AA other than those previously listed
"spartina_alterniflora", -- Midpoint of percent cover class for Spartina alterniflora in the AA
"other7", -- Midpoint of percent cover class for an additional plant community in the AA other than those previously listed
"other3_1", -- Description of additional plant community in the AA other than those previously listed
"watershed", -- HUC10 watershed that the wetland site is within
"hab4_species_richness_count", -- Count of different plant species found in the AA
"b3_surrounding_development", -- Percent of developed land within 250m from the edge of the AA
"hab1_structural_patch_type", -- Number of different types of structural habitat patch types found within the AA
"pannes_pools_creeks", -- Midpoint of percent cover class for pannes, pools, or creeks in the AA
"qdr", -- Qualitative Disturbance Rating
"classification", -- Specifies what type of wetland the site is, according to protocol's classification system
"other14_1", -- Description of additional plant community in the AA other than those previously listed
"hab5_7_invasive_rating", -- Metric rating based on the percent cover of invasive plant species in the AA
"water_on_surface_cm", -- Measurement (cm) that best represents the amount of standing water on the marsh surface in the AA
"b5_marsh_habitat_richness_1", -- Metric rating based on the number of different communities and habitats found within 250m of the AA
"other5", -- Midpoint of percent cover class for an additional plant community in the AA other than those previously listed
"salinity_ppt", -- Salinity (ppt) of the surface or creek water using a YSI or other digital water quality instrument
"ditches", -- Midpoint of percent cover class for ditches in the AA
"h4_docks_duck_blinds_rating", -- Area of AA and buffer covered by man-made structures, such as docks and duck blinds
"midtram_version", -- Version of protocol used to assess site
"b2_avg_buffer_width", -- Average buffer width (m) surrounding the AA that is in natural or semi-natural condition
"other9_1", -- Description of additional plant community in the AA other than those previously listed
"buffer_attribute_score", -- Overall score of all buffer attributes combined based on protocol formula
"hab4_species_richness_rating", -- Metric rating based on the count of different plant species found in the AA
"other6", -- Midpoint of percent cover class for an additional plant community in the AA other than those previously listed
"b5_marsh_habitat_richness", -- Number of different communities and habitats found within 250m of the AA
"other8", -- Midpoint of percent cover class for an additional plant community in the AA other than those previously listed
"phragmites_australis", -- Midpoint of percent cover class for Phragmites australis in the AA
"b3_altered_high_impact_land_2", -- Metric rating based on percent of the buffer area that is an altered (unnatural) land use or a high impact land use
"other2", -- Midpoint of percent cover class for an additional plant community in the AA other than those previously listed
"hab3_5_plant_layers_rating", -- Metric rating based on the count of plant layers in the AA
"final_score", -- The final score of a wetland site on a scale of 0 to 100
"stability_of_aa", -- Describes the current physical stability of the wetland within the AA
"other1", -- Midpoint of percent cover class for an additional plant community in the AA other than those previously listed
"other8_1", -- Description of additional plant community in the AA other than those previously listed
"date", -- Date site was assessed
"open_water", -- Midpoint of percent cover class for open water (>30m wide) in the AA
"b2_natural_land_uses_in_buffer", -- Percent of the buffer area that is contiguous between the AA and 250m buffer edge and in a natural or semi-natural state
"b4_250m_landscape_condition", -- Landscape condition within 250m surrounding the AA center point based on the nativeness of vegetation, disturbance to substrate, and extent of human visitation
"other12", -- Midpoint of percent cover class for an additional plant community in the AA other than those previously listed
"h2_fill_rating", -- Metric rating based on the estimated percent of the AA that has fill
"marsh_community", -- A general description of the AA based on plant species present
"hab4_vertical_biotic_structure", -- Metric rating based on the interspersion and complexity of the vegetation community
"h4_point_source_rating", -- Metric rating based on the extent of point sources (absent, low, moderate, or high) in the AA and 250m buffer
"spartina_alterniflora_spartina", -- Midpoint of percent cover class for mixed community of Spartina alterniflora and Spartina cynosuroides in the AA
"spartina_cynosuroides", -- Midpoint of percent cover class for Spartina cynosuroides in the AA
"other9", -- Midpoint of percent cover class for an additional plant community in the AA other than those previously listed
"b3_surrounding_development_1", -- Metric rating based on percent of developed land within 250m from the edge of the AA
"schoenoplectus_americanus", -- Midpoint of percent cover class for Schoenoplectus americanus in the AA
"site_number", -- Unique numeric identifier for each site
"habitat_attribute_score", -- Overall score of all habitat attributes combined based on protocol formula
"b1_estimated_with_5m_buffer", -- Percent of AA perimeter that has at least 5m of natural or semi-natural condition land cover
"iva_frutescens", -- Midpoint of percent cover class for Iva frutescens in the AA
"hab3_5_plant_layers", -- A count of the number of plant layers in the AA
"h1b_point_sources_tidal_fresh", -- Metric rating based on the extent of point sources (absent, low, moderate, or high) in the AA and 250m buffer (tidal freshwater wetlands only)
"b1_estimated_with_10m_buffer", -- Percent of AA perimeter that has at least 5m of natural or semi-natural condition land cover that is at least 10m in width
"hab1_2_bear_cap_hollows_1", -- Estimated percent of the AA that is composed of hollows (when hollows are present)
"other11", -- Midpoint of percent cover class for an additional plant community in the AA other than those previously listed
"hab1_structural_patch_type_1", -- Metric rating based on the number of different types of structural habitat patch types found within the AA
"other10", -- Midpoint of percent cover class for an additional plant community in the AA other than those previously listed
"b3_altered_high_impact_land_1", -- Percent of the buffer area that is a high impact land use
"sub_watershed", -- HUC12 watershed that the wetland site is within
"b1_with_10m_buffer_rating", -- Metric rating based on percent of AA perimeter that has at least 5m of natural or semi-natural condition land cover that is at least 10m in width
"hab3_plant_fragments_rating", -- Metric rating based on the volume of plant fragments in the upper soil horizon
"marsh_type", -- Specifies if wetland is natural or has been manipulated in some way by human activity to be restored, re-established, enhanced, rehabilitated, or impounded
"aa_moved", -- Indicates if the site center point and assessment area had to be moved from the original random location
"unhealthy_marsh", -- Midpoint of percent cover class for unhealthy marsh in the AA (i.e. deterioration or sudden wetland dieback)
"h3_diking_restriction_rating", -- The presence of dikes or other tidal flow restrictions
"hab1_2_bear_cap_hollows", -- Indicates if the wetland was composed partially of hollows (low, soft, unvegetated areas) in the AA
"root_mat", -- Midpoint of percent cover class for all areas in the AA covered by root mat
"other1_1", -- Description of additional plant community in the AA other than those previously listed
"distance_to_open_water_m", -- Estimated distance (m) from the edge of the AA to the closest source of open water that is >30m wide
"juncus_roemerianus", -- Midpoint of percent cover class for Juncus roemerianus in the AA
"distichlis_spicata", -- Midpoint of percent cover class for Distichlis spicata in the AA
"hab2_horizontal_veg_obs_rating", -- Metric rating based on calculations of visual horizontal obstruction by vegetation on a vegetation board at various heights.
"hab2_horizontal_veg_obs_obs", -- Visual horizontal obstruction by vegetation at 0.25 to 1.25m heights measured in 0.25m intervals with a cover board
"hab2_horizontal_veg_obs_avg", -- Average number of blocks visible on vegetation board at sub-plots
"h2_estimate_fill", -- Estimated percent of the AA that has fill
"b5_6_barriers_to_landward", -- Percent of landward perimeter of wetland within 250m that has physical barriers preventing wetland migration inland
"b3_altered_high_impact_land", -- Percent of the buffer area that is an altered (unnatural) land use
"depth_of_organic_layer_cm", -- Describes how deep the organic layer extended below the surface in a soil sample at the site
"other5_1", -- Description of additional plant community in the AA other than those previously listed
"reason_for_moving", -- Describes why the site center point and assessment area had to be moved from the original random location
"b2_natural_land_uses_in_buffer_1", -- Metric rating based on percent of the buffer area that is contiguous between the AA and 250m buffer edge and in a natural or semi-natural state
"other13_1", -- Description of additional plant community in the AA other than those previously listed
"other7_1", -- Description of additional plant community in the AA other than those previously listed
"unveg_mud_sand", -- Midpoint of percent cover class for unvegetated areas, mud, or sand in the AA
"hab4_6_invasive_co_dominant_1", -- Metric rating based on the percent of co-dominant non-native, invasive species in the AA
"other2_1", -- Description of additional plant community in the AA other than those previously listed
"other13", -- Midpoint of percent cover class for an additional plant community in the AA other than those previously listed
"other6_1", -- Description of additional plant community in the AA other than those previously listed
"other14", -- Midpoint of percent cover class for an additional plant community in the AA other than those previously listed
"hab1_2_bear_cap_rating", -- Metric rating based on average measure of soil resistance (cm)
"salicornia_virginica", -- Midpoint of percent cover class for Salicornia virginica in the AA
"b5_6_barriers_to_landward_1", -- Metric rating based on percent of landward perimeter of wetland within 250m that has physical barriers preventing wetland migration inland
"hab1_2_bear_cap_final_intial" -- Average bearing capacity, a measure of soil resistance (cm)
FROM
"delaware-gov/condition-of-tidal-wetlands-rgup-nusm:latest"."condition_of_tidal_wetlands"
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 delaware-gov/condition-of-tidal-wetlands-rgup-nusm 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 delaware-gov/condition-of-tidal-wetlands-rgup-nusm: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 delaware-gov/condition-of-tidal-wetlands-rgup-nusmCheckout 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 delaware-gov/condition-of-tidal-wetlands-rgup-nusm:latestThis will download all the objects for the latest tag of delaware-gov/condition-of-tidal-wetlands-rgup-nusm 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 delaware-gov/condition-of-tidal-wetlands-rgup-nusm: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 delaware-gov/condition-of-tidal-wetlands-rgup-nusm: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, delaware-gov/condition-of-tidal-wetlands-rgup-nusm is just another Postgres schema.