Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
florinvirdol opened this issue Sep 25, 2020 · 1 comment
Open

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

florinvirdol opened this issue Sep 25, 2020 · 1 comment

Comments

@florinvirdol
Copy link

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>
@florinvirdol florinvirdol changed the title Question: Serve vector tiles with ClusterBuster from PostGIS in the form of shapes (not only points)? Serve vector tiles with ClusterBuster from PostGIS in the form of shapes (not only points)? Sep 25, 2020
@chriszrc
Copy link
Contributor

@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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@florinvirdol @chriszrc