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

["RStudio"] at Irstea [[BR]]

![[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.[[BR]]
!source:"data/Docs/trac/POSE/"

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


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


== Postgres ==

New version with ST_HausdorffDistance see http://docs.postgresqlfr.org/8.1/migration.html [[BR]]
{{{
SELECT version();
select postgis_full_version(); 
select PostGIS_version();
}}}
SRID ["srid"]
{{{
#!sql
select * from spatial_ref_sys where srtext LIKE '%NTF%';
}}}

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

{{{
#!sql
--- 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
{{{
#!sql
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
{{{
#!sql
ALTER TABLE ccm21.riversegments ADD CONSTRAINT riversegments_pkey PRIMARY KEY (gid);
}}}

 * Drop a primary key
{{{
#!sql
ALTER TABLE ccm21.riversegments DROP CONSTRAINT riversegments_pkey;
}}}
 * Change the srid into 3035 (ETRS1989 LAEA)
{{{
#!sql
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
{{{
#!sql
CREATE VIEW results AS select * from ccm21.riversegments r inner join ccm21.resultmodel re on re.res_wso1_id=r.wso1_id
}}}

* Sequence
{{{
#!sql
ALTER SEQUENCE rht.model_mod_mod_id_seq RESTART WITH 2;
}}}

--- Riversegments

{{{
#!sql
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
{{{
#!sql
  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)
}}}