kysely-postgis

Extension to use postgis functions with kysely more easely.

/!\ Early development

Example

import { Database } from './types.ts'; // the Database interface
import { Pool } from 'pg';
import { Kysely, PostgresDialect } from 'kysely';
import { stf, setDefaultOptions } from 'kysely-postgis';

const dialect = new PostgresDialect({
  pool: new Pool(/* Pool configuration*/),
});
const db = new Kysely()<Database>({ dialect });

// By default, WKT and GeoJSON are validated
// Disable GeoJSON and WKT validation globally
// setDefaultOptions({
//   validate: false,
// });

// Returns a GeoJSON from geometry column
const query1 = db
  .selectFrom('table')
  .select((eb) => stf(eb).asGeoJSON('column').as('geojson'))
  .compile();
console.log(query1.sql);
//select ST_AsGeoJSON("column") as "geojson" from "table"

// Transform a GeoJSON Object to geometry
const query2 = db
  .selectFrom('table')
  .select((eb) =>
    stf(eb)
      .geomFromGeoJSON({
        type: 'Point',
        coordinates: [125.6, 10.1],
      })
      .as('geojson'),
  )
  .compile();
console.log(query2.sql, query2.parameters);
//select ST_GeomFromGeoJSON($1) as "geojson" from "table" [ '{"type":"Point","coordinates":[125.6,10.1]}' ]

// Transform a GeoJSON string to geometry
const query3 = db
  .selectFrom('table')
  .select((eb) =>
    stf(eb)
      .geomFromGeoJSON(eb.val(`{"type": "Point","coordinates": [125.6, 10.1]}`))
      .as('geojson'),
  )
  .compile();
console.log(query3.sql, query3.parameters);
//select ST_GeomFromGeoJSON($1) as "geojson" from "table" [ '{"type": "Point","coordinates": [125.6, 10.1]}' ]

// Transform a WKT string to geometry
const query4 = db
  .selectFrom('table')
  .select((eb) =>
    stf(eb)
      .geomFromText('POINT(1 2)'))
      .as('geom'),
  )
  .compile();
console.log(query4.sql, query4.parameters);
//select ST_GeomFromText($1) as "geom" from "table" [ 'POINT(1, 2)' ]

// You need to use eb.val() to pass a GeoJSON string or the string will be considerate like a column
const query5 = db
  .selectFrom('table')
  .select((eb) =>
    stf(eb)
      .area(eb.val(`{"type": "Polygon","coordinates": [
        [[100.0, 0.0],[101.0, 0.0],[101.0, 1.0],[100.0, 1.0],[100.0, 0.0]]
      ]}`))
      .as('geom'),
  )
  .compile();
console.log(query5.sql, query5.parameters);
//select ST_Area($1) as "geom" from "table" [ '{"type": "Polygon","coordinates": [[[100.0, 0.0],[101.0, 0.0],[101.0, 1.0],[100.0, 1.0][100.0, 0.0]]]}' ]

// You can pass any additional parameters to a ST function
// Be careful, theses parameters will simply be add at the end of the parameters
const query6 = db
  .selectFrom('table')
  .select((eb) =>
    stf(eb)
      .buffer('geom', 1, {
          additionalParameters: [eb.val('endcap=square join=round')],
        })
      .as('geom'),
  )
  .compile();
console.log(query6.sql, query6.parameters);
//select ST_Buffer("geom", $1, $2) as "geojson" from "etablissementIsochrone" [ 1, 'endcap=square join=round' ]

Currently supported functions

• area(geo column | GeoJSON Polygon/MultiPolygon /object, string/, { useSpheroid? }), see postgis documentation
• asGeoJSON(column, { maxDecimalDigits?, options? }), see postgis documentation
• asText(geo column), see postgis documentation
• boundary(geo column | GeoJSON /object, string/), see postgis documentation
• buffer(geo column | GeoJSON /object, string/, radius), see postgis documentation
• centroid(geo column | GeoJSON /object, string/), see postgis documentation
• contains(geoa column | GeoJSON /object, string/, geob column | GeoJSON /object, string/), see postgis documentation
• covers(geoa column | GeoJSON /object, string/, geob column | GeoJSON /object, string/), see postgis documentation
• crosses(geoa column | GeoJSON /object, string/, geob column | GeoJSON /object, string/), see postgis documentation
• dWithin(geoa column | GeoJSON /object, string/, geob column | GeoJSON /object, string/, distance number), see postgis documentation
• difference(geoa column | GeoJSON /object, string/, geob column | GeoJSON /object, string/), see postgis documentation
• disjoint(geoa column | GeoJSON /object, string/, geob column | GeoJSON /object, string/), see postgis documentation
• distance(geoa column | GeoJSON /object, string/, geob column | GeoJSON /object, string/), see postgis documentation
• distanceSphere(geoa column | GeoJSON /object, string/, geob column | GeoJSON /object, string/), see postgis documentation
• equals(geoa column | GeoJSON /object, string/, geob column | GeoJSON /object, string/), see postgis documentation
• expand(geoa column | GeoJSON /object, string/, unitsToExpand number), see postgis documentation
• geomFromGeoJSON(GeoJSON /object, string or column name/), see postgis documentation
• geomFromText(WKT string, { srid? }), see postgis documentation
• geoHash(geoa column | GeoJSON /object, string/), see postgis documentation
• intersection(geoa column | GeoJSON /object, string/, geob column | GeoJSON /object, string/), see postgis documentation
• intersects(geoa column | GeoJSON /object, string/, geob column | GeoJSON /object, string/), see postgis documentation
• isValid(geo column | GeoJSON /object, string/), see postgis documentation
• makeValid(geo column | GeoJSON /object, string/), see postgis documentation
• maxDistance(geoa column | GeoJSON /object, string/, geob column | GeoJSON /object, string/), see postgis documentation
• overlaps(geoa column | GeoJSON /object, string/, geob column | GeoJSON /object, string/), see postgis documentation
• srid(geo column | GeoJSON /object, string/), see postgis documentation
• scale(geoa column | GeoJSON /object, string/, xFactor number, yFactor number), see postgis documentation
• segmentize(geoa column | GeoJSON /object, string/, maxLength: number), see postgis documentation
• setSRID(geoa column | GeoJSON /object, string/, srid), see postgis documentation
• simplify(geoa column | GeoJSON /object, string/, tolerance number, see postgis documentation
• simplifyPreserveTopology(geoa column | GeoJSON /object, string/, tolerance number), see postgis documentation
• transform(geoa column | GeoJSON /object, string/, srid), see postgis documentation
• translate(geoa column | GeoJSON /object, string/, deltaX number, deltaY number), see postgis documentation
• union(geoa column set | column | GeoJSON /object, string/, geob? | column | GeoJSON /object, string/), see postgis documentation
• within(geoa column | GeoJSON /object, string/, geob column | GeoJSON /object, string/), see postgis documentation
• x(geoa column | GeoJSON /object, string/), see postgis documentation
• y(geoa column | GeoJSON /object, string/), see postgis documentation
• z(geoa column | GeoJSON /object, string/), see postgis documentation
• m(geoa column | GeoJSON /object, string/), see postgis documentation