ST_AsPNG query has wrong position on Mapbox

Question

I am trying to import GeoTIFF files into a PostgreSQL database using raster2pgsql.

I use the following query to import the data into my database:

raster2pgsql -s 4326 -c -P -I -C -t 256x256 *.tif public.my_raster_table > out.sql

The import seems ok. Then, to work with Mapbox with a z/x/y format, I build a request.

For example, for 18/133274/88180 (Lille, France) I create the following request

SELECT ST_AsPNG(ST_Union(rast)) As new_rast FROM my_raster_table WHERE ST_Intersects(rast, ST_Transform(ST_Segmentize(ST_MakeEnvelope( 336628.6725679152, 6556921.160358975, 336781.5466244854, 6557074.034415545, 3857), 38.21851414255798), 4326))

The data is retreived, but my raster file has a wrong position, and it is repeated multiple times (I try to replace a road).

I know my ST_Intersects is right as it works well for vector tiles.

Where did I go wrong ?

Answer 1

It is repeated multiple times because the tile size in the raster from raster2pgsql does not match the size of ST_MakeEnvelope. You will need to also ST_Clip the ST_Union against the envelope. This will unfortunately leave you with some blank edges since ST_Clip does not seem to handle raster cells that are on the border of the area. I ended up using ST_DumpAsPolygons to get a clean edge instead of cutting the rasters with clip.

If the position errors is not only form the missing clip, I would also assume that the raster and the render have different srid. Mapbox expects srid 3857.