back to first page [..][[BR]]
back to Recipes for EDA ["CookBook Eda"] [[BR]]
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 );