back to first page[..][[BR]]
back to ["RHT"][[BR]]

A FINIR
{{{
#!sql
-- creation de la table noeudbdcarthage_rht_roe, je rajoute un the_geom a la requete pour pouvoir voir la table dans Qgis  
DROP TABLE IF EXISTS rht.noeudbdcarthage_rht_roe;
CREATE TABLE rht.noeudbdcarthage_rht_roe as (
        SELECT distinct on (id_bdcarth) id_bdcarth, id_drain, min(distance) as distance, the_geom FROM (
               SELECT id_bdcarth, s.gid, id_drain ,CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance,s.the_geom 
               FROM bd_carthage2011.noeud_hydrographique As s
               INNER JOIN  rht.rht_roev2 r ON ST_DWithin(r.the_geom, s.the_geom,300)
               WHERE s.the_geom IS NOT NULL
               ORDER BY id_bdcarth) AS sub 
        GROUP BY id_bdcarth, distance,id_drain, gid, the_geom
);
alter table rht.noeudbdcarthage_rht_roe 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 '', 'rht', 'noeudbdcarthage_rht_roe', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM rht.noeudbdcarthage_rht_roe LIMIT 1;

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