Efficient bulk update and insert

100%

Efficient bulk update and insert

✓ Published0🌍 Public

Last edited Jun 5, 2013

Created on Jun 5, 2013

This example demonstrates two efficient SQL patterns for bulk loading election results into a CartoDB table named `counties_results`. The `insert.sql` file shows how to add new rows using a single `INSERT INTO ... VALUES` statement with multiple tuples, while `update.sql` performs a bulk update by joining the table against a `FROM (VALUES ...)` clause, matching records on `st_usps` and `fips`. Both files contain identical county-level presidential vote percentage data, including Democratic, Republican, and other party shares, ready to be applied to a spatial database.

AI-generated description

Bulk insert or update CartoDB records

Use the SQL example here to efficiently update CartoDB tables. See both the INSERT and UPDATE variants

Similar vizzes

Loading thumbnail…

CartoDB CSV Bulkloader for Python

This example demonstrates a Python script that bulk-loads CSV data into a CartoDB table via the CartoDB SQL API, converting latitude and longitude columns into geometry using the CDB_LatLng function. It reads rows from a local CSV file, batches inserts of up to 25,000 rows per request, and skips records already present by checking a unique identifier against the table’s latest entry. The code uses Python’s csv, urllib2, and json modules to parse data and send HTTP POST requests, with error handling that writes failed SQL statements to a log file.

Aandrewxhill

63% 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

63% match

Loading thumbnail…

Batch API uploads to CARTO

This example shows how to upload large datasets to CARTO by splitting them into smaller chunks and using the SQL Batch API to merge them. It demonstrates three chained batch queries: creating a combined table with `CREATE TABLE ... UNION ALL`, dropping the `cartodb_id` column with `ALTER TABLE`, and finalizing with `cdb_cartodbfytable`. The code references CARTO’s SQL API and batch query chaining, alongside a Python script from `carto-etl` and GDAL/ogr2ogr alternatives for record-by-record inserts.

Aandrewbt

59% 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

58% match

Loading thumbnail…

Merging an entire directory of data and loading it to CartoDB

This example shows a shell script that uploads every GPX file in a directory to an existing CartoDB table. It demonstrates how to merge an entire folder of GPS track data into a single cloud-hosted dataset. The script relies on a Python utility called `cartodb-utils.py` to handle the upload process. For each `.gpx` file, the script calls the `append` command, passing the file path, table name, API key, and CartoDB account name as arguments. This approach automates the bulk ingestion of geospatial data without manual intervention.

Aandrewxhill

57% 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

56% match

Loading thumbnail…

Gist 89811213f98645c51fa8

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.

Aandrewxhill

56% match

Loading thumbnail…

CartoDB Point Clustering

This example uses CartoDB to render U.S. state polygons with clustered point data. Clicking a state triggers an SQL query that applies ST_Dump and ST_SimplifyPreserveTopology to generate line segments radiating from the state centroid to its boundary points, producing a stylized "POW!" label effect. The visualization combines CartoDB’s tile layer API with custom CartoCSS styling, including pattern fills and text labels, to show how dynamic SQL can reshape geographic features interactively.

Aandrewxhill

54% match