SWING - algumas tarefas e dúvidas

testes.sql

--ATUALIZAÇÃO DO SIGA:

ALTER TABLE ms_1213_verao RENAME ucs TO ms_ucs;
ALTER TABLE ms_1213_verao RENAME hectares TO ha;
ALTER TABLE ms_1213_verao RENAME the_geom TO geom;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO webgis;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO webgis;

-- EM SEGUIDA DEVE-SE
--CRIAR OS MAPFILES DA NOVA SAFRA
--MUDAR O MAPFILE PRINCIPAL
--CRIAR OS ARQUIVOS .XML



/*trocar o SRID -1 pelo correto:
http://gis.stackexchange.com/questions/34612/how-to-change-the-srid-of-exisisting-data-in-postgis*/
select UpdateGeometrySRID('Schema Name', 'mytable', 'the_geom', newSRID) ;

---


SELECT a.*, b.id_mun
FROM ms_2012_s1 a, mun b
WHERE a.ms_mun = b.ms_mun;

SELECT ms_mun, id_mun
FROM ms_2012_s2
GROUP BY ms_mun, id_mun ORDER BY ms_mun

-- importante!!!
ALTER TABLE ms_2012_s2  ADD COLUMN id_mun int;

UPDATE ms_2012_s2 SET id_mun = (SELECT id_mun FROM mun WHERE ms_2012_s2.ms_mun = mun.ms_mun)

ms_2012_s1 201537

-------------------------------------------------------------------------------------------
---------------------------------- INDICES ------------------------------------------------
-- Create the index
CREATE INDEX idx_ms_2010_s1_geom
on ms_2010_s1
USING gist(geom);

CREATE INDEX idx_ms_2010_s2_geom
on ms_2010_s2
USING gist(geom);

CREATE INDEX idx_ms_2011_s1_geom
on ms_2011_s1
USING gist(geom);

CREATE INDEX idx_ms_2011_s2_geom
on ms_2011_s2
USING gist(geom);

CREATE INDEX idx_ms_2012_s1_geom
on ms_2012_s1
USING gist(geom);

CREATE INDEX idx_ms_2012_s2_geom
on ms_2012_s2
USING gist(geom);

----------------------------------------------
CREATE INDEX ms_2010_s1_id_mun_idx
  ON ms_2010_s1
  USING btree
  (id_mun);

CREATE INDEX ms_2010_s2_id_mun_idx
  ON ms_2010_s2
  USING btree
  (id_mun);

 CREATE INDEX ms_2011_s1_id_mun_idx
  ON ms_2011_s1
  USING btree
  (id_mun);

  CREATE INDEX ms_2011_s2_id_mun_idx
  ON ms_2011_s2
  USING btree
  (id_mun);


  CREATE INDEX ms_2012_s1_id_mun_idx
  ON ms_2012_s1
  USING btree
  (id_mun);


  CREATE INDEX ms_2012_s2_id_mun_idx
  ON ms_2012_s2
  USING btree
  (id_mun);
---------------------------------------------
CREATE INDEX ms_2010_s1_ms_ucs_idx
  ON ms_2010_s1
  USING btree (ms_ucs);

  CREATE INDEX ms_2010_s2_ms_ucs_idx
  ON ms_2010_s2
  USING btree (ms_ucs);

  CREATE INDEX ms_2011_s1_ms_ucs_idx
  ON ms_2011_s1
  USING btree (ms_ucs);

  CREATE INDEX ms_2011_s2_ms_ucs_idx
  ON ms_2011_s2
  USING btree (ms_ucs);

  CREATE INDEX ms_2012_s1_ms_ucs_idx
  ON ms_2012_s1
  USING btree (ms_ucs);

  CREATE INDEX ms_2012_s2_ms_ucs_idx
  ON ms_2012_s2
  USING btree (ms_ucs);
-----------------------------------------------------------------
-- To give the database some information about how the index looks

vacuum analyze ms_2010_s1;

vacuum analyze ms_2010_s2;

vacuum analyze ms_2011_s1;

vacuum analyze ms_2011_s2;

vacuum analyze ms_2012_s1;

vacuum analyze ms_2012_s2;

--------------------------------------------
CLUSTER idx_ms_2010_s1_geom ON ms_2010_s1;

CLUSTER idx_ms_2010_s2_geom ON ms_2010_s2;

CLUSTER idx_ms_2011_s1_geom ON ms_2011_s1;

CLUSTER idx_ms_2011_s2_geom ON ms_2011_s2;

CLUSTER idx_ms_2012_s1_geom ON ms_2012_s1;

CLUSTER idx_ms_2012_s2_geom ON ms_2012_s2;


------------------------------------------

DROP TABLE analises;

CREATE TABLE analises (
	gid serial PRIMARY KEY,
	raio float NOT NULL
	);

SELECT AddGeometryColumn (
	'', -- esquema
	'analises', -- nome da tabela
	'point_geom', -- nome da coluna
	'4326', -- SRID
	'POINT', -- tipo de geometria
	2 -- plano (2d)
	);

SELECT AddGeometryColumn (
	'', -- esquema
	'analises', -- nome da tabela
	'pol_geom', -- nome da coluna
	'4326', -- SRID
	'POLYGON', -- tipo de geometria
	2 -- plano (2d)
	);

CREATE INDEX idx_pol_geom_gist ON analises USING GIST (pol_geom);
CREATE INDEX idx_pt_geom_gist ON analises USING GIST (point_geom);

VACUUM ANALYZE nyc_census_blocks;

-- FUNCTION --
CREATE OR REPLACE FUNCTION fill_buffer()
RETURNS trigger AS
$$
BEGIN

NEW.pol_geom:=ST_Buffer(NEW.point_geom, NEW.raio/111.111);

RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

--
-- TRIGGER --
CREATE TRIGGER tr_fill_buffer
BEFORE INSERT OR UPDATE ON analises
FOR EACH ROW EXECUTE PROCEDURE fill_buffer();
--

SELECT ms_ucs,
SUM(ST_Area(Geography((ST_Intersection(analises.pol_geom,ms_2010_s1.geom)))))/10000
AS area_ha
FROM analises, ms_2010_s1
WHERE analises.pol_geom && ms_2010_s1.geom
AND ST_Intersects(analises.pol_geom,ms_2010_s1.geom)
GROUP BY ms_ucs;


------- testar o resultado do clip
CREATE TABLE apagar AS
SELECT ROW_NUMBER() over (order by ms_2012_s2.ms_ucs) as gid,
       ms_ucs, 
       ST_Union( ST_Intersection(analises.pol_geom,ms_2012_s2.geom)) AS geom 
FROM analises, ms_2012_s2 
WHERE ST_IsValid(ms_2012_s2.geom) 
AND ST_Intersects(analises.pol_geom,ms_2012_s2.geom) 
AND analises.gid = 14 
GROUP BY ms_ucs;


===================================================
===================================================
PROBLEMA PARA RETORNAR MAIS DE UM MUNICÍPIO...
http://old.nabble.com/zoom-to-features-from-external-application-td19685547.html



===================================================================================
MODIFICAÇÕES - NOMES DAS TABELAS E TAMBÉM NA TABELA ANÁLISES:

-- Create the index
CREATE INDEX idx_ms_0910_inverno_geom
on ms_0910_inverno
USING gist(geom);

CREATE INDEX idx_ms_0910_verao_geom
on ms_0910_verao
USING gist(geom);
 
CREATE INDEX idx_ms_1011_inverno_geom
on ms_1011_inverno
USING gist(geom);
 
CREATE INDEX idx_ms_1011_verao_geom
on ms_1011_verao
USING gist(geom);
 
CREATE INDEX idx_ms_1112_inverno_geom
on ms_1112_inverno
USING gist(geom);
 
CREATE INDEX idx_ms_1112_verao_geom
on ms_1112_verao
USING gist(geom);
 
CREATE INDEX idx_analises_geom
on analises
USING gist(pol_geom);
--------------------------------------------
vacuum analyze ms_0910_inverno;

vacuum analyze ms_0910_verao;
 
vacuum analyze ms_1011_verao;
 
vacuum analyze ms_1011_inverno;
 
vacuum analyze ms_1112_inverno;
 
vacuum analyze ms_1112_verao;

vacuum analyze analises;
--------------------------------------------
CLUSTER idx_ms_0910_inverno_geom ON ms_0910_inverno;
 
CLUSTER idx_ms_0910_verao_geom ON ms_0910_verao;
 
CLUSTER idx_ms_1011_inverno_geom ON ms_1011_inverno;

CLUSTER idx_ms_1011_verao_geom ON ms_1011_verao;
 
CLUSTER idx_ms_1112_inverno_geom ON ms_1112_inverno;

CLUSTER idx_ms_1112_verao_geom ON ms_1112_verao;

CLUSTER idx_analises_geom ON analises;