Serve vector tiles with ClusterBuster from PostGIS in the form of shapes (not only points)?

Question

Serve vector tiles with ClusterBuster from PostGIS in the form of shapes (not only points)?

florinvirdol opened this issue 4 years ago · 1 comments

Hi, guys!

As i already discussed a little with @cosmin-petrescu in my last question #58 i had a question / suggestion whether similar to Martin or Tegola servers, it's possible to serve vector tiles with ClusterBuster from PostGIS in the form of shapes (eg.: polygons, multi-polygons - not only points) and without being clustered (with or without filtering)?

I believe this would be very helpful to serve only from ClusterBuster (points and shapes) without the need to have an additional server (Martin) only for shapes.

Cosmin mentioned that they never tried to serve shapes because shapes were out of the scope.

Do you plan in the near future to investigate this?

I tried it and it seems it doesn't serve shapes to the fronted.

Don't know if it's something that i need to configure for serving shapes, but in short words, i'm receiving the following error:

SELECT ST_AsMVT(tile, 'points', 4096, 'geom') AS mvt FROM tile
{
  e: error: Argument to ST_X() must have type POINT

I did set the table to a shapes collection from PostGIS, changed the maxZoomLevel = 0 and did enable the debugging debug: true, but i have the following logs (the undefined or Buffer data comes from a console.log(result) i put inside server's success function):

WITH base_query AS (
SELECT
  geom AS center,
  1 AS size,
  0 AS clusterNo,
  1 AS expansionZoom
FROM postalarea
WHERE
        ST_Intersects(TileBBox(7, 65, 42, 3857), ST_Transform(geom, 3857))

),
tile AS (
SELECT
  ST_AsMVTGeom(ST_Transform(center, 3857), TileBBox(7, 65, 42, 3857), 4096, 256, false) AS geom,
  jsonb_build_object(
    'count', size,
    'expansionZoom', expansionZoom,
    'lng', ST_X(ST_Transform(center, 4326)),
    'lat', ST_Y(ST_Transform(center, 4326))
  ) AS attributes
FROM base_query
)
SELECT ST_AsMVT(tile, 'points', 4096, 'geom') AS mvt FROM tile
WITH base_query AS (
SELECT
  geom AS center,
  1 AS size,
  0 AS clusterNo,
  1 AS expansionZoom
FROM postalarea
WHERE
        ST_Intersects(TileBBox(7, 65, 41, 3857), ST_Transform(geom, 3857))

),
tile AS (
SELECT
  ST_AsMVTGeom(ST_Transform(center, 3857), TileBBox(7, 65, 41, 3857), 4096, 256, false) AS geom,
  jsonb_build_object(
    'count', size,
    'expansionZoom', expansionZoom,
    'lng', ST_X(ST_Transform(center, 4326)),
    'lat', ST_Y(ST_Transform(center, 4326))
  ) AS attributes
FROM base_query
)
SELECT ST_AsMVT(tile, 'points', 4096, 'geom') AS mvt FROM tile
WITH base_query AS (
SELECT
  geom AS center,
  1 AS size,
  0 AS clusterNo,
  1 AS expansionZoom
FROM postalarea
WHERE
        ST_Intersects(TileBBox(7, 64, 41, 3857), ST_Transform(geom, 3857))

),
tile AS (
SELECT
  ST_AsMVTGeom(ST_Transform(center, 3857), TileBBox(7, 64, 41, 3857), 4096, 256, false) AS geom,
  jsonb_build_object(
    'count', size,
    'expansionZoom', expansionZoom,
    'lng', ST_X(ST_Transform(center, 4326)),
    'lat', ST_Y(ST_Transform(center, 4326))
  ) AS attributes
FROM base_query
)
SELECT ST_AsMVT(tile, 'points', 4096, 'geom') AS mvt FROM tile
WITH base_query AS (
SELECT
  geom AS center,
  1 AS size,
  0 AS clusterNo,
  1 AS expansionZoom
FROM postalarea
WHERE
        ST_Intersects(TileBBox(7, 66, 42, 3857), ST_Transform(geom, 3857))

),
tile AS (
SELECT
  ST_AsMVTGeom(ST_Transform(center, 3857), TileBBox(7, 66, 42, 3857), 4096, 256, false) AS geom,
  jsonb_build_object(
    'count', size,
    'expansionZoom', expansionZoom,
    'lng', ST_X(ST_Transform(center, 4326)),
    'lat', ST_Y(ST_Transform(center, 4326))
  ) AS attributes
FROM base_query
)
SELECT ST_AsMVT(tile, 'points', 4096, 'geom') AS mvt FROM tile
WITH base_query AS (
SELECT
  geom AS center,
  1 AS size,
  0 AS clusterNo,
  1 AS expansionZoom
FROM postalarea
WHERE
        ST_Intersects(TileBBox(7, 64, 42, 3857), ST_Transform(geom, 3857))

),
tile AS (
SELECT
  ST_AsMVTGeom(ST_Transform(center, 3857), TileBBox(7, 64, 42, 3857), 4096, 256, false) AS geom,
  jsonb_build_object(
    'count', size,
    'expansionZoom', expansionZoom,
    'lng', ST_X(ST_Transform(center, 4326)),
    'lat', ST_Y(ST_Transform(center, 4326))
  ) AS attributes
FROM base_query
)
SELECT ST_AsMVT(tile, 'points', 4096, 'geom') AS mvt FROM tile
WITH base_query AS (
SELECT
  geom AS center,
  1 AS size,
  0 AS clusterNo,
  1 AS expansionZoom
FROM postalarea
WHERE
        ST_Intersects(TileBBox(7, 65, 43, 3857), ST_Transform(geom, 3857))

),
tile AS (
SELECT
  ST_AsMVTGeom(ST_Transform(center, 3857), TileBBox(7, 65, 43, 3857), 4096, 256, false) AS geom,
  jsonb_build_object(
    'count', size,
    'expansionZoom', expansionZoom,
    'lng', ST_X(ST_Transform(center, 4326)),
    'lat', ST_Y(ST_Transform(center, 4326))
  ) AS attributes
FROM base_query
)
SELECT ST_AsMVT(tile, 'points', 4096, 'geom') AS mvt FROM tile
query969a5bdb-376c-46fa-b733-7b694e32945d: 1218.621ms
gzip969a5bdb-376c-46fa-b733-7b694e32945d: 3.558ms
<Buffer 1f 8b 08 00 00 00 00 00 00 0a 03 00 00 00 00 00 00 00 00 00>
WITH base_query AS (
SELECT
  geom AS center,
  1 AS size,
  0 AS clusterNo,
  1 AS expansionZoom
FROM postalarea
WHERE
        ST_Intersects(TileBBox(7, 66, 41, 3857), ST_Transform(geom, 3857))

),
tile AS (
SELECT
  ST_AsMVTGeom(ST_Transform(center, 3857), TileBBox(7, 66, 41, 3857), 4096, 256, false) AS geom,
  jsonb_build_object(
    'count', size,
    'expansionZoom', expansionZoom,
    'lng', ST_X(ST_Transform(center, 4326)),
    'lat', ST_Y(ST_Transform(center, 4326))
  ) AS attributes
FROM base_query
)
SELECT ST_AsMVT(tile, 'points', 4096, 'geom') AS mvt FROM tile
query82a04b6b-a05c-4351-bd50-9a47e76a7d58: 410.535ms
gzip82a04b6b-a05c-4351-bd50-9a47e76a7d58: 0.615ms
<Buffer 1f 8b 08 00 00 00 00 00 00 0a 03 00 00 00 00 00 00 00 00 00>
WITH base_query AS (
SELECT
  geom AS center,
  1 AS size,
  0 AS clusterNo,
  1 AS expansionZoom
FROM postalarea
WHERE
        ST_Intersects(TileBBox(7, 64, 43, 3857), ST_Transform(geom, 3857))

),
tile AS (
SELECT
  ST_AsMVTGeom(ST_Transform(center, 3857), TileBBox(7, 64, 43, 3857), 4096, 256, false) AS geom,
  jsonb_build_object(
    'count', size,
    'expansionZoom', expansionZoom,
    'lng', ST_X(ST_Transform(center, 4326)),
    'lat', ST_Y(ST_Transform(center, 4326))
  ) AS attributes
FROM base_query
)
SELECT ST_AsMVT(tile, 'points', 4096, 'geom') AS mvt FROM tile
WITH base_query AS (
SELECT
  geom AS center,
  1 AS size,
  0 AS clusterNo,
  1 AS expansionZoom
FROM postalarea
WHERE
        ST_Intersects(TileBBox(7, 64, 43, 3857), ST_Transform(geom, 3857))

),
tile AS (
SELECT
  ST_AsMVTGeom(ST_Transform(center, 3857), TileBBox(7, 64, 43, 3857), 4096, 256, false) AS geom,
  jsonb_build_object(
    'count', size,
    'expansionZoom', expansionZoom,
    'lng', ST_X(ST_Transform(center, 4326)),
    'lat', ST_Y(ST_Transform(center, 4326))
  ) AS attributes
FROM base_query
)
SELECT ST_AsMVT(tile, 'points', 4096, 'geom') AS mvt FROM tile
{
  e: error: Argument to ST_X() must have type POINT
      at Connection.parseE (C:\zFVStuff\Dentsu\Google Maps - Deck.gl\_Servers\ClusterBuster\clusterbuster-master\node_modules\pg\lib\connection.js:614:13)
      at Connection.parseMessage (C:\zFVStuff\Dentsu\Google Maps - Deck.gl\_Servers\ClusterBuster\clusterbuster-master\node_modules\pg\lib\connection.js:413:19)
      at Socket.<anonymous> (C:\zFVStuff\Dentsu\Google Maps - Deck.gl\_Servers\ClusterBuster\clusterbuster-master\node_modules\pg\lib\connection.js:129:22)
      at Socket.emit (events.js:311:20)
      at Socket.EventEmitter.emit (domain.js:482:12)
      at addChunk (_stream_readable.js:294:12)
      at readableAddChunk (_stream_readable.js:275:11)
      at Socket.Readable.push (_stream_readable.js:209:10)
      at TCP.onStreamRead (internal/stream_base_commons.js:186:23) {
    name: 'error',
    length: 95,
    severity: 'ERROR',
    code: 'XX000',
    detail: undefined,
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'lwgeom_pg.c',
    line: '243',
    routine: 'pg_error'
  }
}
undefined
WITH base_query AS (
SELECT
  geom AS center,
  1 AS size,
  0 AS clusterNo,
  1 AS expansionZoom
FROM postalarea
WHERE
        ST_Intersects(TileBBox(7, 66, 43, 3857), ST_Transform(geom, 3857))

),
tile AS (
SELECT
  ST_AsMVTGeom(ST_Transform(center, 3857), TileBBox(7, 66, 43, 3857), 4096, 256, false) AS geom,
  jsonb_build_object(
    'count', size,
    'expansionZoom', expansionZoom,
    'lng', ST_X(ST_Transform(center, 4326)),
    'lat', ST_Y(ST_Transform(center, 4326))
  ) AS attributes
FROM base_query
)
SELECT ST_AsMVT(tile, 'points', 4096, 'geom') AS mvt FROM tile
query5771eff7-0240-4f05-af55-4c7b6c05fd93: 2046.227ms
gzip5771eff7-0240-4f05-af55-4c7b6c05fd93: 2.083ms
<Buffer 1f 8b 08 00 00 00 00 00 00 0a 03 00 00 00 00 00 00 00 00 00>
WITH base_query AS (
SELECT
  geom AS center,
  1 AS size,
  0 AS clusterNo,
  1 AS expansionZoom
FROM postalarea
WHERE
        ST_Intersects(TileBBox(7, 64, 41, 3857), ST_Transform(geom, 3857))

),
tile AS (
SELECT
  ST_AsMVTGeom(ST_Transform(center, 3857), TileBBox(7, 64, 41, 3857), 4096, 256, false) AS geom,
  jsonb_build_object(
    'count', size,
    'expansionZoom', expansionZoom,
    'lng', ST_X(ST_Transform(center, 4326)),
    'lat', ST_Y(ST_Transform(center, 4326))
  ) AS attributes
FROM base_query
)
SELECT ST_AsMVT(tile, 'points', 4096, 'geom') AS mvt FROM tile
{
  e: error: Argument to ST_X() must have type POINT
      at Connection.parseE (C:\zFVStuff\Dentsu\Google Maps - Deck.gl\_Servers\ClusterBuster\clusterbuster-master\node_modules\pg\lib\connection.js:614:13)
      at Connection.parseMessage (C:\zFVStuff\Dentsu\Google Maps - Deck.gl\_Servers\ClusterBuster\clusterbuster-master\node_modules\pg\lib\connection.js:413:19)
      at Socket.<anonymous> (C:\zFVStuff\Dentsu\Google Maps - Deck.gl\_Servers\ClusterBuster\clusterbuster-master\node_modules\pg\lib\connection.js:129:22)
      at Socket.emit (events.js:311:20)
      at Socket.EventEmitter.emit (domain.js:482:12)
      at addChunk (_stream_readable.js:294:12)
      at readableAddChunk (_stream_readable.js:275:11)
      at Socket.Readable.push (_stream_readable.js:209:10)
      at TCP.onStreamRead (internal/stream_base_commons.js:186:23) {
    name: 'error',
    length: 95,
    severity: 'ERROR',
    code: 'XX000',
    detail: undefined,
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'lwgeom_pg.c',
    line: '243',
    routine: 'pg_error'
  }
}
undefined
queryf4a40470-0c5d-45ce-8615-ab90806fa57b: 3137.602ms
gzipf4a40470-0c5d-45ce-8615-ab90806fa57b: 1.012ms
<Buffer 1f 8b 08 00 00 00 00 00 00 0a 03 00 00 00 00 00 00 00 00 00>
WITH base_query AS (
SELECT
  geom AS center,
  1 AS size,
  0 AS clusterNo,
  1 AS expansionZoom
FROM postalarea
WHERE
        ST_Intersects(TileBBox(7, 64, 42, 3857), ST_Transform(geom, 3857))

),
tile AS (
SELECT
  ST_AsMVTGeom(ST_Transform(center, 3857), TileBBox(7, 64, 42, 3857), 4096, 256, false) AS geom,
  jsonb_build_object(
    'count', size,
    'expansionZoom', expansionZoom,
    'lng', ST_X(ST_Transform(center, 4326)),
    'lat', ST_Y(ST_Transform(center, 4326))
  ) AS attributes
FROM base_query
)
SELECT ST_AsMVT(tile, 'points', 4096, 'geom') AS mvt FROM tile
{
  e: error: Argument to ST_X() must have type POINT
      at Connection.parseE (C:\zFVStuff\Dentsu\Google Maps - Deck.gl\_Servers\ClusterBuster\clusterbuster-master\node_modules\pg\lib\connection.js:614:13)
      at Connection.parseMessage (C:\zFVStuff\Dentsu\Google Maps - Deck.gl\_Servers\ClusterBuster\clusterbuster-master\node_modules\pg\lib\connection.js:413:19)
      at Socket.<anonymous> (C:\zFVStuff\Dentsu\Google Maps - Deck.gl\_Servers\ClusterBuster\clusterbuster-master\node_modules\pg\lib\connection.js:129:22)
      at Socket.emit (events.js:311:20)
      at Socket.EventEmitter.emit (domain.js:482:12)
      at addChunk (_stream_readable.js:294:12)
      at readableAddChunk (_stream_readable.js:275:11)
      at Socket.Readable.push (_stream_readable.js:209:10)
      at TCP.onStreamRead (internal/stream_base_commons.js:186:23) {
    name: 'error',
    length: 95,
    severity: 'ERROR',
    code: 'XX000',
    detail: undefined,
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'lwgeom_pg.c',
    line: '243',
    routine: 'pg_error'
  }
}
undefined
queryc48b6381-b2b0-466b-a860-ca8b4f223b23: 4231.551ms
gzipc48b6381-b2b0-466b-a860-ca8b4f223b23: 0.668ms
<Buffer 1f 8b 08 00 00 00 00 00 00 0a 03 00 00 00 00 00 00 00 00 00>
query8a0121e5-3c00-430e-970b-b8931154b011: 3059.252ms
gzip8a0121e5-3c00-430e-970b-b8931154b011: 1.194ms
<Buffer 1f 8b 08 00 00 00 00 00 00 0a 03 00 00 00 00 00 00 00 00 00>

Answer 1 · 2022-04-15T11:58:18.000Z

@florinvirdol at a minimum, you'd need to fork the repo, and change the query that generates the tile to either not return a lat/lng, or get the centroid of your shape and use that for the lat/lng, since non point geometries won't return ST_X/ST_Y coords-

https://github.com/chargetrip/clusterbuster/blob/master/lib/queries/tile.ts