Closed gdey closed 4 years ago
SQL to get adjoing sheets for an mgd_id:
select mdg_id, wkb_geometry
from public.tlm_50_index
WHERE mdg_id != 'M758G39354' and wkb_geometry && (
SELECT ST_Expand(ST_Extent(wkb_geometry),0.01) AS expanded
FROM public.tlm_50_index
where mdg_id like 'M758G39354'
)
SQL to get the bounds for the 9-sheets:
select ST_SetSRID(ST_Extent(wkb_geometry),4326) as bounds
from public.tlm_50_index
WHERE mdg_id != 'M758G39354' and wkb_geometry && (
SELECT ST_Expand(ST_Extent(wkb_geometry),0.01) AS expanded
FROM public.tlm_50_index
where mdg_id like 'M758G39354'
)
SQL to get the cutout for oceans/lakes/coastlines, etc...:
select ST_INTERSECTION(b.bounds,wkb_geometry)
--select ogc_fid, ST_ASText(wkb_geometry)
from oceans, (
select ST_SETSRID(ST_Extent(wkb_geometry),4326) as bounds
from public.tlm_50_index
WHERE mdg_id != 'M758G39354' and wkb_geometry && (
SELECT ST_Expand(ST_Extent(wkb_geometry),0.01) AS expanded
FROM public.tlm_50_index
where mdg_id like 'M758G39354'
)
) AS b
WHERE wkb_geometry && b.bounds
Given an mgd_id and a database of sheets to bounds data.
Get the bounding box's of the following: