kcmo/kansas-city-crime-nibrs-summary-6wc4-sd7p
Icon for Socrata external plugin

Query the Data Delivery Network

Query the DDN

The 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 kansas_city_crime_nibrs_summary table in this repository, by referencing it like:

"kcmo/kansas-city-crime-nibrs-summary-6wc4-sd7p:latest"."kansas_city_crime_nibrs_summary"

or in a full query, like:

SELECT
    ":id", -- Socrata column ID
    "motor_vehicle_theft", -- Definition: The theft of a motor vehicle.  Note: A motor vehicle is a self-propelled vehicle that runs on the surface of land and not on rails and that fits one of the following property descriptions: ! Automobiles - sedans, coupes, station wagons, convertibles, taxicabs, or other similar motor vehicles that serve the primary purpose of transporting people ! Buses - motor vehicles that are specifically designed (but not necessarily used) to transport groups of people on a commercial basis ! Recreational Vehicles - motor vehicles that are specifically designed (but not necessarily used) to transport people and also provide them temporary lodging for recreational purposes ! Trucks - motor vehicles that are specifically designed (but not necessarily used) to transport cargo ! Other Motor Vehicles - any other motor vehicles, e.g., motorcycles, motor scooters, trail bikes, mopeds, snowmobiles, or golf carts. 
    "kidnapping_abduction", -- Definition: The unlawful seizure, transportation, and/or detention of a person against his/her will or of a minor without the consent of his/her custodial parent(s) or legal guardian.  Note: Kidnaping/Abduction includes hostage-taking. 
    "burglary_breaking_and_entering", -- Definition: The unlawful entry into a building or other structure with the intent to commit a felony or a theft.   Note: For NIBRS purposes, Larceny-theft is an element of Burglary and, therefore, should not be reported as a separate offense if associated with the unlawful entry of a structure. 
    "bribery", -- Definition: The offering, giving, receiving, or soliciting of anything of value (i.e., a bribe, gratuity, or kickback) to sway the judgment or action of a person in a position of trust or influence.   Note: If the bribery involves changing the outcome of a sporting contest or event, it should be reported under Gambling Offenses as Sports Tampering, not Bribery. 
    "sex_offenses_forcible", -- Definition: Any sexual act directed against another person, forcibly and/or against that person’s will or not forcibly or against the person’s will in instances where the victim is incapable of giving consent. 
    "calendar_year", -- What year a crime occurred. 
    "prostitution_offenses", -- Definition: To unlawfully engage in or promote sexual activities for profit.   Note: This offense involves prostitution by both males and females. 
    "pornography_obscene_material", -- Definition: The violation of laws or ordinances prohibiting the manufacture, publishing, sale, purchase, or possession of sexually explicit material, e.g., literature or photographs. 
    "weapon_law_violations", -- Definition: The violation of laws or ordinances prohibiting the manufacture, sale, purchase, transportation, possession, concealment, or use of firearms, cutting instruments, explosives, incendiary devices, or other deadly weapons. 
    "total_crimes_against_person", -- This column contains the sum total for crimes against person(s). It is the sum of the following columns: Homicide Offenses, Sex Offenses, Forcible, 	Assault Offenses,	Sex Offenses - Non-forcible, 	Kidnapping/Abduction 
    "counterfeiting_forgery", -- Definition: The altering, copying, or imitation of something, without authority or right, with the intent to deceive or defraud by passing the copy or thing altered or imitated as that which is original or genuine or the selling, buying, or possession of an altered, copied, or imitated thing with the intent to deceive or defraud. 
    "robbery", -- Definition: The taking or attempting to take anything of value under confrontational circumstances from the control, custody, or care of another person by force or threat of force or violence and/or by putting the victim in fear of immediate harm.   Note: Because some type of assault is an element of the crime of Robbery, an assault should not be reported as a separate crime as long as it was performed in furtherance of the robbery. However, if the injury results in death, a Homicide Offense must also be reported.  The victims of a robbery include not only those persons and other entities (businesses, financial institutions, etc.) from whom property was taken (or was attempted to be taken), but also those persons toward whom the robber(s) directed force or threat of force in perpetrating the offense. Therefore, although the primary victim in a bank robbery would be the Financial Institution, the teller toward whom the robber pointed a gun and made a demand should also be reported as a victim. Carjackings are Robbery offenses in which a motor vehicle is taken through force or threat of force. Report only a Robbery not a Motor Vehicle Theft.  
    "sex_offenses_non_forcible", -- Definition: An unlawful attack by one person upon another.  Note: By definition there can be no attempted assaults, only completed assaults.  
    "larceny_theft_offenses", -- Definition: The unlawful taking, carrying, leading, or riding away of property from the possession or constructive possession of another person. 
    "fiscal_year", -- The City's Fiscal Year runs from May 1st to April 30th. While this column only displays the year it is actually set as a date:time format and is set to the last day of the fiscal year. This allows for proper roll-ups in Socrata.
    "arson", -- Definition: To unlawfully and intentionally damage or attempt to damage any real or personal property by fire or incendiary device. 
    "assault_offenses", --  Definition: Unlawful, nonforcible sexual intercourse. Such as Incest or Statutory Rape
    "homicide_offenses", -- Definition: The killing of one human being by another. 
    "drug_narcotic_offenses", -- Definition: The violation of laws prohibiting the production, distribution, and/or use of certain controlled substances and the equipment or devices utilized in their preparation and/or use. 
    "total_crimes_against_property", -- This column contains the total crimes against property. It is a sum of the following columns: Robbery, Arson, Extortion/Blackmail, Burglary/Breaking and Entering, Larceny/Theft Offenses, Motor Vehicle Theft, Fraud Offenses, Counterfeiting/Forgery, Embezzlement, Stolen Property Offenses, Destruction/Damage/Vandalism, Bribery. 
    "total_crimes_against_society", -- This column contains the total number of crimes against society. This is a sum of the following columns: Drug/Narcotic Offenses, Gambling Offenses, Prostitution Offenses, Pornography/Obscene Material, Weapon Law Violations. 
    "gambling_offenses", -- Definition: To unlawfully bet or wager money or something else of value; assist, promote, or operate a game of chance for money or some other stake; possess or transmit wagering information; manufacture, sell, purchase, possess, or transport gambling equipment, devices, or goods; or tamper with the outcome of a sporting event or contest to gain a gambling advantage
    "fraud_offenses", -- Definition: The intentional perversion of the truth for the purpose of inducing another person or other entity in reliance upon it to part with something of value or to surrender a legal right.   
    "embezzlement", -- Definition: The unlawful misappropriation by an offender to his/her own use or purpose of money, property, or some other thing of value entrusted to his/her care, custody, or control
    "stolen_property_offenses", -- Definition: Receiving, buying, selling, possessing, concealing, or transporting any property with the knowledge that it has been unlawfully taken, as by Burglary, Embezzlement, Fraud, Larceny, Robbery, etc. 
    "destruction_damage_vandalism", -- Definition: To willfully or maliciously destroy, damage, deface, or otherwise injure real or personal property without the consent of the owner or the person having custody or control of it.   Note: This offense is to be reported only if the reporting agency deems that substantial injury to property has occurred. The offense includes a broad range of injury to property, i.e., from deliberate, extensive destruction of property at one extreme to mischievous, less extensive damage at the other extreme. It does not include destruction or damage to property caused by the crime of Arson. Incidental damage resulting from another offense (e.g., Burglary or Robbery) is to be reported in this offense category only if the reporting agency deems the amount of damage to be substantial. For example, insubstantial damage, such as a broken window or forced door, should not be reported, but substantial damage, such as major structural damage caused by a truck backing into a storefront to gain admittance, should be reported. For the crime of Arson, however, incidental damage resulting from fighting the fire should be included as part of the loss caused by burning. The determination of whether the damage was substantial is left to the discretion of the reporting law enforcement agency and should not require burdensome damage assessments.
    "extortion_blackmail", -- Definition: To unlawfully obtain money, property, or any other thing of value, either tangible or intangible, through the use or threat of force, misuse of authority, threat of criminal prosecution, threat of destruction of reputation or social standing, or through other coercive means
    "date" -- These statistics are presented as a monthly snapshot, and therefore may have revisions in the future. However, the data are presented as tallied at the end of each month. So data marked for December 31st includes data for the entire month of December. The data normally takes a month or two to compile. 
FROM
    "kcmo/kansas-city-crime-nibrs-summary-6wc4-sd7p:latest"."kansas_city_crime_nibrs_summary"
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 kcmo/kansas-city-crime-nibrs-summary-6wc4-sd7p with SQL in under 60 seconds.

This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.kcmo.org. When you querykcmo/kansas-city-crime-nibrs-summary-6wc4-sd7p: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.kcmo.org, 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 \
  "kcmo/kansas-city-crime-nibrs-summary-6wc4-sd7p" \
  --handler-options '{
    "domain": "data.kcmo.org",
    "tables": {
        "kansas_city_crime_nibrs_summary": "6wc4-sd7p"
    }
}'

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, kcmo/kansas-city-crime-nibrs-summary-6wc4-sd7p is just another Postgres schema.