back to first page ..
back to Recipes for EDA CookBook Eda
This corresponds to ticket #14

Adding the shape files to the database

Electrofishing Britany

Creating sql file

--Cedric
CD C:\Documents and Settings\cedric\Mes documents\Migrateur\eda\peche_electrique_bretagne\Cédric_Stations IA ANG\Cédric_Stations IA ANG
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 2154 -W LATIN1 Sta_IA_ANG_Bret_L93_CB ia.iabret > ia.sql
--Celine
D:
cd D:\Celine Jouanin\Cédric_Stations IA ANG
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 2154 -W LATIN1 Sta_IA_ANG_Bret_L93_CB ia.iabret > ia.sql

Using psql to load the file

--Cedric
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -h localhost -U postgres -p 5433 -c "CREATE SCHEMA ia;"
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -h localhost -U postgres -p 5433 -f ia.sql 
--Celine
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -h localhost -U postgres -p 5432 -c "CREATE SCHEMA ia;"
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -h localhost -U postgres -p 5432 -f ia.sql 

We have to reproject our data

select SRID (the_geom) FROM ia.iabret--2154 (RGF93/Lambert 93)

ALTER TABLE ia.iabret DROP CONSTRAINT enforce_srid_the_geom;
UPDATE ia.iabret set the_geom=ST_Transform(the_geom,3035) ;
ALTER TABLE ia.iabret ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);

select SRID (the_geom) FROM ia.iabret;--3035

udating table geometry_columns

Update geometry_columns SET srid=3035 where f_table_name='iabret';

IAV files

C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 2154 -W LATIN1 Stations_IAang_IAV_L93 ia.iaiav > iaiav.sql

using psql to load the file

--Cedric
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -h localhost -U postgres -p 5433 -f iaiav.sql 
--Celine
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -h localhost -U postgres -p 5432 -f iaiav.sql 

ALTER TABLE ia.iaiav DROP CONSTRAINT enforce_srid_the_geom;
UPDATE ia.iaiav set the_geom=ST_Transform(the_geom,3035) ;
ALTER TABLE ia.iaiav ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);
Update geometry_columns SET srid=3035 where f_table_name='iaiav';

Projecting the files on the ccm

• first create two index to speed up queries

  CREATE INDEX indexiabret ON ia.iabret
    USING GIST ( the_geom GIST_GEOMETRY_OPS );
  CREATE INDEX indexiaiav ON ia.iaiav
    USING GIST ( the_geom GIST_GEOMETRY_OPS );
  

Joining the riversegment and ia files

-- creation de la table correspondance, je rajoute un the_geom a la requete pour pouvoir voir la table dans Qgis  
-- attentaion cette table contient des doublons et la clause DISTINCT ON NE permet pas de choisir parmi les doublons.
DROP TABLE IF EXISTS ia.correspondancemult;
CREATE TABLE ia.correspondancemult as (
        SELECT iagid, gid, min(distance) as distance, the_geom FROM (
               SELECT i.gid as iagid, r.gid ,CAST(distance(r.the_geom, i.the_geom) as  decimal(15,1)) as distance,i.the_geom 
               FROM ia.iabret As i
               INNER JOIN  ccm21.riversegments r ON ST_DWithin(r.the_geom, i.the_geom,300)
               ORDER BY i.gid) AS sub 
        GROUP BY iagid, gid, the_geom
);

-- Dans cette requête, on recherche d'abord à sélectionner pour chaque point de pêche la plus petite des distances aux tronçons
-- puis comme il existe des doublons (même distance) 
DROP TABLE IF EXISTS ia.correspondance;
CREATE TABLE ia.correspondance as (
	SELECT  distinct on (sub.iagid) sub.iagid ,sub.distance,the_geom,gid FROM 
	(
		SELECT iagid, min(distance) as distance  from ia.correspondancemult
		group by iagid 
		order by iagid
	) as sub 
	LEFT JOIN ia.correspondancemult c on (sub.iagid=c.iagid and sub.distance=c.distance)
);
alter table ia.correspondance add constraint pk_correspondance PRIMARY KEY (iagid);

-- mise à jour de la table geometry_columns
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'ia', 'correspondance', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM ia.correspondance LIMIT 1;

-- creation d'index, clé primaire, et constraintes qui vont bien
alter table ia.correspondance add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table ia.correspondance add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
alter table ia.correspondance add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
CREATE INDEX indexcorrespondance ON ia.correspondance
  USING GIST ( the_geom GIST_GEOMETRY_OPS );