ny-gov/hate-crimes-by-county-and-bias-type-beginning-2010-6xda-q7ev
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 hate_crimes_by_county_and_bias_type_beginning_2010 table in this repository, by referencing it like:

"ny-gov/hate-crimes-by-county-and-bias-type-beginning-2010-6xda-q7ev:latest"."hate_crimes_by_county_and_bias_type_beginning_2010"

or in a full query, like:

SELECT
    ":id", -- Socrata column ID
    "anti_protestant", -- Count of incidents with a reported Anti-Protestant bias. Protestant: A person who follows the monotheistic religion of Christianity that is not part of Catholic or Eastern Orthodox faith. Members of this faith affirm the Reformation principles of justification by faith alone, the priesthood of all believers, and the primacy of the Bible as the only source of revealed truth. Moreover, believers deny the universal authority of the Pope and some churches are governed by federated councils on the local, national, and international levels. (Merriam-Webster Collegiate Dictionary [Eleventh Edition, 2003]; the Encyclopedia Britannica Micropedia Ready Reference [15th Edition, 2010])
    "anti_gay_male_and_female", -- Count of incidents with a reported Anti-Gay (Male and Female) bias.
    "anti_gay_female", -- Count of incidents with a reported Anti-Female Homosexual (Lesbian) bias.  Lesbian: Of or relating to women who are physically, romantically, and/or emotionally attracted to other women. Some lesbian women prefer to be described as gay women; preferred over the term “homosexual;” may be used as a noun. 
    "crime_type", -- Category of crime defined by the FBI, including Crimes Against Persons (crimes targeting individuals or groups of individuals), and Property Crimes.
    "anti_multi_religious_groups", -- Count of incidents with a reported Anti-Multi-Religious Groups bias. Multiple Religions, Group: A group of persons demonstrating a commitment or devotion to religious faith or observance based upon multiple faiths.
    "anti_transgender", -- Count of incidents with a reported Anti-Transgender bias. Transgender: Of or relating to a person who identifies as a different gender from their gender as determined at birth. The person may also identify himself or herself as “transsexual.” A transgender person may outwardly express his or her gender identity all of the time, part of the time, or none of the time; a transgender person may decide to change his or her body to medically conform to his or her gender identity.
    "anti_other_race", -- Count of incidents with a reported Anti-Other Race bias. Other Race/Ethnicity/Ancestry: A person of a different race/ethnicity/ancestry than is otherwise included in this combined category.
    "anti_non_hispanic", -- Count of incidents with a reported Anti-Non-Hispanic bias. Not Hispanic: A person who is not of Cuban, Mexican, Puerto Rican, South or Central American, or other Spanish culture or origin, regardless of race.  The two bias types included under New York State’s Hate Crime Law (Penal Law Article 485) that are not included in the list of federally-defined bias types are noted with an asterisk(*).
    "anti_heterosexual", -- Count of crimes with a reported Anti-Heterosexual bias. Heterosexual: Of or relating to people who are physically, romantically, and/or emotionally attracted to people of the opposite sex.
    "anti_mental_disability", -- Count of incidents with a reported Anti-Mental Disability bias. Mental Disability: Any mental impairment or psychological disorder such as: organic brain syndrome, emotional or mental illness, and specific learning disabilities. (Americans with Disabilities Act)
    "year", -- Year the crime incident was reported.
    "anti_other_christian", -- Count of incidents with a reported Anti-Other Christian bias. Other Christian: A person who follows other denominations or nondenominational religions based on the life and teachings of Jesus Christ but not described above. Examples of these religions include Metaphysical-Christ Church Unity, Spiritualist, Unity/Unitarianist, Unity Church, Universalist, and Other Metaphysical. (Pew Research Center)
    "anti_gender_non_conforming", -- Count of incidents with a reported Anti-Gender Non-Conforming bias.  Gender Non-Conforming:  Describes a person who does not conform to the gender-based expectations of society, e.g., a woman dressed in traditionally male clothing or a man wearing makeup.  Note: A gender nonconforming person may or may not be a lesbian, gay, bisexual, or transgender person but may be perceived as such. 
    "anti_black", -- Count of incidents with a reported Anti-Black of African American bias. Black or African American: A person having origins in any of the Black racial groups of Africa. This category includes persons from the following nationalities or groups: African American, Kenyan, Nigerian, or Haitian. (Census)
    "anti_atheism_agnosticism", -- Count of incidents with a reported Anti-Atheism/Agnosticism bias. Atheist: A person who does not believe in the existence of a deity. (Merriam-Webster Collegiate Dictionary [Eleventh Edition, 2003]; the Encyclopedia Britannica Micropedia Ready Reference [15th Edition, 2010]) Agnostic: A person who believes that the existence or nature of an ultimate reality, such as a deity, is unknown, and probably unknowable. (Merriam-Webster Collegiate Dictionary [Eleventh Edition, 2003]; the Encyclopedia Britannica Micropedia Ready Reference [15th Edition, 2010])
    "anti_religious_practice_generally", -- Count of incidents with a reported general Anti-Religious Practice bias.
    "anti_jehovahs_witness", -- Count of incidents with a reported Anti-Jehovah’s Witness bias. Jehovah’s Witness: A person who follows the religion founded by Charles Taze Russell. Members witness by distributing literature and by personal evangelism of beliefs in the theocratic rule of God, the sinfulness of organized religions and governments, and an imminent millennium. The activities of Jehovah’s Witnesses are governed by the Watchtower Society which makes all major decisions, interprets the Bible, and counsels Witnesses using Watchtower materials. Members of the faith are often seen giving generously of their time in proclaiming their faith and teaching in private homes. (Merriam-Webster Collegiate Dictionary [Eleventh Edition, 2003]; the Encyclopedia Britannica Micropedia Ready Reference [15th Edition, 2010])
    "anti_mormon", -- Count of incidents with a reported Anti-Mormon bias. Mormon (Latter-day Saint): A person who follows the Church of Jesus Christ of Latter- day Saints tracing its modern origin to Joseph Smith and accepting the Book of Mormon as scripture. Latter-day Saints consider the following writings to be scripture: 1) The Holy Bible; 2) The Book of Mormon, Another Testament of Jesus Christ; 3) The Doctrine and Covenants; and 4) The Pearl of Great Price. Mormons are often associated with members of the faith who serve as full-time volunteer missionaries in the U.S. and abroad. (Merriam-Webster Collegiate Dictionary [Eleventh Edition, 2003]; the Encyclopedia Britannica Micropedia Ready Reference [15th Edition, 2010])
    "anti_gay_male", -- Count of incidents with a reported Anti-Male Homosexual (Gay) bias.  Gay: Of or relating to men who are physically, romantically, and/or emotionally attracted to people of the same sex.  The term “gay” is preferred over the term “homosexual.” While the word gay may also be used to describe women, for FBI UCR Program purposes reporting an anti-gay bias, the victim should be a male.
    "county", -- Location where the crime was reported.
    "anti_male", -- Count of incidents with a reported Anti-Male bias. Male: An individual that produces small usually motile gametes (as spermatozoa or spermatozoids) which fertilize the egg of a female. (Merriam-Webster Dictionary)
    "anti_female", -- Count of incidents with a reported Anti-Female bias. Female: An individual of the sex that bears young or produces eggs. (Merriam-Webster Dictionary)
    "anti_age", -- Count of incidents with a reported Anti-Age bias (60 years old or more). Age (60 years old or more): A preformed negative opinion or attitude toward a person or group of persons based on their actual or perceived age of 60 years old or more. The two bias types included under New York State’s Hate Crime Law (Penal Law Article 485) that are not included in the list of federally-defined bias types are noted with an asterisk(*).
    "anti_white", -- Count of incidents with a reported Anti-White bias. White: A person having origins in any of the original peoples of Europe, the Middle East, or North Africa. This category includes persons from the following nationalities: Irish, German, Italian, Lebanese, Arab, Moroccan, or Caucasian. (Census)
    "anti_american_indian_alaskan_native", -- Count of incidents with a reported Anti-American Indian or Alaskan Native bias. American Indian or Alaska Native: A person having origins in any of the original peoples of North and South America (including Central America) and who maintains tribal affiliation or community attachment. This category includes persons from the following tribal affiliations: Navajo, Blackfeet, Inupiat, Yup’ik, or Central American Indian groups or South American Indian groups. (Census)
    "anti_asian", -- Count of incidents with a reported Anti-Asian bias. Asian: A person having origins in any of the original peoples of the Far East, Southeast Asia, or the Indian subcontinent including, for example, Cambodia, China, India, Japan, Korea, Malaysia, Pakistan, the Philippine Islands, Thailand, and Vietnam. This category includes persons from the following nationalities: Asian Indian, Bangledeshi, Bhutanese, Bermese, Cambodian, Chinese Filipino, Hmong, Indonesian, Japanese, Korean, Laotian, Malaysian, Nepalese, Pakistani, Sri Lankan, Taiwanese, Thai, Vietnamese, Other Asian, specified; Other Asian, not specified. (Census)
    "anti_native_hawaiian_pacific_islander", -- Count of incidents with a reported Anti-Native Hawaiian/Pacific Islander bias. Native Hawaiian or Other Pacific Islander: A person having origins in any of the original peoples of Hawaii, Guam, Samoa, or other Pacific Islands. This category includes persons from the following nationalities: Fijian, Guamanian or Chamorro, Marshallese, Native Hawaiian, Other Micronesian, Other Pacific Islander, not specified; Other Polynesian, Samoan, Tongan. (Census)
    "anti_multi_racial_groups", -- Count of incidents with a reported Anti-Multi-Racial Groups bias. Multiple Races, Group: A group of persons having origins from multiple racial categories.
    "anti_jewish", -- Count of incidents with a reported Anti-Jewish bias. Jewish (Judaism): A person who identifies himself or herself as a member of the religious and/or ethnic group that descended from the ancient Hebrews and is characterized by belief in one transcendent God who revealed Himself to Abraham, Moses, and the Hebrew prophets. Jewish religious practice is based on the Hebrew Scriptures (the “Torah”) and rabbinic laws and customs. (Merriam-Webster Collegiate Dictionary [Eleventh Edition, 2003]; the Encyclopedia Britannica Micropedia Ready Reference [15th Edition, 2010])
    "anti_catholic", -- Count of incidents with a reported Anti-Catholic bias. Catholic: A person who follows the monotheistic religion of Catholic Christianity, especially that of the Roman Catholic Church. Followers of this faith believe the teachings of the Bible, and place emphasis on church traditions, including the historical continuity of the church, the Pope as the head of the church, and the requirement of celibacy of those in the priesthood. (Merriam-Webster Collegiate Dictionary [Eleventh Edition, 2003]; the Encyclopedia Britannica Micropedia Ready Reference [15th Edition, 2010])
    "anti_islamic_muslim", -- Count of incidents with a reported Anti-Islamic (Muslim) bias. Islamic (Muslim): A person who follows the monotheistic religion of Muslims, which includes belief in Allah as the sole deity and in Muhamad as his prophet. Practitioners of the Islamic faith follow the teachings of the Koran and practice the Five Pillars of Islam: praying, fasting during Ramadan, almsgiving, pilgrimage, and declaration of faith. (Merriam-Webster Collegiate Dictionary [Eleventh Edition, 2003]; the Encyclopedia Britannica Micropedia Ready Reference [15th Edition, 2010])
    "anti_other_religion", -- Count of incidents with a reported Anti-Other Religion bias. Other Religions: A person who follows other non-Christian religions not described above. Examples of these religions include the Baha’I Faith, Jainism, Shintoism, Taoism, Tenrikyo, Wicca, and Zoroastrainism. (Pew Research Center)
    "anti_buddhist", -- Count of incidents with a reported Anti-Buddhist bias. Buddhist: A person who follows the religion of eastern or central Asia that grew out of the teaching of Siddhartha Gautama, the Buddha, or Enlightened One. People of this faith believe that suffering is inherent in life and that one can be liberated from it by mental and moral self-purification by following the Four Noble Truths and the Eightfold Path in order to reach nirvana. (Merriam-Webster Collegiate Dictionary [Eleventh Edition, 2003]; the Encyclopedia Britannica Micropedia Ready Reference [15th Edition, 2010])
    "anti_eastern_orthodox_greek_russian_etc", -- Count of incidents with a reported Anti-Eastern Orthodox (Greek, Russian, etc.) bias. Eastern Orthodox (Russian, Greek, Other): A person who follows the monotheistic religion of the Eastern Christian Church. This faith follows the teachings of the Bible and church traditions, accords primacy of honor to the Patriarch of Constantinople as head of the church, and adheres to the decisions of the First Seven Ecumenical Councils and the Byzantine Rite. (Merriam-Webster Collegiate Dictionary [Eleventh Edition, 2003]; the Encyclopedia Britannica Micropedia Ready Reference [15th Edition, 2010])
    "anti_hindu", -- Count of incidents with a reported Anti-Hindu bias. Hindu: A person who observes the traditions and practices of the dominant religion of India, which include acceptance of the sanctity of the Vedas (sacred texts); the understanding of one Divine Reality manifested in multiple forms; acceptance of the laws of karma (principle of cause and effect), dharma (righteous modes of conduct), belief in reincarnation, and the ultimate spiritual goal of enlightenment (moksha). (The Encyclopedia Britannica Micropedia Ready Reference [15th Edition, 2010])
    "anti_sikh", -- Count of incidents with a reported Anti-Sikh bias. Sikh: A person who follows the monotheistic religion founded by Guru Nanak in the Punjab region of South Asia. Sikhs follow the teachings of 10 gurus; study from the religion’s primary sacred text (i.e., the Guru Granth Sahib), and worship in Gurdwaras. Some members of the Sikh faith may be distinguished by the dastaar (Sikh turban) and five religious articles: kesh (unshorn hair, including a beard), kanga (wooden comb), kara (steel bracelet), kachera (short trousers), and kirpan (religious sword). (Merriam- Webster Collegiate Dictionary [Eleventh Edition, 2003]; the Encyclopedia Britannica Micropedia Ready Reference [15th Edition, 2010])
    "anti_hispanic", -- Count of incidents with a reported Anti-Hispanic bias. Hispanic or Latino: A person of Cuban, Mexican, Puerto Rican, South or Central American, or other Spanish culture or origin, regardless of race. Includes people from Hispanic or Latino groups such as: Dominican Republic; Central American (excludes Mexican)–Costa Rican, Guatemalan, Honduran, Nicaraguan, Panamanian, Salvadoran, Other Central American; South America–Argentinian, Bolivian, Chilean, Columbian, Ecuadorian, Paraguayan, Peruvian, Uruguayan, Venezuelan, Other South American; Spaniard–All other Hispanic or Latino.
    "anti_arab", -- Count of incidents with a reported Anti-Arab bias. Arab: A person having origins, and/or ancestry, in any of the Arabic speaking peoples of Lebanon, Syria, Palestine, Jordan, Iraq, Saudi Arabia, Yemen, Oman, United Arab Emirates, Qatar, Bahrain, Kuwait, Egypt, Libya, Tunisia, Comoros, Algeria, Morocco, Sudan, Djibouti, Mauritania, and Somalia.
    "anti_other_ethnicity_national_origin", -- Count of incidents with a reported Anti-Other Ethnicity/National Origin bias. Other Race/Ethnicity/Ancestry: A person of a different race/ethnicity/ancestry than is otherwise included in this category.
    "total_offenders", -- Unique count of offenders reported. Reporting on the number of offenders may be incomplete, partly due to the fact that some property crimes do not involve direct contact between the target and offender. More than one offender may be reported per incident.
    "total_victims", -- Unique count of victims reported. A victim may be a person, group of persons, business, institution, or society as a whole. More than one victim may be reported per incident.
    "total_incidents", -- Sum of hate crime incidents reported.  Each incident includes a single bias type.
    "anti_physical_disability", -- Count of incidents with a reported Anti-Physical Disability bias. Physical Disability: Any physical impairment; any physiological disorder or condition, cosmetic disfigurement, or anatomical loss affecting one or more of the following body systems: neurological, musculoskeletal, special sense organs, respiratory (including speech organs), cardiovascular, reproductive, digestive, genitourinary, hemic and lymphatic, skin, and endocrine. (Americans with Disabilities Act)
    "anti_bisexual" -- Count of incidents with a reported Anti-Bisexual bias. Bisexual: Of or relating to people who are physically, romantically, and/or emotionally attracted to both men and women.
FROM
    "ny-gov/hate-crimes-by-county-and-bias-type-beginning-2010-6xda-q7ev:latest"."hate_crimes_by_county_and_bias_type_beginning_2010"
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/hate-crimes-by-county-and-bias-type-beginning-2010-6xda-q7ev 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/hate-crimes-by-county-and-bias-type-beginning-2010-6xda-q7ev: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/hate-crimes-by-county-and-bias-type-beginning-2010-6xda-q7ev" \
  --handler-options '{
    "domain": "data.ny.gov",
    "tables": {
        "hate_crimes_by_county_and_bias_type_beginning_2010": "6xda-q7ev"
    }
}'

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/hate-crimes-by-county-and-bias-type-beginning-2010-6xda-q7ev is just another Postgres schema.