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

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

using psql to load the file

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

We have to reproject our data

select SRID (the_geom) FROM ia.iabret--2154

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

C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -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 stationsp2, the_geom is that of stationsp2

-- creation de la table correspondance, je rajoute un the_geom a la requete pour pouvoir voir la table dans Qgis DROP TABLE IF EXISTS bd_map.correspondance; CREATE TABLE bd_map.correspondance as (

SELECT distinct on (st_codecsp) st_codecsp, gid, min(distance) as distance, the_geom FROM (

SELECT st_codecsp, gid ,CAST(distance(r.the_geom, s.the_geom) as decimal(15,1)) as distance,s.the_geom FROM bd_map.stationsp2 As s INNER JOIN riversegments r ON ST_DWithin(r.the_geom, s.the_geom,300) WHERE s.the_geom IS NOT NULL ORDER BY st_codecsp) AS sub

GROUP BY st_codecsp, gid, the_geom

); alter table bd_map.correspondance add column id serial; -- 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 , 'bd_map', 'correspondance', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType?(the_geom) FROM bd_map.correspondance LIMIT 1;

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

USING GIST ( the_geom GIST_GEOMETRY_OPS );