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 alternative_fuel_stations_in_new_york table in this repository, by referencing it like:
"ny-gov/alternative-fuel-stations-in-new-york-bpkx-gmh7:latest"."alternative_fuel_stations_in_new_york"or in a full query, like:
SELECT
":id", -- Socrata column ID
"city", -- The city of the station's location
"ng_vehicle_class", -- For CNG and LNG stations, the maximum vehicle size that can physically access the fueling infrastructure. LD = Station can only accommodate light-duty vehicles (Classes 1-2); MD = Station can accommodate light- and medium-duty vehicles (Classes 1-5); HD = Station can accommodate light-, medium-, and heavy-duty vehicles (Classes 1-8)
"access_detail_code", -- A description of other station access information, given as code values as described below: KEY_ALWAYS: Card key at all times; CREDIT_CARD_ALWAYS: Credit card at all times; CREDIT_CARD_AFTER_HOURS: Credit card after hours; FLEET: Fleet customers only; GOVERNMENT: Government only; KEY_AFTER_HOURS: Card key after hours; CALL: Call ahead.
"access_code", -- A description of who is allowed to access the station, given as code values as described below: public: Publicly available to all customers; private: Private station.
"federal_agency_code", -- If the station is owned by a federal agency, a record for the federal agency that owns this station. See federal agency record fields for response details.
"federal_agency_id", -- If owned by a federal agency, a unique identifier for the federal agency
"owner_type_code", -- The type of organization that owns the fueling infrastructure P = Privately owned; T = Utility owned; FG = Federal government owned; LG = Local government owned; SG State government owned; J = Jointly owned (combination of owner types)
"cng_on_site_renewable_source", -- For CNG stations, the type of renewable energy used to generate CNG on-site, given as code values as described below: GEOTHERMAL: Geothermal; HYDRO: Hydropower; LANDFILL: Landfill; LIVESTOCK: Livestock Operations; SOLAR: Solar; WASTEWATER: Wastewater Treatment; WIND: Wind.
"lpg_primary", -- For propane stations, the type of station. true = Primary: A propane station is considered primary because it meets the following criteria, developed based on feedback from the propane industry: 1.The price of a gallon of propane autogas, including all taxes, at the station is less expensive than other propane sold (e.g., gas grill bottles); 2.The station is able to fuel vehicles during normal business hours (e.g., 8am-5pm M-F); and 3.The station accepts credit cards. false = Secondary: The station does not meet the criteria to be considered primary
":@computed_region_yamh_8v7k", -- This column was automatically created in order to record in what polygon from the dataset 'NYS Municipal Boundaries' (yamh-8v7k) the point in column 'georeference' is located. This enables the creation of region maps (choropleths) in the visualization canvas and data lens.
"status_code", -- The current status of the station - E = Open
"station_phone", -- The phone number of the station
"intersection_directions", -- Brief additional information about how to locate the station
"plus4", -- The plus-4 portion of the station's ZIP code (U.S. ZIP codes only)
"cng_storage_capacity", -- For CNG stations, the total storage capacity, measured in standard cubic feet (scf).
"facility_type", -- The type of facility at which the station is located, given as code values as described below: AIRPORT: Airport; ARENA: Arena; AUTO_REPAIR: Auto repair shop; BANK: Bank; BREWERY_DISTILLERY_WINERY: Brewery/distillery/winery; B_AND_B: B&B; CAMPGROUND: Campground; CARWASH: Carwash; CAR_DEALER: Car dealer; COLLEGE_CAMPUS: College campus; CONVENIENCE_STORE: Convenience store; CONVENTION_CENTER: Convention center; COOP: Co-op; FACTORY: Factory; FED_GOV: Federal government; FIRE_STATION: Fire station; FLEET_GARAGE: Fleet garage; FUEL_RESELLER: Fuel reseller; GAS_STATION: Service/gas station; GROCERY: Grocery store; HARDWARE_STORE: Hardware store; HOSPITAL: Hospital; HOTEL: Hotel; INN: Inn; LIBRARY: Library; MIL_BASE: Military base; MOTOR_POOL: Motor pool; MULTI_UNIT_DWELLING: Multi-Unit dwelling; MUNI_GOV: Municipal Government; MUSEUM: Museum; NATL_PARK: National park; OFFICE_BLDG: Office building; OTHER: Other; OTHER_ENTERTAINMENT: Other entertainment; PARK: Park; PARKING_GARAGE: Parking garage; PARKING_LOT: Parking lot; PAY_GARAGE: Pay-parking garage; PAY_LOT: Pay-parking lot; PHARMACY: Pharmacy; PLACE_OF_WORSHIP: Place of worship; PRISON: Prison; REC_SPORTS_FACILITY: Recreational sports facility; REFINERY: Refinery; RENTAL_CAR_RETURN: Rental car return; RESEARCH_FACILITY: Research facility/laboratory; RESTAURANT: Restaurant; REST_STOP: Rest stop; RV_PARK: RV Park; SCHOOL: School; SHOPPING_CENTER: Shopping center; SHOPPING_MALL: Shopping mall; STADIUM: Stadium; STANDALONE_STATION: Stand-alone station; STATE_GOV: State/provincial government; STORAGE: Storage facility; TRAVEL_CENTER: Travel center; TRUCK_STOP: Truck stop; UTILITY: Utility.
"groups_with_access_code_french", -- A description of who is allowed to access the station and other station access information, in French, if available: Privé: Private station; Privé - réservé aux clients de parcs: Private station that may allow other entities to fuel through a business-to-business arrangement. For more information, fleet customers should refer to the “Access Days Time” field for contact information or contact the station directly; Privé – réservé au gouvernement: Private station available only to government vehicles, or some subset of government vehicles; Public: Publicly available to all customers; Public – appeler à l'avance: Publicly available, but customers should call before visiting the station. See “Station Phone” for more details; Public – carte de crédit en tout temps: Publicly available, but only accepts credit cards as payment. The station may also accept fleet cards or station-specific fueling cards. See “Cards Accepted” field for more details; Public – carte-clé en tout temps: Publicly available, but only accepts fleet cards or station-specific fueling cards as payment. See “Cards Accepted” field for more details; Public – carte de crédit après les heures d'ouverture; Publicly available, and accepts credit cards 24 hours a day. See “Access Days Time” and “Cards Accepted” fields for more details; Public – carte-clé après les heures d'ouverture: Publicly available, and accepts fleet cards or station-specific fueling cards 24 hours a day. See “Access Days Time” and “Cards Accepted” fields for more details; PRÉVU - pas encore accessible: Not yet open, but plans to carry alternative fuel in the future. See the "Expected Date" field for an anticipated open date; TEMPORAIREMENT SUSPENDU: Temporarily unavailable. See the “Expected Date” field for an anticipated open date.
"access_days_time_french", -- Hours of operation for the station, in French, if available.
"intersection_directions_french", -- Brief additional information about how to locate the station, in French, if available.
"latitude", -- The latitude of the station's address (see the "Geocode Status" field for details on the latitude's accuracy)
"ev_network", -- For electric stations, the name of the EVSE network, if applicable
"street_address", -- The street address of the station's location
"bd_blends_french", -- For biodiesel stations, the level of biodiesel blends the station provides, in French, if available.
"lng_on_site_renewable_source", -- For LNG stations, the type of renewable energy used to generate CNG on-site, given as code values as described below: GEOTHERMAL: Geothermal; HYDRO: Hydropower; LANDFILL: Landfill; LIVESTOCK: Livestock Operations; SOLAR: Solar; WASTEWATER: Wastewater Treatment; WIND: Wind.
"ev_dc_fast_count", -- For electric stations, the number of DC Fast Chargers
"ev_on_site_renewable_source", -- For EVSE stations, the type of renewable energy used to generate electricity on-site, given as code values: GEOTHERMAL: Geothermal; HYDRO: Hydropower; LANDFILL: Landfill; LIVESTOCK: Livestock Operations; SOLAR: Solar; WASTEWATER: Wastewater Treatment; WIND: Wind.
"lng_vehicle_class", -- For LNG stations, the maximum vehicle size that can physically access the fueling infrastructure, given as code values as described below: LD: Station can only accommodate light-duty vehicles (Classes 1-2); MD: Station can accommodate light- and medium-duty vehicles (Classes 1-5); HD: Station can accommodate light-, medium-, and heavy-duty vehicles (Classes 1-8). Note: This field does not take into account station capacity or other considerations.
"cng_fill_type_code", -- For CNG stations, the type of dispensing capability available, given as code values as described below: Q: Quick fill; T: Timed fill; B: Both: quick fill and timed fill.
"hydrogen_pressures", -- For hydrogen stations, any array of strings identifying the pressures of the hydrogen available (in bar), given as code values as described below: 350: 350 bar (35 megapascal, or MPa); 700: 700 bar (70 MPa).
"e85_blender_pump", -- For E85 stations, an indication of whether the station has a blender pump on site. true = In addition to E85, the station has a blender pump capable of providing mid-level ethanol blends. false = The station does not have a blender pump
"state", -- The two character U.S. state code of the station's location
"cng_dispenser_num", -- For CNG stations, the number of CNG dispensers installed.
"ev_other_info", -- For electric stations, the number and type of additional EVSE, such as: SP Inductive = Small paddle inductive; LP Inductive = Large paddle inductive; Avcon = Conductive
"ng_psi", -- For CNG stations, a space separated list of PSI pressures available
"ev_pricing", -- For EVSE stations, information about whether and how much users must pay to use the EVSE.
"access_days_time", -- Hours of operation for the station
"lpg_nozzle_types", -- For LPG stations, an array of strings, identifying the type of nozzles available at the station, given as code values described below: ACME: ACME; QUICK_CONNECT: Quick-connect
"cng_total_compression_capacity", -- For CNG stations, the total compressor capacity per compressor, measured in standard cubic feet per minute (scfm).
"station_name", -- The name of the station
"zip", -- The ZIP code (postal code) of the station's location
":@computed_region_kjdx_g34t", -- This column was automatically created in order to record in what polygon from the dataset 'Counties' (kjdx-g34t) the point in column 'georeference' is located. This enables the creation of region maps (choropleths) in the visualization canvas and data lens.
"expected_date", -- For planned stations, the date the station is expected to open or start carrying alternative fuel. For temporarily unavailable stations, the expected date is the date the station is expected to reopen. This date is estimated but not guaranteed
"georeference", -- Open Data/Socrata-generated geocoding information from supplied address components.
"e85_other_ethanol_blends", -- For E85 stations, an array of strings identifying the range(s) of blends other than E85 available at the station, given as code values described below: E15: Station also offers E15; E20-E25: Station also offers blends of E20-E25; E30-E35: Station also offers blends of E30-E35; E40-Plus: Station also offers blends of E40 and above.
"cng_vehicle_class", -- For CNG stations, the maximum vehicle size that can physically access the fueling infrastructure, given as code values as described below: LD: Station can only accommodate light-duty vehicles (Classes 1-2); MD: Station can accommodate light- and medium-duty vehicles (Classes 1-5); HD: Station can accommodate light-, medium-, and heavy-duty vehicles (Classes 1-8). Note: This field does not take into account station capacity or other considerations.
"hydrogen_standards", -- For hydrogen stations, any array of strings identifying which SAE International fueling protocol standard(s) the infrastructure meets, given as code values as described below: J2601: SAE J2601; J2601-2: SAE J2601-2.
"ev_pricing_french_", -- French language description: For EVSE stations, information about whether and how much users must pay to use the EVSE.
"ev_level1_evse_num", -- For electric stations, the number of Level 1 EVSE (standard 110V outlet)
"hydrogen_is_retail", -- For hydrogen stations, whether a station offers the retail sale of hydrogen by accepting payment at the point of sale, given as code values as described below: true: Station meets the criteria to be considered retail; false: Station does not meet the criteria to be considered retail.
"bd_blends", -- For biodiesel stations, the level of biodiesel blends the station provides
"country", -- The country of the station’s location
"cng_psi", -- For CNG stations, a space separated list of PSI pressures available.
"ev_network_web", -- For electric stations, the EVSE network Web site, if applicable
"ng_fill_type_code", -- For CNG stations, the type of dispensing capability available. Q = Quick fill; T = Timed fill; B = Both: quick fill and timed fill
"hydrogen_status_link", -- For hydrogen stations, a link to a website that provides up-to-date information about the current status of this hydrogen station
"longitude", -- The longitude of the station's address (see the "Geocode Status" field for details on the longitude's accuracy)
"fuel_type_code", -- BD = Biodiesel (B20 and above); CNG = Compressed Natural Gas; E85 = Ethanol (E85); ELEC = Electric; HY = Hydrogen; LNG = Liquefied Natural Gas; LPG = Liquefied Petroleum Gas (Propane)
"federal_agency_name", -- If owned by a federal agency, the name of the federal agency
":@computed_region_wbg7_3whc", -- This column was automatically created in order to record in what polygon from the dataset 'New York Zip Codes' (wbg7-3whc) the point in column 'georeference' is located. This enables the creation of region maps (choropleths) in the visualization canvas and data lens.
"ev_connector_types", -- For electric stations, an array of strings identifying the connector types available at this station. NEMA515 = NEMA 5-15 (Level 1); NEMA520 = NEMA 5-20 (Level 1); NEMA1450 = NEMA 14-50 (Level 1); J1772 = J1772 (Level 2); CHADEMO = HAdeMO (DC fast charging); J1772COMBO = SAE J1772 Combo (DC fast charging); TESLA = Tesla (DC fast charging)
"open_date", -- The date that the station began offering the fuel. Please note that LPG (propane) stations do not have open dates. Some open dates are approximate
"updated_at", -- The time the station's details were last updated (ISO 8601 format)
"id", -- A unique identifier for this specific station
"date_last_confirmed", -- The date the station's details were last confirmed
"geocode_status", -- A rating indicating the approximate accuracy of the latitude and longitude for the station's address. GPS = The location is from a real GPS readout at the station; 200-9 = Premise (building name, property name, shopping center, etc.) level accuracy; 200-8 = Address level accuracy; 200-7 = Intersection level accuracy; 200-6 = Street level accuracy; 200-5 = ZIP code (postal code) level accuracy; 200-4 = Town (city, village) level accuracy; 200-3 = Sub-region (county, municipality, etc.) level accuracy; 200-2 Region (state, province, prefecture, etc.) level accuracy; 200-1 = Country level accuracy; 200-0 = Unknown accuracy
"ev_level2_evse_num", -- For electric stations, the number of Level 2 EVSE (J1772 connector)
"cards_accepted", -- A space-separated list of payment methods accepted - for explanation see http://developer.nrel.gov/docs/transportation/alt-fuel-stations-v1/all/#fuel-station-record-fields.
"groups_with_access_code" -- A description of who is allowed to access the station and other station access information. Public = Publicly available to all customers; Public – call ahead = Publicly available, but customers should call before visiting the station. See “Station Phone” for more details; Public – credit card at all times = Publicly available, but only accepts credit cards as payment. The station may also accept fleet cards or station-specific fueling cards. See “Cards Accepted” field for more details; Public – card key at all times = Publicly available, but only accepts fleet cards or station-specific fueling cards as payment. See “Cards Accepted” field for more details; Public – credit card after hours = Publicly available, and accepts credit cards 24 hours a day. See “Access Days Time” and “Cards Accepted” fields for more details; Public – card key after hours = Publicly available, and accepts fleet cards or station-specific fueling cards 24 hours a day. See “Access Days Time” and “Cards Accepted” fields for more details
FROM
"ny-gov/alternative-fuel-stations-in-new-york-bpkx-gmh7:latest"."alternative_fuel_stations_in_new_york"
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/alternative-fuel-stations-in-new-york-bpkx-gmh7 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/alternative-fuel-stations-in-new-york-bpkx-gmh7: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
Install Splitgraph Locally
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/alternative-fuel-stations-in-new-york-bpkx-gmh7" \
--handler-options '{
"domain": "data.ny.gov",
"tables": {
"alternative_fuel_stations_in_new_york": "bpkx-gmh7"
}
}'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/alternative-fuel-stations-in-new-york-bpkx-gmh7 is just another Postgres schema.