José Felgueiras nº 6179


Exercicio 1


CREATE DATABASE "Freq2_6179"
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       LC_COLLATE = 'Portuguese, Portugal'
       LC_CTYPE = 'Portuguese, Portugal'
       CONNECTION LIMIT = -1;




Exercicio 2

       a)

       tabela fregpcpl
            nº de registros 74
            tipo de geometria ST_MultiPolygon

        tabela Meteo
            nº de registros 37
            tipo de geometria ST_Point

        tabela hidro
            nº de registros 184
            tipo de geometria ST_MultiLineString

        tabela ZPE
            nº de registros 6
            tipo de geometria ST_MultiPolygon

        b)

        create table turismo (id serial PRIMARY KEY, descricao text, codigo text);
        select AddGeometryColumn('turismo', 'the_geom', 3763, 'GEOMETRY', 2 );

        INSERT INTO turismo VALUES ( 1, 'Norte','T1', ST_GeomFromText ('POLYGON ((-39880 247130, -338740 247500, -38930 246445, -39880 247130))', 3763 ) );
        INSERT INTO turismo VALUES ( 2, 'Centro','T2', ST_GeomFromText ('POLYGON ((-34500 245520, -33250 245630, -34390 244490, -34500 245520))', 3763 ) );
        INSERT INTO turismo VALUES ( 3, 'Sul','T3', ST_GeomFromText ('POLYGON ((-40520 237230, -39385 237590, -39570 236530, -40520 237230))', 3763 ) );


Exercicio 3
        a)
        select descricao, st_area(the_geom) from turismo
        "Norte";102183800
        "Centro";649800
        "Sul";568250

        b)
        select concelho, sqrt(sum(ST_area2d(the_geom))) from fregpcpl where concelho like 'PAREDES DE COURA' or concelho like 'PONTE DE LIMA' GROUP BY concelho

        "PAREDES DE COURA";138185913.444047
        "PONTE DE LIMA";320254616.706563

        c)
        select count(*) from hidro where st_length(the_geom)<2000
        39

        d)
        select count(m.*) from meteo m, zpe z where ST_within(m.the_geom, z.the_geom) and z.nome = 'Peneda-Gerês'
        14

        e)
        select f.nome, count(m.*) from meteo m, fregpcpl f where f.concelho = 'PONTE DE LIMA' and ST_within(m.the_geom, f.the_geom) GROUP By f.nome
        "BOALHOSA";1
        "MOREIRA DO LIMA";1
        "LABRUJÓ";1
        "ARCOZELO";1
        "CALVELO";1
        "ARCOS";1


        f)
        select designacao from hidro where ST_within( the_geom,( select st_buffer(the_geom, 5000) from meteo where nome = 'SOUTO') ) limit 1
        "Rio Lima"
        "Rio Lima"
        "Rio Lima"
        "Rio Lima"


        g)
        select m1.nome, m2.nome, st_distance(m1.the_geom, m2.the_geom) from meteo m1, meteo m2 where m1.nome != m2.nome order by st_distance(m1.the_geom, m2.the_geom) asc  limit 1
        "LINDOSO 1";"LINDOSO 2";295.93763560723

        h)
        select sum(st_length(intersection(h.the_geom, f.the_geom)))/1000
        from hidro h, fregpcpl f
        where st_intersects(h.the_geom,f.the_geom)
        and f.concelho = 'PAREDES DE COURA';

        61.3076312783985

        i)
        select f.nome from fregpcpl f, meteo m where not st_intersects( st_buffer(m.the_geom,10000), f.the_geom )
        "ROMARIGÃES"


Exercicio 4

        a)
        create view view4_a as
            select gid, nome, the_geom from zpe where st_area(the_geom) > 50000000

        b)

        create view view4_b as
            select m.gid, m.nome, m.the_geom from meteo m, fregpcpl f where ST_within(m.the_geom, f.the_geom) and f.concelho = 'PONTE DE LIMA'
        c)
        create view view4_c as
            select t.id, t.the_geom
            from turismo t, hidro h
            where st_distance(t.the_geom, h.the_geom )<200

Exercicio 5

        a)

        select nome from zpe
        where not st_intersects( the_geom, (select st_union(the_geom) from meteo))

        Retorna o nome de todas as zonas protegidas que nao tem estação meteriologica

        b)
        select m.*
        from meteo m, ( select st_union(the_geom) as the_geom from zpe) z,
                        (select st_union(st_buffer(the_geom, 1000)) as the_geom from hidro) bh
        where st_contains(st_difference(z.the_geom, bh.the_geom), m.the_geom);

Retorna todos os campos das estações meteorológica que ou estão numa zona protegida ou tem um rio a menos de 1 km