back to first page [..]
back to ["CookBook Eda"]

RStudio at Irstea

[[Image(source:data/Docs/trac/pourc_projette.jpg)]]

[[Image(source:data/Docs/trac/barrages_cutfrance.png,600px)]]

See the ticket #45

[[Color(,#3849ce,source)]]

[[Freemind(htdocs://site/reunion7janv.mm)]]

source:"data/Docs/trac/POSE/SWOT in Pose.mm" or "Attach file" en bas de la page

Size limit to attach a file : 256KB --> give the name of the folder where we can find the file.
source:"data/Docs/trac/POSE/"

!{{{ !#!html !<h2 style="color: orange">This is raw HTML</h2> !}}}

This is raw HTML

​http://trac.edgewall.org/wiki/WikiFormatting

Postgres

New version with ST_HausdorffDistance see ​http://docs.postgresqlfr.org/8.1/migration.html

SELECT version();
select postgis_full_version(); 
select PostGIS_version();

SRID srid

select * from spatial_ref_sys where srtext LIKE '%NTF%';

Geometry functions
​http://blogs.msdn.com/b/davidlean/archive/2008/10/24/sql-20008-spatial-samples-part-n-of-n-conditional-methods.aspx

--- Nombre de colonnes dans une table
select relnatts from pg_class where relname='stationsp2'

cd C:\Program Files\PostgreSQL\8.4\bin\
pg_restore.exe --host localhost --port 5432 --username postgres --dbname "ccm21" --verbose "C:\me\riversegments.backup"

--supprimer la table puis :
d:
--repertoire ou a été mis le fichier
cd D:\me\CCM21
C:\"Program Files"\PostgreSQL\8.4\bin\psql  -U postgres --dbname "eda2.0" -f "riversegments_backup_fulldistsource.sql"

How to save only a table - backup

C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t ccm21.riversegments eda2.0> riversegments_backup.sql

C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 --inserts -t ccm21.catchments eda2.0> catchments_backup.sql

How to save a schema

C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 --schema clc --verbose eda2.0> eda2_clc.sql

How to save several schema

pg_dump -h 192.168.1.104 -U postgres --verbose  --schema barlbr --schema shp --schema result --schema public --schema geobs --schema archive --file "barrage_vilaine_partiel_9012012.sql" barrage_vilaine

pg_dump -h 192.168.1.104 -U postgres --verbose  --exclude-schema barlbr --exclude-schema shp --exclude-schema result --exclude-schema public --exclude-schema geobs --exclude-schema archive --file "barrage_vilaine_autres_9012012.sql" barrage_vilaine

• Intégration d'une colonne géométrique

  SELECT AddGeometryColumn ( 'rht','attributs_rht_fev_2011_vs2','the_geom',3035,'MULTILINESTRING',2);
  update rht.attributs_rht_fev_2011_vs2 set the_geom=rht.the_geom from rht.rht
   where rht.id_drain=attributs_rht_fev_2011_vs2.id_drain; 
  

• Add a primary key

  ALTER TABLE ccm21.riversegments ADD CONSTRAINT riversegments_pkey PRIMARY KEY (gid);
  

• Drop a primary key

  ALTER TABLE ccm21.riversegments DROP CONSTRAINT riversegments_pkey;
  

• Change the srid into 3035 (ETRS1989 LAEA)

  ALTER TABLE uga2010.uga DROP CONSTRAINT enforce_srid_the_geom;
  UPDATE uga2010.uga SET the_geom = ST_transform(the_geom, 3035); 
  ALTER TABLE uga2010.uga ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);
  

• Create view of a query

  CREATE VIEW results AS select * from ccm21.riversegments r inner join ccm21.resultmodel re on re.res_wso1_id=r.wso1_id
  

• Sequence

  ALTER SEQUENCE rht.model_mod_mod_id_seq RESTART WITH 2;
  

--- Riversegments

DROP INDEX IF EXISTS ccm21.indexriversegments_wso1_id;
CREATE INDEX indexriversegments_wso1_id
ON ccm21.riversegments
USING btree (wso1_id);

-- ALTER TABLE ccm21.riversegments1 DROP CONSTRAINT enforce_dims_the_geom;
ALTER TABLE ccm21.riversegments ADD CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2);
-- ALTER TABLE ccm21.riversegments1 DROP CONSTRAINT enforce_geotype_the_geom;
ALTER TABLE ccm21.riversegments ADD CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTILINESTRING'::text OR the_geom IS NULL);
-- ALTER TABLE ccm21.riversegments1 DROP CONSTRAINT enforce_srid_the_geom;
ALTER TABLE ccm21.riversegments ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);
-- ALTER TABLE ccm21.riversegments DROP CONSTRAINT riversegments_pkey;
ALTER TABLE ccm21.riversegments ADD CONSTRAINT riversegments_pkey PRIMARY KEY(gid);

}}}

select wso_id, area from france.wso f where area='France' except (select wso_id, area from europe.wso where area='France') 

Permet connaitre id_drain qui n'a pas d'attribut

  select * from rht.rht where id_drain not in (select id_drain from rht.rht right outer join rht.attributs_rht_fev_2011 on id_drain=id_drainattr)