Joining the stationsp table with station table

[cedric]

In stationsp, the location of electrofishing points has been updated, but new stations have been fished since this treatment. We update existing stations with previous coordinates if different from actual

[cedric]

I've created a new table station_sp2, with when existing the geom from Hélène and when not the new geom integrated in BDMAP.

DROP TABLE IF EXISTS stationsp2;
CREATE TABLE stationsp2 WITH OIDS AS(
  select 
  station.st_altitude,
  station.st_abcisse,
  station.st_codecsp,
  station.st_codesei,
  station.st_datearret,
  station.st_datecreation,
  station.st_distancesource,
  station.st_distancemer,
  station.st_finalite,
  station.st_imageign,
  station.st_imagedept,
  station.st_lieudit,
  station.st_limites,
  station.st_localisation,
  station.st_longueur,
  station.st_moduleia,
  station.st_cd_naturecourseau,
  station.st_ordonnee,
  station.st_penteign,
  station.st_pkaval,
  station.st_raisremp,
  station.st_sbv,
  station.st_t_janvier,
  station.st_t_juillet,
  station.st_cd_typecourseau,
  station.st_cd_tet,
  station.st_st_id,
  station.st_cm_id,
  station.st_cx_id,
  station.st_th_id,
  station.st_eh_id,
  station.st_uh_id,
  station.st_dt_cre,
  station.st_dt_maj,
  station.st_qi_maj,
  station.st_masseeau,
  stationsp.x,
  stationsp.y,
  stationsp.fnode_,
  stationsp.tnode_,
  stationsp.id_trhyd,
  stationsp.st_id,
  CASE WHEN stationsp.the_geom IS NULL THEN station.the_geom
       ELSE stationsp.the_geom
       END AS the_geom
  FROM stationsp right join bdmap.station on stationsp.st_codecsp=station.st_codecsp)
COMMENT ON TABLE stationsp2 is 'table BDMAP extraite janvier 2010 et mise à jour avec les coordonnées reprojetées par Hélène'


select count(*) from stationsp2
select count(*) from bdmap.station --10203 for both
select count(*) from stationsp --8741

select * from stationsp2 limit 1000

ALTER TABLE stationsp2 ADD COLUMN newstation boolean;
UPDATE stationsp2 set newstation=TRUE where x is null;
UPDATE stationsp2 set newstation=FALSE where x is not null;

ALTER TABLE bdmap.stationsp2 SET WITH OIDS;

-- les problèmes de projection apparents

select * from stationsp2 where st_codecsp IN ('03890162','062B0086','01620140','03270062','03270061','03270063','03270064','062160126','06420055','06070241')

[cedric]

Milestone Data integration deleted