Skip to main content
100%

upsert the_geom with security definer

✓ Published0🌍 Public
Aandrewxhill
Last edited Jun 21, 2013
Created on Jun 21, 2013

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.

AI-generated description

Similar vizzes

Loading thumbnail…

Polygon insert Named Function

This example shows a SQL function that inserts new polygon features into a Minneapolis dataset, limited to geometries with fewer than 100 points. The function, `AXH_NewMinneapolis`, takes an integer parent ID and a geometry, then performs an `INSERT` into the `minneapolis` table using the `RETURNING cartodb_id` clause. It is defined with `SECURITY DEFINER` to elevate privileges during execution, and `EXECUTE` permission is granted to the public user. The code uses PostgreSQL/PostGIS spatial functions like `ST_NPoints` and the CartoDB API to manage the data insertion.

Aandrewxhill
70% match
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
66% 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
62% match
Loading thumbnail…

cdb_latlng

This example defines a set of PostgreSQL functions for converting latitude and longitude values into spatial geometries. It shows how to construct valid 4326-point geometries from numeric, floating-point, or text inputs, including support for comma-style decimal separators. The code uses PostGIS functions such as `ST_SetSRID` and `ST_MakePoint` to build the geometry, along with custom helpers like `CDB_DecimalDegreeLiteral` for parsing text coordinates. It handles both individual lat/lng arguments and a single space-separated coordinate string, making it a flexible utility for geospatial data preprocessing in SQL.

Aandrewxhill
61% match
Loading thumbnail…

PlpgSQL to cross dateline when drawing two points

This example demonstrates a PostGIS/PLpgSQL function that corrects lines crossing the 180-degree antimeridian when drawn on a web Mercator map. It takes two point geometries and constructs a MultiLineString that breaks the crossing segment at the map’s edge, inserting a vertical line at ±20037508.34 to create a continuous visual path. The code uses ST_Transform, ST_MakeLine, and ST_Collect to handle the geometry, and defines a custom aggregate (axh_crossdateline) to process ordered point sequences. The function expects SRID 3857 inputs and returns the adjusted line for seamless rendering.

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

Fustion Tables CSV => Postgresql => Geom fix

This example shows a workflow for repairing invalid geometry in a FEMA damage assessment dataset stored in PostgreSQL with PostGIS. It converts KML-style polygon coordinates from a Fusion Tables CSV export into valid PostGIS geometries, then updates the spatial column. The process uses SQL string functions like `replace()` to transform coordinate formatting, `ST_GeomFromText` to parse the text into geometry, and `ST_MakePolygon` with `ST_AddPoint` to close and correct the polygons. The result prepares the data for subsequent mapping or analysis in a GIS environment.

Aandrewxhill
59% match