back to first page..
back to CookBook Eda
back to RHT

Joining RHT with BDMAP

Joining the riversegment from RHT and stationsp2 (??)

Problème de projections !!! A vérifier en cours ! -- creation de la table bdmap_rht, je rajoute un the_geom a la requete pour pouvoir voir la table dans Qgis DROP TABLE IF EXISTS rht.bdmap_rht; CREATE TABLE rht.bdmap_rht 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 rht.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, distance,gid, the_geom

); alter table rht.bdmap_rht 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', 'bdmap_rht', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType?(the_geom) FROM rht.bdmap_rht LIMIT 1;

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

USING GIST ( the_geom GIST_GEOMETRY_OPS );