By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select lon||'{'
||to_char(abs(floor(ST_XMin(inter))),'fm000')
||'...'
||to_char(abs(floor(ST_XMax(inter))),'fm000')
||'}'||
lat||'{'
||to_char(abs(ceil(ST_YMin(inter))),'fm00')
||'...'
||to_char(abs(ceil(ST_YMax(inter))),'fm00')
||'}'
from
(SELECT st_intersection(ST_Envelope(ST_LineFromText('LINESTRING(-6.16754 -43.4077, 7.60639 44.4763)', 4326)), quadrant) inter, lon, lat
from (select ST_Envelope(ST_GeomFromText('LINESTRING(0 0, '||lon_v||' '||lat_v||')', 4326)) quadrant, lon, lat
from (select 180 lon_v, 'W' lon union select -180, 'E') long
, (select 180 lat_v, 'N' lat union select -180, 'S') lat ) quadrant
) quadis;
| ?column? |
|---|
| E{007...000}S{43...00} |
| E{007...000}N{00...45} |
| W{000...007}S{43...00} |
| W{000...007}N{00...45} |