pg_eviltransform

pg_eviltransform : Coordinate transforms for BD09/GCJ02 via PostGIS ST_Transform

Overview

Packages

Source

pig build pkg pg_eviltransform;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pg_eviltransform;		# install via package name, for the active PG version

pig install pg_eviltransform -v 18;   # install for PG 18
pig install pg_eviltransform -v 17;   # install for PG 17
pig install pg_eviltransform -v 16;   # install for PG 16
pig install pg_eviltransform -v 15;   # install for PG 15
pig install pg_eviltransform -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION pg_eviltransform CASCADE; -- requires postgis

Usage

Sources:

• Official v0.0.4 README
• v0.0.4 release notes
• v0.0.4 control file
• v0.0.4 upgrade SQL

pg_eviltransform extends PostGIS with coordinate transformations involving China’s GCJ-02 and BD-09 systems. Version 0.0.4 also adds exact Jenks natural-break classification through ST_JenksBins array and aggregate overloads.

Coordinate Transformation

CREATE EXTENSION postgis;
CREATE EXTENSION pg_eviltransform;

-- WGS84 to GCJ-02 using a readable coordinate-system name.
SELECT ST_EvilTransform(
    ST_SetSRID('POINT(120 30)'::geometry, 4326),
    'GCJ02'
);

-- BD-09 to Web Mercator.
SELECT ST_EvilTransform(
    ST_SetSRID('POINT(120.011070620552 30.0038830555128)'::geometry, 990002),
    3857
);

Custom SRIDs are 990001 for GCJ-02 and 990002 for BD-09. When neither endpoint uses a custom system, ST_EvilTransform delegates to PostGIS ST_Transform; otherwise it converts through WGS84 (4326) when necessary.

Jenks Natural Breaks

-- Array form; NULL elements are ignored.
SELECT ST_JenksBins(ARRAY[1, 2, NULL, 10, 11]::numeric[], 2);

-- Streaming aggregate form for a large table.
SELECT ST_JenksBins(value, 7)
FROM measurements;

-- Return lower rather than upper bin edges.
SELECT ST_JenksBins(value, 7, true)
FROM measurements;

Array inputs support numeric, double precision, real, bigint, integer, and smallint. Aggregate inputs are numeric or double precision; cast other numeric columns when needed.

API Index and Caveats

• ST_EvilTransform(geometry, integer|text) and ST_EvilTransform(geometry, text, integer|text): four overloads corresponding to the PostGIS ST_Transform interface.
• ST_JenksBins(values[], breaks [, invert]): classifies an array and returns double precision[] edges.
• ST_JenksBins(value, breaks [, invert]): streaming aggregate that avoids materializing array_agg.
• PostGIS is a runtime prerequisite and must be installed before pg_eviltransform.
• Jenks inputs must be finite and breaks must be at least one. numeric values are converted to finite f64, so returned edges are floating-point values.
• When the distinct value count does not exceed breaks, the result is the sorted set of unique values; no valid input rows return NULL.