negative tile coordinates using --extent

[musicformellons]

I am updating my cache after a new point has been added with this command:

exec(`RUST_LOG=trace t_rex generate --extent=${x},${y},${x},${y} --minzoom=${min} --maxzoom=${max} --overwrite=true --config /home/usr/osm.toml`)

For my layers 7 to 21 this works fine, i.e. the point is updated. However at layers < 7 the 'tile bound coordinates' become negative which causes trouble and tiles are not properly updated.

So this is how successful logs look like for layer 7:

2019-03-27 10:07:07.576 DEBUG wgs84 extent: Some(Extent { minx: 4.295980701608052, miny: 52.09069818826458, maxx: 4.295980701608052, maxy: 52.09069818826458 })
2019-03-27 10:07:07.576 DEBUG tile limits: Extent { minx: 478226.3841607385, miny: 6816541.493019397, maxx: 478226.3841607385, maxy: 6816541.493019397 }
2019-03-27 10:07:07.576 DEBUG level 7: ExtentInt { minx: 65, miny: 85, maxx: 66, maxy: 86 }
2019-03-27 10:07:07.576 DEBUG points/7/65/85 retrieving with Extent { minx: 313086.0678560771, miny: 6574807.424977716, maxx: 626172.1357121617, maxy: 6887893.492833801 }
2019-03-27 10:07:07.576 DEBUG executing query: 
2019-03-27 10:07:07.577 DEBUG preparing query with name `s4`: SELECT ST_Transform(wkb_geometry,3857) AS wkb_geometry,"id","point_name","url","front_image","city","created_at","updated_at" FROM (SELECT wkb_geometry,"id","point_name","url","front_image","city","created_at"::TEXT,"updated_at"::TEXT FROM points WHERE wkb_geometry && ST_Shift_Longitude(ST_Transform(ST_MakeEnvelope($1,$2,$3,$4,3857),4326))) AS _q
2019-03-27 10:07:07.578 DEBUG executing query: BEGIN
2019-03-27 10:07:07.578 DEBUG executing statement s4 with parameters: [313086.0678560771, 6574807.424977716, 626172.1357121617, 6887893.492833801]
2019-03-27 10:07:07.586 INFO WARNING: ST_Shift_Longitude signature was deprecated in 2.2.0. Please use ST_ShiftLongitude
2019-03-27 10:07:07.587 DEBUG Reading features in layer points
2019-03-27 10:07:07.587 DEBUG executing query: ROLLBACK
2019-03-27 10:07:07.588 DEBUG points/7/65/85 layer points: 1 features
2019-03-27 10:07:07.588 DEBUG Filecache.write /home/usr/mvtcache/points/7/65/42.pbf

And this when it returns the faulty '0 features' at layer 6 (similar output for layer 1-5):

2019-03-27 10:07:07.777 DEBUG points/6/32/42 retrieving with Extent { minx: -0.000000003725290298461914, miny: 6261721.357121635, maxx: 626172.1357121617, maxy: 6887893.492833801 }
2019-03-27 10:07:07.777 DEBUG executing query: 
2019-03-27 10:07:07.777 DEBUG executing query: BEGIN
2019-03-27 10:07:07.778 DEBUG executing statement s4 with parameters: [-0.000000003725290298461914, 6261721.357121635, 626172.1357121617, 6887893.492833801]
2019-03-27 10:07:07.781 INFO WARNING: ST_Shift_Longitude signature was deprecated in 2.2.0. Please use ST_ShiftLongitude
2019-03-27 10:07:07.781 DEBUG Reading features in layer points
2019-03-27 10:07:07.782 DEBUG executing query: ROLLBACK
2019-03-27 10:07:07.782 DEBUG points/6/32/42 layer points: 0 features

What is causing this? Note the value minx: -0.000000003725290298461914 ; Seems to me the tile bound coordinates should have a minimum of 0...?!

[musicformellons]

Please let me know if you need more info to debug this.

[pka]

Could you try to execute the query manually to find out wheter a non-negative value solves the problem? The executed query should be

SELECT ST_Transform(wkb_geometry,3857) AS wkb_geometry,"id","point_name","url","front_image","city","created_at","updated_at" FROM
 (SELECT wkb_geometry,"id","point_name","url","front_image","city","created_at"::TEXT,"updated_at"::TEXT 
   FROM points
   WHERE wkb_geometry && ST_Shift_Longitude(ST_Transform(ST_MakeEnvelope(-0.000000003725290298461914, 6261721.357121635, 626172.1357121617, 6887893.492833801,3857),4326))
) AS _q

[pka]

My experiments show a different bbox with a non-negative number in combination with ST_Shift_Longitude:
SELECT ST_AsEWKT(ST_Shift_Longitude(ST_Transform(ST_MakeEnvelope(-0.000000003725290298461914, 6261721.357121635, 626172.1357121617, 6887893.492833801,3857),4326)))
->
SRID=4326;POLYGON((360 48.9224992637582,360 52.4827802220782,5.62499999999998 52.4827802220782,5.62499999999998 48.9224992637582,360 48.9224992637582))

compared to

SELECT ST_AsEWKT(ST_Shift_Longitude(ST_Transform(ST_MakeEnvelope(0, 6261721.357121635, 626172.1357121617, 6887893.492833801,3857),4326)))
->
SRID=4326;POLYGON((0 48.9224992637582,0 52.4827802220782,5.62499999999998 52.4827802220782,5.62499999999998 48.9224992637582,0 48.9224992637582))

Maybe ST_Shift_Longitude should be optional? Could you also try without it?

[musicformellons]

Ahum, my knowledge of both sql and geographic projection etcetera is very limited. I tried running this query straight on my postgres database:

postgres=> SELECT ST_Transform(wkb_geometry,3857) AS wkb_geometry,"id","point_name","url","front_image","city","created_at","updated_at" FROM (SELECT wkb_geometry,"id","point_name","url","front_image","city","created_at"::TEXT,"updated_at"::TEXT FROM points WHERE wkb_geometry && ST_Shift_Longitude(ST_Transform(ST_MakeEnvelope(0, 6261721.357121635, 626172.1357121617, 6887893.492833801,3857),4326))) AS _q

it just returns a prompt. When I check in my map if the layers 1-6 got updated they did not it seems (I did clean my browser cache).

[musicformellons]

As a side remark: As I remember things I used to need to run the --extent call on every layer to get them updated after adding a point. However, currently, when I do not run my updating procedure I (e.g.) notice the layers 21-11 and 9-7 do get updated. So apparently the cache updating has improved in more recent versions of t-rex?! Or is this 'something else' (e.g. mapbox-gl-js which I use)?

[pka]

Can you run the following queries on a psql prompt and check how many records you get (you probably had a missing semicolon)?

1. Zoom level 7

SELECT ST_Transform(wkb_geometry,3857) AS wkb_geometry,"id","point_name","url","front_image","city","created_at","updated_at" FROM
 (SELECT wkb_geometry,"id","point_name","url","front_image","city","created_at"::TEXT,"updated_at"::TEXT 
   FROM points
   WHERE wkb_geometry && ST_Shift_Longitude(ST_Transform(ST_MakeEnvelope(313086.0678560771, 6574807.424977716, 626172.1357121617, 6887893.492833801,3857),4326))
) AS _q;

2. Zoom level 6

SELECT ST_Transform(wkb_geometry,3857) AS wkb_geometry,"id","point_name","url","front_image","city","created_at","updated_at" FROM
 (SELECT wkb_geometry,"id","point_name","url","front_image","city","created_at"::TEXT,"updated_at"::TEXT 
   FROM points
   WHERE wkb_geometry && ST_Shift_Longitude(ST_Transform(ST_MakeEnvelope(-0.000000003725290298461914, 6261721.357121635, 626172.1357121617, 6887893.492833801,3857),4326))
) AS _q;

3. Zoom level 6 with positive value

SELECT ST_Transform(wkb_geometry,3857) AS wkb_geometry,"id","point_name","url","front_image","city","created_at","updated_at" FROM
 (SELECT wkb_geometry,"id","point_name","url","front_image","city","created_at"::TEXT,"updated_at"::TEXT 
   FROM points
   WHERE wkb_geometry && ST_Shift_Longitude(ST_Transform(ST_MakeEnvelope(0, 6261721.357121635, 626172.1357121617, 6887893.492833801,3857),4326))
) AS _q;

4. Zoom level 6 without ST_Shift_Longitude

SELECT ST_Transform(wkb_geometry,3857) AS wkb_geometry,"id","point_name","url","front_image","city","created_at","updated_at" FROM
 (SELECT wkb_geometry,"id","point_name","url","front_image","city","created_at"::TEXT,"updated_at"::TEXT 
   FROM points
   WHERE wkb_geometry && ST_Transform(ST_MakeEnvelope(-0.000000003725290298461914, 6261721.357121635, 626172.1357121617, 6887893.492833801,3857),4326)
) AS _q;

[musicformellons]

Aaargh, the semicolon, of course. So output:
#1, #3 and #4 return one point; #2 returns nothing.

[pka]

Thanks! So either avoiding negative coordinates or omitting ST_Shift_Longitude solves your problem. I'll have to check again with #98.

[musicformellons]

Any idea when this will be solved?

[pka]

Changing the grid resolutions to calculated values based on z0, results in a tile border of (0.0, -6261721.357121639) instead of (-0.000000003725290298461914, 6261721.357121635) for tile 6/32/42.
So this should solve your problem. I'll probably cut a minor release soon.

[musicformellons]

Yeah, solved in 0.9.2 ! Thx!