1

This is a update 1 thus far on the advice in the comment:

It looks like I need ST_ConvexHull to create the overall bounds of all of the geometries. It's tested against the individual geometries using ST_Intersect when the vector tiles are created. This doesn't seem to work, but thus far

WITH bounds AS
(
    -- Here the the generated zoom, x and y are needed... Somehow!
    -- Or maybe this doesn't work like this since this needs to know
    -- the total are abounds generated from all the geometries first!
    -- Maybe generate the paremeters or union the results of many
    -- queries (check that this is OK first)!
    -- Though the linked https://gis.stackexchange.com/questions/360197/using-st-asmvt-with-st-tileenvelope-clipping-in-mapbox
    -- probably holds a cueue to improve this idea!
    SELECT ST_TileEnvelope(0, 0, 0) AS geom
),
vectorGeometry AS
(
    SELECT   
        ST_AsMvtGeom(ST_Transform(rel.geom, 3857), bounds.geom) AS geom
    FROM
        relations rel, bounds
    WHERE ST_Intersects(rel.geom, ST_Transform(bounds.geom, 3857))
)
SELECT ST_AsMvt(vectorGeometry.*) AS VectorTiles
FROM vectorGeometry;

Here is maybe one way to generate the zoom, x and y combinations...

SELECT
    Z.x AS zoom,
    X.x AS X,
    Y.x AS Y
FROM
(
    SELECT(deca * 10 + unit + 1) AS x
    FROM
    (
        SELECT 0 AS deca
        UNION ALL SELECT 1        
    ) AS da,
    (
        SELECT 0 AS unit
        UNION ALL SELECT 1
        UNION ALL SELECT 2        
    ) AS u
) AS Z CROSS JOIN
(
    SELECT(deca * 10 + unit + 1) AS x
    FROM    
    (
        SELECT 0 AS deca
        UNION ALL SELECT 1        
    ) AS da,
    (
        SELECT 0 AS unit
        UNION ALL SELECT 1      
        UNION ALL SELECT 2        
    ) AS u
) AS X CROSS JOIN
(
    SELECT(deca * 10 + unit + 1) AS x
    FROM    
    (
        SELECT 0 AS deca
        UNION ALL SELECT 1        
    ) AS da,
    (
        SELECT 0 AS unit
        UNION ALL SELECT 1      
        UNION ALL SELECT 2        
    ) AS u
) AS Y;

But the queries would need to be joined, maybe the vector generating query is not OK either, yet...

I downloaded https://download.geofabrik.de/europe/liechtenstein.html and processed the data to tables nodes, relations and ways each having a preprocessed geom column in projection 3857 (see one example at the end).

I have tried to find a solution in PostGIS to generate vector tiles from tables like this using the new ST_TileEnvelope and the older workhorses ST_MakeEnvelope, ST_AsMVT and ST_AsMVTGeom, but it appears I'm unable to come up with a query to generate the set of x, y and z coordinates one see in documentation from the data already in the tables. I think it is possible.

Would anyone have ideas and hints how to generate vector tiles for all the data in table?

I think alternative would be to have an idea how to get the bounds from the OSM data in code. I'm using NetTopologySuite (which should be fairly compatible with JTS) and I have seen the notes at Slippy Map Tiles.

I saw Using ST_AsMVT with ST_TileEnvelope clipping in mapbox and the answer by Paul Ramsey and it gives me hope this is doable!

As for an example, a relevant query example for data is

So for SELECT id, ST_SRID(geom), ST_AsText(geom) FROM relations WHERE id = 3963012; I have for instance for instance

3963012, 3857, POLYGON((1063022.52325136 5979410.02389074,1063024.25983542 5979358.41980986,1063024.84982872 5979347.01056269,1063031.00579656 5979328.86398924,1063043.96338529 5979329.51969132,1063054.65005641 5979318.17605224,1063055.34023725 5979309.68472516,1063054.65005641 5979292.25949885,1063063.72259491 5979278.32589995,1063075.71170407 5979278.32589995,1063080.29806709 5979263.65466408,1063065.02503295 5979244.95092332,1063047.61466459 5979234.21391242,1063030.02618504 5979237.82023521,1063023.09098077 5979254.76997239,1063048.7278595 5979265.72011031,1063037.56251457 5979270.01492866,1063023.12437661 5979298.02965459,1063024.51587025 5979307.73401605,1063025.99641948 5979318.12687464,1063010.99055212 5979327.92961385,1062975.52416235 5979315.99584584,1062968.3440552 5979320.29068903,1062981.32390782 5979331.58515322,1063010.35603102 5979340.89613037,1062994.3148924 5979391.82800767,1063005.7474041 5979420.7611189,1062993.6358435 5979483.98808878,1063004.45609801 5979497.34826705,1062985.23122195 5979534.77326841,1063028.27846904 5979584.21451605,1063041.78152327 5979620.19729856,1063082.62464444 5979684.39291981,1063058.74661367 5979722.60560495,1063026.78678786 5979751.67096448,1063001.41707591 5979784.72001933,1062988.2368482 5979780.17904552,1062966.5295475 5979782.76920358,1062961.34205922 5979792.81836842,1062982.30351934 5979885.03188376,1062973.00834186 5979894.2286987,1062953.1489447 5979899.42547722,1062933.8016172 5979893.24508166,1062919.22989586 5979874.45801766,1062909.17774584 5979859.55626542,1062900.43916581 5979841.73649183,1062897.84542168 5979823.58888509,1062887.47044513 5979832.98235702,1062895.57450406 5979851.45785412,1062923.11494609 5979899.08121104,1062932.51031111 5979915.60600312,1062933.15596416 5979928.557003,1062931.04089383 5979942.75394405,1062908.85491932 5979930.50785149,1062893.62641297 5979895.52379463,1062873.22155031 5979883.85154459,1062858.3158705 5979859.88413648,1062850.54577004 5979848.21193431,1062843.7330172 5979847.57258661,1062845.68110829 5979861.50709841,1062840.98342578 5979864.73662964,1062838.23383436 5979871.86783227,1062845.51412905 5979874.45801766,1062848.92050547 5979881.26135639,1062870.30497965 5979905.24521711,1062877.75225359 5979924.36022118,1062878.40903858 5979947.3605777,1062855.72212636 5979951.08195258,1062838.99080689 5979961.47558075,1062806.79721016 5979974.73813063,1062798.70428318 5979983.80389023,1062793.72830194 5980002.26331423,1062796.06601124 5980028.67380166,1062799.99558927 5980055.41224783,1062810.23698242 5980076.78994883,1062831.45447737 5980109.25005553,1062852.48272918 5980129.92294428,1062891.53360655 5980152.56317466,1062926.5213225 5980172.59679485,1062967.28652003 5980176.41663111,1063003.88836861 5980178.00686387,1063034.85745094 5980166.89163481,1063071.1142091 5980176.9576381,1063094.0237603 5980183.31857128,1063120.85175758 5980190.77791895,1063191.72887737 5980202.23745675,1063246.30882371 5980240.07535129,1063293.76432263 5980269.86379235,1063298.80709556 5980279.56923935,1063340.60756436 5980324.5554393,1063455.25550793 5980367.54174159,1063491.40094659 5980392.41225083,1063560.91996859 5980387.87095939,1063585.81100673 5980368.01718243,1063595.99674014 5980320.99784868,1063600.85026993 5980297.93092548,1063589.60700136 5980282.09396889,1063558.42641199 5980241.32131596,1063522.94889028 5980197.99136015,1063540.39265448 5980191.59762788,1063570.63816013 5980211.9428282,1063602.62024984 5980259.63373834,1063616.49065839 5980256.33848312,1063627.18846146 5980241.12458469,1063638.49852172 5980208.43446632,1063627.05487807 5980168.90811336,1063622.16795242 5980115.33224053,1063594.90580913 5980097.05291031,1063561.45430214 5980095.85614832,1063548.77501214 5980081.85568477,1063559.10546089 5980067.29784964,1063580.46767117 5980073.9373998,1063599.49217215 5980063.90430374,1063564.09257407 5980048.05137982,1063570.69381988 5980014.77180944,1063572.54172342 5979983.60716478,1063569.91458344 5979971.03313938,1063561.82165646 5979953.70494906,1063536.09572214 5979951.01637768,1063499.17104704 5979955.93449668,1063460.04224603 5980009.24708522,1063488.69588296 5980042.69057342,1063511.30487154 5980061.1337286,1063515.15652592 5980092.2002874,1063506.76303632 5980102.3317802,1063484.39895061 5980072.39636795,1063466.24274167 5980052.0514944,1063441.86377318 5980078.95395217,1063415.45878997 5980053.9040071,1063426.59073905 5980042.69057342,1063364.48559513 5979966.24616187,1063329.37542774 5979920.78640075,1063305.19683434 5979919.80278071,1063300.7663186 5979925.85204573,1063326.34753759 5979972.63972831,1063297.60484506 5980014.01769257,1063234.77612446 5980010.52580446,1063211.62167038 5980009.24708522,1063191.63982178 5979954.3115171,1063169.81006963 5979945.98350546,1063161.48337172 5979909.06493583,1063107.93869665 5979792.32656563,1063104.77722311 5979742.65462709,1063137.58307705 5979722.44167201,1063121.853633 5979688.2453279,1063109.33019029 5979632.63967124,1063143.31603082 5979613.6072744,1063177.95865636 5979553.11698761,1063195.99241387 5979596.90273306,1063220.64968108 5979620.19729856,1063239.19550824 5979631.77083568,1063239.81889739 5979652.47537468,1063269.28516661 5979673.21274942,1063271.09967431 5979691.49118795,1063281.19635212 5979708.63852937,1063325.06736344 5979717.63843818,1063342.39980816 5979741.94971384,1063385.34686771 5979739.83497441,1063391.16887708 5979756.73651086,1063370.08496552 5979782.09707388,1063389.15399429 5979811.22820627,1063399.23954016 5979840.14632062,1063383.09821399 5979844.85126103,1063379.23542766 5979880.31052801,1063387.47306998 5979910.7698752,1063406.29719587 5979909.42559605,1063421.81513289 5979751.27752411,1063417.98574241 5979691.24528942,1063439.2588971 5979660.55720733,1063413.2212682 5979630.57413778,1063423.45152941 5979613.80399146,1063467.84574233 5979620.86941573,1063493.48262106 5979655.72122137,1063507.80943953 5979732.16290082,1063472.29852197 5979780.34297955,1063478.61033709 5979792.39213933,1063499.53840136 5979789.42492973,1063505.60531361 5979779.09708099,1063533.76914478 5979731.17930214,1063537.79891035 5979672.3275166,1063546.8825808 5979666.95054882,1063542.50772481 5979647.44267539,1063529.20504566 5979650.08197335,1063525.35339128 5979640.37722951,1063556.96812666 5979615.82034166,1063537.1309934 5979601.01739138,1063518.62969403 5979598.67318342,1063517.36065184 5979574.50984578,1063480.62521988 5979532.41268537,1063487.68287559 5979505.88895619,1063464.81785218 5979455.0711598,1063425.73357897 5979411.3025218,1063411.11732983 5979394.94261559,1063360.18866279 5979437.18664475,1063344.75978136 5979428.82634328,1063336.49987515 5979404.3520168,1063292.82923891 5979349.97762122,1063265.38898443 5979324.56914181,1063233.49595032 5979309.86504283,1063245.06204541 5979392.97549467,1063208.8832109 5979383.0907188,1063168.61895108 5979402.53242796,1063145.9876986 5979383.76281763,1063130.40296989 5979402.53242796,1063090.84002286 5979434.20316369,1063061.57412873 5979472.44753415,1063051.82254134 5979468.51325767,1063061.57412873 5979441.94054525,1063047.83730357 5979416.8104731,1063027.61055209 5979429.17059083,1063022.52325136 5979410.02389074),(1063569.87005565 5980200.4504892,1063565.71783864 5980194.40103299,1063550.35574891 5980172.00660571,1063546.18126801 5980175.10509916,1063524.15114078 5980150.67785272,1063512.72976102 5980137.46421649,1063514.11012271 5980135.92317337,1063522.0472024 5980131.52956259,1063537.04193781 5980118.72580652,1063562.02203154 5980106.90571043,1063585.84440257 5980108.77462885,1063609.76696115 5980117.61101175,1063604.37909779 5980133.20175748,1063602.95420831 5980138.39867894,1063601.67403417 5980143.0545986,1063597.06540725 5980159.87494886,1063593.53657939 5980172.74434214,1063591.17660618 5980181.36766602,1063588.48267451 5980191.1877734,1063585.73308308 5980201.20462225,1063572.8756819 5980200.59803697,1063569.87005565 5980200.4504892))

3
  • 1
    Precompute a polygon that covers the table's contents. Write a recursive CTE that starts with (0,0,0) and generates the four tiles on the next level (2×X+0/1, 2×Y+0/1, Z+1), and checks if each such tile overlaps the polygon. – CL. Feb 15 at 15:50
  • Good idea! I work/labor on this over the next days (this is nighttime hobby issue). – Veksi Feb 15 at 17:22
  • @CL.Can you elabore on that four tiles generating idea? I came up with one way of generating tile combinations and was thinking to filter some out, but now it doesn't feel so smart idea. Feels excessive, I also don't know how could I plug it into a query that generates the tiles. I worked one tile generating query, it's not ready and maybe don't work, but I think I see how something close that could work if called from code. Would you agree? – Veksi Feb 16 at 20:46
1

This precompute a polygon that covers the table's contents. Then, in the recursive CTE, starts with the (0,0,0) tile and generates the four tiles on the next level, and checks if each such tile overlaps the polygon:

WITH RECURSIVE b(bounds) AS (
  -- select the boundary object, or compute it
),
add(x, y) AS (
  VALUES (0, 0),
         (0, 1),
         (1, 0),
         (1, 1)
),
coords(x, y, z) AS (
  SELECT 0, 0, 0

  UNION ALL

  SELECT 2 * coords.x + add.x,
         2 * coords.y + add.y,
         z + 1
  FROM coords
  CROSS JOIN add
  WHERE z < 10
    AND ST_Intersects(ST_TileEnvelope(z + 1, 2 * coords.x + add.x, 2 * coords.y + add.y, ...),
                      (SELECT bounds FROM b))
)
SELECT z, x, y,
       (SELECT ST_AsMVT(tile)
        FROM (SELECT ST_AsMVTGeom(geom, ST_TileEnvelope(z, x, y, ...))
              FROM geometries
              WHERE geom && ST_TileEnvelope(z, x, y, ...)
             ) AS tile
       ) AS mvt
FROM coords

(Not all objects should be visible in all zoom levels, but that is a different question.)

2
  • I'll read this a bit and likely accept (trying to understand before accepting is probably good habits :)). It looks like something like is the way to go. – Veksi Feb 18 at 18:12
  • This is a very clever query, some skills behind! It took a while to got through this, sorry for the delay. To someone else coming here, the top CTE could have something like SELECT ST_Union(geom) AS commonGeom FROM nodes to have the joint boundary. This query needs to return one row only. – Veksi Feb 19 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.