This exercise requires a CrateDB cluster with the course dataset loaded. If you didn't create your cluster earlier in the course, complete these steps before going further.
It’s your turn to try out some Geospatial queries in your own CrateDB cluster. Open your cluster’s console and let’s dive in…
First, try running this query to determine how many of each type of 311 call was made from an area close to O’Hare airport:
SELECT srtype, count(*) AS num_calls
FROM three_eleven_calls
WHERE within(
locationdetails['location'],
'POLYGON ((-88.021774 41.905854, -88.021774 42.068155, -87.782822 42.068155, -87.782822 41.905854, -88.021774 41.905854))')
GROUP BY srtype ORDER BY num_calls DESC;
This query returns the names and populations of community areas that a flight path from O’Hare airport to Midway airport passes over:
SELECT
name,
details['population'] as population
FROM community_areas
WHERE
intersects (
'LINESTRING (-87.90753846894022 41.9807787186139, -87.72939445102593 41.97508268795004, -87.55763552335945 41.97982941555023, -87.47846040428642 41.92427055787562, -87.51102436455034 41.837749425166244, -87.59225264192574 41.80057450627759, -87.64801197528328 41.770600684095974, -87.69988112259261 41.7819633786835, -87.7530469985847 41.78583107072223)',
boundaries
)
AND details['population'] > 30000
ORDER BY population DESC;
Now spend a few moments composing some queries of your own, or running the examples above with different criteria. Use the CrateDB Geo search documentation as your guide.
When writing your own Geospatial queries, it’s useful to be able to build up GeoJSON or WKT geometries interactively. The geojson.io tool is excellent for this. Use it to create geometries for your own queries and have fun exploring the dataset.
Don’t forget that you can find geospatial data in the three_eleven_calls table:
SELECT srtype, locationdetails['location'] AS location
FROM three_eleven_calls WHERE srnumber='SR24-00581646';
As well as in the community_areas:
SELECT boundaries from community_areas WHERE name='CHATHAM';
And taxi_rides:
SELECT pickupcentroidlocation, dropoffcentroidlocation
FROM taxi_rides
WHERE tripid='b55b017e464858b1f39d9aaea1cc68acbc9f96db';