# Query your data with SPARQL

> Write SPARQL queries over New York City's taxi zones and their green taxi pickups. List the zones, filter and sort them, join two data files, keep the zones with no pickups, and count and add up by borough.

Source: https://semvia.io/docs/quickstart/query-your-data-with-sparql

Folio answers questions about your data in [SPARQL](https://www.w3.org/TR/sparql11-query/), the query language for knowledge graphs. You ask in the terms of your domain model, such as taxi zones and their pickups, and Folio reads the data files your mapping names to answer. One query can read several data files at once.

These examples use the [NYC Taxi & Limousine Commission's (TLC) public trip data](https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page), which you can set up in [Query data files](https://semvia.io/docs/federation/query-data-files).

The domain model is one class, `Zone`, with four properties:

Graph

Figure: The Zone card, a Class Schema, with its comment and four instance properties: average fare, a decimal, 0..1; borough, a string, 1..1; green pickups, an integer, 0..1; and name, a string, 1..1.

The Zone class with its four properties.

Turtle

```turtle
# A zone is one of the areas TLC divides New York City into.
taxi:Zone a svs:ClassSchema ;
  rdfs:label "Zone" ;
  rdfs:comment """One of the areas, roughly a neighborhood or an airport, that TLC
    divides New York City into and that trip records give instead of an address.""" ;
  sh:property [
    sh:path taxi:name ;
    sh:name "name" ;
    sh:description "The name TLC gives the zone, such as Astoria or JFK Airport." ;
    sh:datatype xsd:string ;
    sh:minCount 1 ;
    sh:maxCount 1
  ], [
    sh:path taxi:borough ;
    sh:name "borough" ;
    sh:description """Which of New York City's five boroughs the zone lies in, or EWR
      for Newark Airport.""" ;
    sh:datatype xsd:string ;
    sh:minCount 1 ;
    sh:maxCount 1
  ], [
    sh:path taxi:greenPickups ;
    sh:name "green pickups" ;
    sh:description "How many green taxi trips started in the zone in January 2024." ;
    sh:datatype xsd:integer ;
    sh:maxCount 1
  ], [
    sh:path taxi:averageFare ;
    sh:name "average fare" ;
    sh:description """The average amount a green taxi trip that started in the zone
      charged its riders in January 2024, in US dollars.""" ;
    sh:datatype xsd:decimal ;
    sh:maxCount 1
  ] .
```

## 1. List the zones

A query describes the answer it wants with triple patterns. A triple pattern is a statement with a variable in it, a name written with a `?`: `?zone a taxi:Zone` matches each thing that is a zone. `PREFIX` gives `https://example.org/taxi#`, the namespace the `Zone` class is declared in, the short name `taxi:`. The answer has one row for each way the patterns match, and one column for each variable after `SELECT`.

`ORDER BY` sorts the rows, and `LIMIT` keeps the first few. Each query's **SQL** tab asks roughly the same question in SQL, of the data files directly.

SPARQL

```sparql
PREFIX taxi: <https://example.org/taxi#>

# Select two columns: the zone's name and its borough.
SELECT ?name ?borough WHERE {
  # For each thing that is a zone
  ?zone a taxi:Zone ;
    # Select its name as name and borough as borough
    taxi:name ?name ;
    taxi:borough ?borough .
}
# Sort by name, A to Z
ORDER BY ?name
# Keep the first ten
LIMIT 10
```

SQL

```sql
-- For illustration only: roughly the same query in SQL

SELECT "Zone" AS name, "Borough" AS borough
FROM 'taxi_zone_lookup.csv'
ORDER BY "Zone"
LIMIT 10;
```

Figure: zones.rq in Folio with the Results tab under it, reading 10 rows. Columns name and borough. Allerton/Pelham Gardens, Bronx. Alphabet City, Manhattan. Arden Heights, Staten Island. Arrochar/Fort Wadsworth, Staten Island. Astoria, Queens. Astoria Park, Queens. Auburndale, Queens. Baisley Park, Queens. Bath Beach, Brooklyn. Battery Park, Manhattan.

One row for each of the first ten zones A to Z, with the borough it lies in. Folio reads the zone lookup CSV to answer.

## 2. Filter and sort

`FILTER` keeps only the rows that pass a test, and `ORDER BY DESC` sorts the rows largest first.

SPARQL

```sparql
PREFIX taxi: <https://example.org/taxi#>

# Select two columns: the zone and its pickups.
SELECT ?zone ?pickups WHERE {
  # For each zone, select its green pickups as pickups
  ?zone taxi:greenPickups ?pickups .
  # Keep only the zones with more than 2000 pickups
  FILTER(?pickups > 2000)
}
# Sort by pickups, most first
ORDER BY DESC(?pickups)
```

SQL

```sql
-- For illustration only: roughly the same query in SQL

-- One row for each zone, with how many trips started in it
SELECT "PULocationID" AS zone, count(*) AS pickups
FROM 'green_tripdata_2024-01.parquet'
GROUP BY "PULocationID"
HAVING count(*) > 2000
ORDER BY pickups DESC;
```

Figure: busy-zones.rq in Folio with the Results tab under it, reading 7 rows. Columns zone and pickups (xsd:integer). 74, 12141. 75, 8458. 95, 2915. 43, 2765. 166, 2649. 82, 2623. 41, 2589.

One row for each zone with more than 2000 green taxi pickups in January 2024, the most first. Folio reads the Parquet file to answer, and it knows each zone only by its number, so the zone column shows the last part of each zone's address, such as `74` for `https://example.org/taxi/zone/74`.

## 3. Join two data files

A join matches two patterns on the same variable. In this query, `?zone` takes its pickups from the Parquet file and its name from the CSV file. Both maps build a zone's address from its zone number, so the two patterns match the same zone.

This query and the ones in steps 4 and 5 read the whole Parquet file, so they run with **Allow full fetch** selected.

SPARQL

```sparql
PREFIX taxi: <https://example.org/taxi#>

# Select two columns: the zone's name and its pickups.
SELECT ?name ?pickups WHERE {
  # For each zone, select its green pickups as pickups, from the Parquet file
  ?zone taxi:greenPickups ?pickups .
  # Select the same zone's name as name, from the CSV file
  ?zone taxi:name ?name .
  # Keep only the zones with more than 2000 pickups
  FILTER(?pickups > 2000)
}
# Sort by pickups, most first
ORDER BY DESC(?pickups)
```

SQL

```sql
-- For illustration only: roughly the same query in SQL

-- One row for each zone, with how many trips started in it
WITH pickups AS (
  SELECT "PULocationID" AS zone, count(*) AS pickups
  FROM 'green_tripdata_2024-01.parquet'
  GROUP BY "PULocationID"
)
SELECT z."Zone" AS name, p.pickups
FROM pickups AS p
-- Match each zone to its row in the CSV file, by its number
JOIN 'taxi_zone_lookup.csv' AS z ON z."LocationID" = p.zone
WHERE p.pickups > 2000
ORDER BY p.pickups DESC;
```

Figure: busy-zone-names.rq in Folio with Allow full fetch selected and the Results tab under it, reading 7 rows. Columns name and pickups (xsd:integer). East Harlem North, 12141. East Harlem South, 8458. Forest Hills, 2915. Central Park, 2765. Morningside Heights, 2649. Elmhurst, 2623. Central Harlem, 2589.

The same seven zones, each by its name from the CSV file.

## 4. Keep the zones with no pickups

`OPTIONAL` keeps a row when its pattern matches nothing, and leaves its variable blank.

SPARQL

```sparql
PREFIX taxi: <https://example.org/taxi#>

# Select two columns: the zone's name and its pickups, if any.
SELECT ?name ?pickups WHERE {
  # For each zone whose borough is Staten Island
  ?zone taxi:borough "Staten Island" ;
    # Select its name as name
    taxi:name ?name .
  # Select its green pickups as pickups, or leave pickups blank if it has none
  OPTIONAL { ?zone taxi:greenPickups ?pickups . }
}
# Sort by name, A to Z
ORDER BY ?name
# Keep the first ten
LIMIT 10
```

SQL

```sql
-- For illustration only: roughly the same query in SQL

-- One row for each zone, with how many trips started in it
WITH pickups AS (
  SELECT "PULocationID" AS zone, count(*) AS pickups
  FROM 'green_tripdata_2024-01.parquet'
  GROUP BY "PULocationID"
)
SELECT z."Zone" AS name, p.pickups
FROM 'taxi_zone_lookup.csv' AS z
-- Keep every zone, and leave pickups blank where it had none
LEFT JOIN pickups AS p ON p.zone = z."LocationID"
WHERE z."Borough" = 'Staten Island'
ORDER BY z."Zone"
LIMIT 10;
```

Figure: staten-island.rq in Folio with Allow full fetch selected and the Results tab under it, reading 10 rows. Columns name and pickups (xsd:integer). Arden Heights, blank. Arrochar/Fort Wadsworth, blank. Bloomfield/Emerson Hill, 1. Charleston/Tottenville, blank. Eltingville/Annadale/Prince's Bay, blank. Freshkills Park, blank. Great Kills, blank. Great Kills Park, blank. Grymes Hill/Clifton, blank. Heartland Village/Todt Hill, 1.

One row for each of the first ten Staten Island zones A to Z, with its green taxi pickups in January 2024. Most are blank: 16 of Staten Island's 20 zones had none. Without `OPTIONAL`, the query returns only the 4 zones with a pickup.

## 5. Count and add up

`GROUP BY` puts the rows that share a value into one row. `COUNT` and `SUM` then count the rows in each group and add up a value across them.

SPARQL

```sparql
PREFIX taxi: <https://example.org/taxi#>

# Select three columns: the borough, its zones with pickups, and its pickups in all.
SELECT ?borough (COUNT(?zone) AS ?zones) (SUM(?pickups) AS ?totalPickups) WHERE {
  # For each zone, select its borough as borough, from the CSV file
  ?zone taxi:borough ?borough ;
    # and its green pickups as pickups, from the Parquet file
    taxi:greenPickups ?pickups .
}
# Group the rows by borough: count the zones and add up the pickups in each
GROUP BY ?borough
# Sort by total pickups, most first
ORDER BY DESC(?totalPickups)
```

SQL

```sql
-- For illustration only: roughly the same query in SQL

-- One row for each zone, with how many trips started in it
WITH pickups AS (
  SELECT "PULocationID" AS zone, count(*) AS pickups
  FROM 'green_tripdata_2024-01.parquet'
  GROUP BY "PULocationID"
)
SELECT z."Borough" AS borough, count(*) AS zones, sum(p.pickups) AS "totalPickups"
FROM pickups AS p
JOIN 'taxi_zone_lookup.csv' AS z ON z."LocationID" = p.zone
-- One row for each borough: count its zones and add up their pickups
GROUP BY z."Borough"
ORDER BY "totalPickups" DESC;
```

Figure: boroughs.rq in Folio with Allow full fetch selected and the Results tab under it, reading 8 rows. Columns borough, zones (xsd:integer) and totalPickups (xsd:integer). Manhattan, 39, 32847. Queens, 66, 14937. Brooklyn, 60, 7748. Bronx, 39, 870. Unknown, 1, 112. N/A, 1, 29. Staten Island, 4, 5. EWR, 1, 3.

One row for each borough: how many of its zones had a green taxi pickup in January 2024, and the pickups in all. Unknown and N/A are the boroughs TLC gives its two placeholder zones, for a trip it couldn't place and for one outside New York City.

[Next: Graph your Markdown](https://semvia.io/docs/quickstart/graph-your-markdown): Turn each Markdown file into an entity, relate the files, and query across the graph.
