back to first page ..
back to CookBook Eda
back to Temperature

Joining the temperature with CCM layer

One temperature correspond to several riversegments

• Joining tmp with CCM layer

  DROP TABLE IF EXISTS temperature.tmp_ccm;
  
  CREATE TABLE temperature.tmp_ccm as(
  SELECT gridid, gid, min(distance) as distance, the_geom FROM (
  SELECT gridid, gid, CAST(distance(c.the_geom,t.the_geom) as decimal(15,1)) as distance, t.the_geom FROM temperature.tmp AS t
  INNER JOIN ccm21.riversegments c ON ST_DWithin(c.the_geom,t.the_geom,70000)
  ORDER BY gridid) AS sub
  GROUP BY gridid, distance, gid, the_geom
  );
  
  -- création d'index, clé primaire, et contraintes qui vont bien
  alter table temperature.tmp_ccm add column pk serial PRIMARY KEY;
  alter table temperature.tmp_ccm add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
  alter table temperature.tmp_ccm add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
  alter table temperature.tmp_ccm add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
  
  CREATE INDEX indextmp_ccm ON temperature.tmp_ccm
    USING GIST ( the_geom GIST_GEOMETRY_OPS );
  

• Joining tmx with CCM layer

  DROP TABLE IF EXISTS temperature.tmx_ccm;
  
  CREATE TABLE temperature.tmx_ccm as(
  SELECT gridid, gid, min(distance) as distance, the_geom FROM (
  SELECT gridid, gid, CAST(distance(c.the_geom,t.the_geom) as decimal(15,1)) as distance, t.the_geom FROM temperature.tmx AS t
  INNER JOIN ccm21.riversegments c ON ST_DWithin(c.the_geom,t.the_geom,70000)
  ORDER BY gridid) AS sub
  GROUP BY gridid, distance, gid, the_geom
  );
  
  -- création d'index, clé primaire, et contraintes qui vont bien
  alter table temperature.tmx_ccm add column pk serial PRIMARY KEY;
  alter table temperature.tmx_ccm add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
  alter table temperature.tmx_ccm add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
  alter table temperature.tmx_ccm add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
  
  CREATE INDEX indextmx_ccm ON temperature.tmx_ccm
    USING GIST ( the_geom GIST_GEOMETRY_OPS );
  

• Joining tmn with CCM layer

  DROP TABLE IF EXISTS temperature.tmn_ccm;
  
  CREATE TABLE temperature.tmn_ccm as(
  SELECT gridid, gid, min(distance) as distance, the_geom FROM (
  SELECT gridid, gid, CAST(distance(c.the_geom,t.the_geom) as decimal(15,1)) as distance, t.the_geom FROM temperature.tmn AS t
  INNER JOIN ccm21.riversegments c ON ST_DWithin(c.the_geom,t.the_geom,70000)
  ORDER BY gridid) AS sub
  GROUP BY gridid, distance, gid, the_geom
  );
  
  -- création d'index, clé primaire, et contraintes qui vont bien
  alter table temperature.tmn_ccm add column pk serial PRIMARY KEY;
  alter table temperature.tmn_ccm add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
  alter table temperature.tmn_ccm add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
  alter table temperature.tmn_ccm add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
  
  CREATE INDEX indextmn_ccm ON temperature.tmn_ccm
    USING GIST ( the_geom GIST_GEOMETRY_OPS );
  

Joining the temperature with BDMAP layer ?

One temperature correspond to several BDMAP points

Joining tmp with bdmap layer

source:trunk/EDAcommun/sql/BaseEdaCCMriversegmentstemp.r