Bikeshare stations within a San Francisco buffer

In this example we are going to showcase how easily we can compute buffers around geometries using the Analytics Toolbox.

Creating a buffer of a neighborhood

The following query creates a buffer with a radius of 50 meters around San Francisco’s Financial District neighborhood using the ST_BUFFER function. The number of steps could be modified in order to make the lines smoother if needed.

carto-un

SELECT `carto-un`.carto.ST_BUFFER(neighborhood_geom, 50, 'meters', 5) AS geo FROM `bigquery-public-data`.san_francisco_neighborhoods.boundaries WHERE neighborhood = "Financial District";

carto-un-eu

SELECT `carto-un-eu`.carto.ST_BUFFER(neighborhood_geom, 50, 'meters', 5) AS geo FROM `bigquery-public-data`.san_francisco_neighborhoods.boundaries WHERE neighborhood = "Financial District";

manual

SELECT carto.ST_BUFFER(neighborhood_geom, 50, 'meters', 5) AS geo FROM `bigquery-public-data`.san_francisco_neighborhoods.boundaries WHERE neighborhood = "Financial District";

In this visualization you can see the Financial Disctrict (darker blue) and its buffer around it. Notice that buffers radius are not necesarily positive numbers. Negative numbers would generate a buffer in the interior of the district’s geomery.

Bikeshare stations within a buffer

Now let’s use the buffer as a way of defining a bigger region around the Financial District of San Francisco and filtering some other geometries.

carto-un

SELECT ST_GEOGPOINT(d2.longitude,d2.latitude) AS geo FROM `bigquery-public-data`.san_francisco_neighborhoods.boundaries d1,
`bigquery-public-data`.san_francisco.bikeshare_stations d2
WHERE d1.neighborhood = "Financial District" AND ST_CONTAINS(`carto-un`.carto.ST_BUFFER(d1.neighborhood_geom, 50, 'meters', 5), ST_GEOGPOINT(d2.longitude, d2.latitude));

carto-un-eu

SELECT ST_GEOGPOINT(d2.longitude,d2.latitude) AS geo FROM `bigquery-public-data`.san_francisco_neighborhoods.boundaries d1,
`bigquery-public-data`.san_francisco.bikeshare_stations d2
WHERE d1.neighborhood = "Financial District" AND ST_CONTAINS(`carto-un-eu`.carto.ST_BUFFER(d1.neighborhood_geom, 50, 'meters', 5), ST_GEOGPOINT(d2.longitude, d2.latitude));

manual

SELECT ST_GEOGPOINT(d2.longitude,d2.latitude) AS geo FROM `bigquery-public-data`.san_francisco_neighborhoods.boundaries d1,
`bigquery-public-data`.san_francisco.bikeshare_stations d2
WHERE d1.neighborhood = "Financial District" AND ST_CONTAINS(carto.ST_BUFFER(d1.neighborhood_geom, 50, 'meters', 5), ST_GEOGPOINT(d2.longitude, d2.latitude));

This query uses the ST_BUFFER and ST_CONTAINS functions in order to filter those bikeshare stations that are contained inside the buffered geometry. The result is displayed below, where bikeshare stations are represented as yellow dots.

This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.