--2A
select a.city_name as miasto, round(SDO_NN_DISTANCE(1), 3) as odl
from major_cities a
where SDO_NN(a.geom, (select b.geom from major_cities b where b.city_name = 'Warsaw'), 'sdo_num_res=11 unit=km', 1) = 'TRUE'
    and a.city_name != 'Warsaw';

--2B
select a.city_name as miasto
from major_cities a
where SDO_WITHIN_DISTANCE(a.geom, (select b.geom from major_cities b where b.city_name = 'Warsaw'), 'distance=100 unit=km') = 'TRUE'
    and a.city_name != 'Warsaw';

--2C
select b.cntry_name, c.city_name
from country_boundaries b, major_cities c
where SDO_RELATE(c.geom, b.geom, 'mask=INSIDE') = 'TRUE'
and b.cntry_name = 'Slovakia';

--2D
select a.cntry_name as panstwo, SDO_GEOM.SDO_DISTANCE(a.geom, b.geom, 1, 'unit=km') as odl
from country_boundaries a, country_boundaries b
where SDO_FILTER(a.geom, b.geom) != 'TRUE'
and b.cntry_name = 'Poland';

--3A
select a.cntry_name as panstwo, SDO_GEOM.SDO_LENGTH(SDO_GEOM.SDO_INTERSECTION(A.GEOM, B.GEOM, 1), 1, 'unit=km') as odl
from country_boundaries a, country_boundaries b
where SDO_FILTER(a.geom, b.geom) = 'TRUE'
and b.cntry_name = 'Poland' and a.cntry_name != 'Poland';

--3B
select a.cntry_name
from country_boundaries a
order by round(SDO_GEOM.sdo_area(a.geom, 1, 'unit=SQ_KM')) desc
fetch first row only;

--3C
select SDO_GEOM.sdo_area(SDO_GEOM.SDO_MBR(SDO_GEOM.SDO_UNION(
    (select geom from major_cities where city_name = 'Warsaw'),
    (select geom from major_cities where city_name = 'Lodz')
    )), 1, 'unit=SQ_KM') as SQ_KM
from dual;

--3D
select SDO_GEOM.SDO_UNION(
    (select geom from major_cities where city_name = 'Prague'),
    (select geom from country_boundaries b where cntry_name = 'Poland')
    ).SDO_GTYPE as gtype
from dual;

--3E
select c.city_name, b.cntry_name
from country_boundaries b, major_cities c
where b.cntry_name = c.cntry_name
order by SDO_GEOM.SDO_DISTANCE(SDO_GEOM.SDO_CENTROID(b.geom, 1), c.geom, 1, 'unit=km') asc
fetch first row only;

--3F
select distinct r.name, sum(SDO_GEOM.SDO_LENGTH(SDO_GEOM.SDO_INTERSECTION(b.geom, r.geom, 1), 1, 'unit=km')) as odl
from country_boundaries b, rivers r
where b.cntry_name = 'Poland'
    and SDO_RELATE(b.geom, r.geom, 'mask=ANYINTERACT') = 'TRUE'
group by r.name;