Skip to main content
100%

Create a table to store changes from any of your tables in a log table.

✓ Published0🌍 Public
Aandrewxhill
Last edited Jun 19, 2015
Created on Jun 19, 2015

This example demonstrates a SQL-based version-control system for geospatial data in a CartoDB database. It shows how to create a log table that stores changes from any tracked table, capturing insertions, updates, and deletions with full row data as JSON. The code uses PostgreSQL PL/pgSQL triggers and functions, along with the `row_to_json` and `json_populate_record` functions to serialize and reconstruct records. A trigger on a user-specified table invokes the logging function, storing the geometry, operation type, source ID, and table name in the `version_control` table.

AI-generated description

Similar vizzes

Loading thumbnail…

Leaflet.draw control for CartoDB

This example shows a Leaflet map where users can draw, edit, and delete vector shapes, with changes persisted to a CartoDB backend. The code listens for drawing actions and converts edited or newly drawn layers into GeoJSON, sending them via POST requests to CartoDB’s SQL API. It uses the Leaflet.draw plugin and Leaflet’s layer management (e.g., `layers.eachLayer`, `layers.toGeoJSON`) to collect geometries and associated `cartodb_id` values. A custom SQL function (`osm2_upsert_leaflet_data`) handles inserts, updates, and deletions, and the response updates the layer’s ID after insertion.

Aandrewxhill
57% match
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
57% match
Loading thumbnail…

upsert the_geom with security definer

This example shows a PostgreSQL function that upserts geometry data for a Leaflet-based mapping application. It accepts parallel arrays of CartoDB IDs and GeoJSON strings, then returns operation codes (insert, update, delete) with corresponding cartodb_ids. The function uses PL/pgSQL with SECURITY DEFINER privileges to build a dynamic SQL query that performs updates, deletes, and inserts against the `leaflet_data` table. It leverages PostGIS functions `ST_SetSRID` and `ST_GeomFromGeoJSON` to convert GeoJSON into spatial geometries, while handling null values to trigger deletion.

Aandrewxhill
57% match
Loading thumbnail…

Map - Table

A map and sortable table display geographic data from a CartoDB visualization, with the table listing attribute columns and rows from the map's underlying dataset. The interface uses the CartoDB.js library to render the map and SQL API to query data, while jQuery DataTables provides interactive sorting and pagination. Users can filter table entries by column values, which dynamically updates the map's SQL query to highlight matching features. The example demonstrates a linked map-table workflow for exploring spatial and tabular data together.

Aandrewxhill
56% match
Loading thumbnail…

Notes

A set of tutorial notes for CartoDB demonstrates a progression through mapping workflows, from importing CSV data with latitude/longitude coordinates to styling visualizations with CartoCSS and querying tables via SQL. The notes cover data editing, georeferencing, privacy settings, and integrating CartoDB.js for web maps, referencing external resources like colorbrewer2.org and PostGIS documentation.

Aandrewxhill
56% match
Loading thumbnail…

Leaflet Draw

This example demonstrates a Leaflet-based web map that lets users create, edit, and delete point markers, with changes persisted to a CARTO points-geometry table. It uses Leaflet.draw for the interactive drawing controls and CARTO’s SQL API to read and write GeoJSON features. The map is initialized with a CARTO basemap, and existing points from a CARTO table are loaded via a `cartodb.SQL` query and added to a `L.FeatureGroup`. A custom `persistOnCartoDB` function handles UPDATE, INSERT, and DELETE actions by constructing parameterized SQL statements, which are executed through a security-definer PostgreSQL function to modify the underlying table. The example demonstrates a full read–edit–write workflow for point data, with hooks for the Leaflet.draw events.

Aandrewbt
56% match
Loading thumbnail…

Triggers en Postgresql

This example demonstrates how PostgreSQL triggers automate audit logging by capturing every insert, update, and delete operation on the `emp` table into an `emp_audit` table. The trigger function `process_emp_audit()` uses PL/pgSQL to record the operation type, timestamp, user, and row data, while the `CREATE TRIGGER` statement attaches it as an `AFTER` trigger for each row. The SQL script runs sample queries to show audit entries accumulating after initial inserts, a salary update, and a full table deletion, all through native PostgreSQL trigger and function APIs.

Aaaizemberg
55% match
Loading thumbnail…

command-line python interface for manipulating data on CartoDB

This example demonstrates a command-line Python tool for managing and manipulating data stored on CartoDB, allowing users to upload files, overwrite or append rows, add columns, drop tables, and export datasets. It first shows the workflow of interacting with CartoDB’s SQL and Import APIs, then explains the underlying implementation. The script uses the `requests` library for HTTP calls, `urllib2` for polling import status, and `argparse` for command-line options, translating between CartoDB’s data types and PostgreSQL types during column alterations and insertions.

Aandrewxhill
55% match