How to use the slonik.sql.raw function in slonik

To help you get started, we’ve selected a few slonik examples, based on popular ways it is used in public projects.

Secure your code as it's written. Use Snyk Code to scan source code in minutes - no build needed - and fix issues immediately.

github chargetrip / clusterbuster / lib / queries.ts View on Github external
query: string[];
}) =>
  sql`
WITH filtered AS
    (SELECT ${sql.raw(table)}.${sql.raw(geometry)} ${sql.raw(
    attributesToSelect(attributes)
  )}
    FROM ${sql.raw(table)}
    WHERE ST_Intersects(TileBBox(${z}, ${x}, ${y}, 3857), ST_Transform(${sql.raw(
    geometry
  )}, 3857))
      ${sql.raw(query.length > 0 ? `AND ${query.join(' AND ')}` : '')}
    ),
    q as
    (SELECT 1 as c1,
            ST_AsMVTGeom(ST_Transform(${sql.raw(
              geometry
            )}, 3857), TileBBox(${z}, ${x}, ${y}, 3857), ${extent}, ${bufferSize}, false) AS geom,
            jsonb_build_object('count', 1, 'expansionZoom', ${sql.raw(
              `${maxZoomLevel}`
            )},'lng', ST_X (ST_Transform(${sql.raw(
              geometry
            )}, 4326)),'lat', ST_Y (ST_Transform(${sql.raw(
              geometry
            )}, 4326))${sql.raw(attributesToArray(attributes))}) AS attributes
     FROM filtered)
SELECT ST_AsMVT(q, ${sourceLayer}, ${extent}, 'geom') as mvt
from q
`;
github chargetrip / clusterbuster / lib / queries.ts View on Github external
`ST_ClusterDBSCAN(${geometry}, ${zoomToDistance(
            maxZoomLevel,
            radius
          )}, 1) over () AS clusters`
        )}
        ${sql.raw(attributesToSelect(attributes))}
    FROM filtered),
    ${sql.raw(`grouped_clusters_${maxZoomLevel}`)} AS
    (SELECT SUM(theCount) AS theCount,
            clusters AS clusterNo,
            ${sql.raw(`${maxZoomLevel + 1}`)} AS expansionZoom,
      ${sql.raw(attributesFirstToSelect(attributes))}
      ST_Centroid(ST_Collect(center)) AS center
    ${sql.raw(`FROM clustered_${maxZoomLevel}`)}
    GROUP BY clusters),
    ${sql.raw(additionalLevels)}
`;
github chargetrip / clusterbuster / lib / queries.ts View on Github external
z: number;
  table: string;
  geometry: string;
  sourceLayer: string;
  maxZoomLevel: number;
  extent: number;
  bufferSize: number;
  attributes: string[];
  query: string[];
}) =>
  sql`
WITH filtered AS
    (SELECT ${sql.raw(table)}.${sql.raw(geometry)} ${sql.raw(
    attributesToSelect(attributes)
  )}
    FROM ${sql.raw(table)}
    WHERE ST_Intersects(TileBBox(${z}, ${x}, ${y}, 3857), ST_Transform(${sql.raw(
    geometry
  )}, 3857))
      ${sql.raw(query.length > 0 ? `AND ${query.join(' AND ')}` : '')}
    ),
    q as
    (SELECT 1 as c1,
            ST_AsMVTGeom(ST_Transform(${sql.raw(
              geometry
            )}, 3857), TileBBox(${z}, ${x}, ${y}, 3857), ${extent}, ${bufferSize}, false) AS geom,
            jsonb_build_object('count', 1, 'expansionZoom', ${sql.raw(
              `${maxZoomLevel}`
            )},'lng', ST_X (ST_Transform(${sql.raw(
              geometry
            )}, 4326)),'lat', ST_Y (ST_Transform(${sql.raw(
              geometry
github chargetrip / clusterbuster / lib / queries.ts View on Github external
geometry: string;
  query: string[];
  attributes: string[];
  additionalLevels: string;
  zoomToDistance: (zoomLevel: number, radius: number) => number;
}) =>
  sql`
  with filtered as
    (SELECT ${sql.raw(table)}.${sql.raw(geometry)}, 1 as theCount ${sql.raw(
    attributesToSelect(attributes)
  )}
    FROM ${sql.raw(table)}
    WHERE ST_Intersects(TileBBox(${z}, ${x}, ${y}, 3857), ST_Transform(${sql.raw(
    geometry
  )}, 3857))
        ${sql.raw(query.length > 0 ? `AND ${query.join(' AND ')}` : '')}
    ),
    ${sql.raw(`clustered_${maxZoomLevel}`)} AS
    (SELECT ${sql.raw(geometry)} as center,
        theCount,
        ${sql.raw(
          `ST_ClusterDBSCAN(${geometry}, ${zoomToDistance(
            maxZoomLevel,
            radius
          )}, 1) over () AS clusters`
        )}
        ${sql.raw(attributesToSelect(attributes))}
    FROM filtered),
    ${sql.raw(`grouped_clusters_${maxZoomLevel}`)} AS
    (SELECT SUM(theCount) AS theCount,
            clusters AS clusterNo,
            ${sql.raw(`${maxZoomLevel + 1}`)} AS expansionZoom,
github chargetrip / clusterbuster / lib / queries.ts View on Github external
${sql.raw(`clustered_${maxZoomLevel}`)} AS
    (SELECT ${sql.raw(geometry)} as center,
        theCount,
        ${sql.raw(
          `ST_ClusterDBSCAN(${geometry}, ${zoomToDistance(
            maxZoomLevel,
            radius
          )}, 1) over () AS clusters`
        )}
        ${sql.raw(attributesToSelect(attributes))}
    FROM filtered),
    ${sql.raw(`grouped_clusters_${maxZoomLevel}`)} AS
    (SELECT SUM(theCount) AS theCount,
            clusters AS clusterNo,
            ${sql.raw(`${maxZoomLevel + 1}`)} AS expansionZoom,
      ${sql.raw(attributesFirstToSelect(attributes))}
      ST_Centroid(ST_Collect(center)) AS center
    ${sql.raw(`FROM clustered_${maxZoomLevel}`)}
    GROUP BY clusters),
    ${sql.raw(additionalLevels)}
`;
github chargetrip / clusterbuster / lib / queries.ts View on Github external
sourceLayer: string;
  extent: number;
  bufferSize: number;
  attributes: string[];
}) => sql`
    ${filterBlock}
     tiled as
    (SELECT center,
            expansionZoom,
            theCount ${sql.raw(attributesToSelect(attributes))}
     ${sql.raw(`FROM grouped_clusters_${z}`)}
     WHERE ST_Intersects(TileBBox(${z}, ${x}, ${y}, 3857), ST_Transform(center, 3857))),
     q as
    (SELECT 1 as c1,
            ST_AsMVTGeom(ST_Transform(center, 3857), TileBBox(${z}, ${x}, ${y}, 3857), ${extent}, ${bufferSize}, false) AS geom,
            jsonb_build_object('count', theCount, 'expansionZoom', expansionZoom,'lng', ST_X (ST_Transform(center, 4326)),'lat', ST_Y (ST_Transform(center, 4326))${sql.raw(
              attributesToArray(attributes)
            )}) as attributes
     FROM tiled)
SELECT ST_AsMVT(q, ${sourceLayer}, ${extent}, 'geom') as mvt
from q
`;
github chargetrip / clusterbuster / lib / queries.ts View on Github external
theCount,
        ${sql.raw(
          `ST_ClusterDBSCAN(${geometry}, ${zoomToDistance(
            maxZoomLevel,
            radius
          )}, 1) over () AS clusters`
        )}
        ${sql.raw(attributesToSelect(attributes))}
    FROM filtered),
    ${sql.raw(`grouped_clusters_${maxZoomLevel}`)} AS
    (SELECT SUM(theCount) AS theCount,
            clusters AS clusterNo,
            ${sql.raw(`${maxZoomLevel + 1}`)} AS expansionZoom,
      ${sql.raw(attributesFirstToSelect(attributes))}
      ST_Centroid(ST_Collect(center)) AS center
    ${sql.raw(`FROM clustered_${maxZoomLevel}`)}
    GROUP BY clusters),
    ${sql.raw(additionalLevels)}
`;
github chargetrip / clusterbuster / lib / queries.ts View on Github external
FROM ${sql.raw(table)}
    WHERE ST_Intersects(TileBBox(${z}, ${x}, ${y}, 3857), ST_Transform(${sql.raw(
    geometry
  )}, 3857))
        ${sql.raw(query.length > 0 ? `AND ${query.join(' AND ')}` : '')}
    ),
    ${sql.raw(`clustered_${maxZoomLevel}`)} AS
    (SELECT ${sql.raw(geometry)} as center,
        theCount,
        ${sql.raw(
          `ST_ClusterDBSCAN(${geometry}, ${zoomToDistance(
            maxZoomLevel,
            radius
          )}, 1) over () AS clusters`
        )}
        ${sql.raw(attributesToSelect(attributes))}
    FROM filtered),
    ${sql.raw(`grouped_clusters_${maxZoomLevel}`)} AS
    (SELECT SUM(theCount) AS theCount,
            clusters AS clusterNo,
            ${sql.raw(`${maxZoomLevel + 1}`)} AS expansionZoom,
      ${sql.raw(attributesFirstToSelect(attributes))}
      ST_Centroid(ST_Collect(center)) AS center
    ${sql.raw(`FROM clustered_${maxZoomLevel}`)}
    GROUP BY clusters),
    ${sql.raw(additionalLevels)}
`;

slonik

A Node.js PostgreSQL client with strict types, detailed logging and assertions.

BSD-3-Clause
Latest version published 1 day ago

Package Health Score

83 / 100
Full package analysis