Gist 89811213f98645c51fa8

100%

Gist 89811213f98645c51fa8

✓ Published0🌍 Public

Last edited Jun 4, 2014

Created on Jun 4, 2014

Using CartoDB’s SQL API and PostGIS functions, this notebook explores Gilt purchase data aggregated by zip code. It shows a series of interactive maps, including a bubble map of average session time and choropleths of purchase rates, built from geocoded postal points and polygon tables. The workflow demonstrates nested SQL subqueries to join transaction aggregates with geometries, spatial filtering with `ST_Intersects` and `ST_Buffer` around New York City, and on-the-fly centroid generation via `st_centroid` for a target audience map. Geocoding and tile rendering rely on CartoDB’s default web-mercator projection.

AI-generated description

Similar vizzes

Loading thumbnail…

Gist 6114786

This example implements a private torque-style time-series visualization for geospatial data using PostgreSQL and PostGIS. It defines custom SQL functions that aggregate point features into web-Mercator grid cells, grouping counts by time steps and returning arrays of values and dates for rendering. The code uses `ST_SnapToGrid`, `CDB_XYZ_Resolution`, and `CDB_XYZ_Extent` to tile data at a given zoom level, with two variants: a standard response for instantaneous counts and a cumulative response that includes expiration dates for each cell. A metadata function returns the spatial bounds and time range from the source table, supporting both timestamped and numeric columns.

Aandrewxhill

75% match

Loading thumbnail…

Gist 9378741

This interactive interface guides users through geocoding tabular data, first classifying location columns by type—city names, postal codes, IP addresses, or street addresses—then offering point or polygon output options. After georeferencing, it displays result summaries with success counts, failure explanations, and cost feedback, including free and paid quota messaging. The example uses CartoDB’s geocoding API and a dynamic form interface, with state-driven prompts and dropdowns to collect necessary country or region details before triggering the geocoding process.

Aandrewxhill

75% match

Loading thumbnail…

Gist 7c1efd9bdead734010b0

This tutorial demonstrates how to build interactive maps and animated visualizations of a cat’s GPS tracking data using CartoDB’s web interface, SQL, and CartoCSS. It shows point, categorical, and temporal bubble maps, including a Torque animation of Spencer the Cat’s movements over time. The code uses CartoDB’s SQL API with functions like `ST_MakeLine` and `date_part`, along with CartoCSS styling for markers. It also covers importing polygon building datasets from MassGIS and querying them with spatial operations like `ST_Distance` and `CDB_LatLng`.

Aandrewxhill

74% match

Loading thumbnail…

Gist 2e969e0d45ceb3555cc4

This visualization demonstrates unemployment rates across U.S. states and zip codes using interactive maps. It begins with a choropleth map of April unemployment data by state, then shifts to a bubble map of zip-code-level rates, with bubble size reflecting percentage values. The example uses CartoDB’s georeferencing, SQL updates to clean and convert data, and dynamic SQL queries to reorder or filter results, such as sorting by rate or selecting points within a distance of Washington, D.C. It also incorporates CartoDB.js for interactivity, including a street-view preview on hover.

Aandrewxhill

74% match

Loading thumbnail…

Gist 1198897

This visualization demonstrates the geographic distribution of the 2010 U.S. Census data, mapping population counts and related demographic metrics across states. Built with Ruby and CartoDB importer, it renders interactive choropleth maps using color-coded polygons to represent data values, allowing users to explore spatial patterns in the dataset. The visualization leverages Google Spreadsheet integration for data input and PostgreSQL for storage, with map rendering handled through CartoDB’s geospatial processing pipeline.

Aandrewxhill

73% match

Loading thumbnail…

Fun SQL statements for CartoDB

This example shows how to explore and analyze tornado and county data in CartoDB using SQL statements. It demonstrates basic queries for filtering, sorting, and aggregating records, followed by spatial functions that inspect geometry types, compute areas, distances, and intersections. The code uses PostGIS functions such as `ST_Area`, `ST_Distance`, `ST_Intersects`, and `ST_DWithin`, alongside CartoDB-specific helpers like `CDB_LatLng`. It also includes updates to add tornado counts and density per county, highlighting how SQL can drive interactive map visualizations.

Aandrewxhill

72% match

Loading thumbnail…

rundown

This example demonstrates how SQL queries can filter, analyze, and modify geographic data in CartoDB, using Massachusetts census blocks and Boston corner store locations. It shows spatial queries like distance calculations and bounding-box filters, plus joins to compute nearest fire station distances. The code uses the CartoDB SQL API and CartoDB.js library, with Leaflet for map rendering, to apply dynamic SQL filters and create visualizations without altering the underlying dataset.

Aandrewxhill

71% match

Loading thumbnail…

CartoDB SQL tricks: Various things that as a SQL-newbie I had to search for >10 minutes to find answers for.

This example demonstrates a collection of SQL recipes for solving common spatial data problems in CartoDB, ranging from merging neighboring county polygons with ST_Union to creating great circle lines via ST_MakeLine and ST_Segmentize. It shows practical techniques like updating geometries with conditional CASE statements, extracting coordinates with ST_X and ST_Y, stripping HTML tags using regexp_replace, and formatting zip codes with to_char. The code also covers performance strategies for large updates and querying spatial relationships with st_contains. Each snippet targets PostGIS functions within the CartoDB platform, emphasizing data preparation and geometric transformations.

Aandrewbt

71% match