Changes between Version 14 and Version 15 of CookBook EpA

Timestamp:

Mar 30, 2010 3:15:28 PM (15 years ago)

Author:

cedric

Comment:

--

CookBook EpA

@@ -56 +56 @@
   USING GIST ( the_geom GIST_GEOMETRY_OPS );
 }}}
-Joining the riversegment and stationsp2, the_geom is that of stationsp2
+Joining the riversegment and ia files
+{{{
+-- creation de la table correspondance, je rajoute un the_geom a la requete pour pouvoir voir la table dans Qgis  
+-- attentaion cette table contient des doublons et la clause DISTINCT ON NE permet pas de choisir parmi les doublons.
+DROP TABLE IF EXISTS ia.correspondancemult;
+CREATE TABLE ia.correspondancemult as (
+        SELECT iagid, gid, min(distance) as distance, the_geom FROM (
+               SELECT i.gid as iagid, r.gid ,CAST(distance(r.the_geom, i.the_geom) as  decimal(15,1)) as distance,i.the_geom 
+               FROM ia.iabret As i
+               INNER JOIN  riversegments r ON ST_DWithin(r.the_geom, i.the_geom,300)
+               ORDER BY i.gid) AS sub 
+        GROUP BY iagid, gid, the_geom
+);
+-- Dans cette requète, on recherche d'aord à sélectionner pour chaque point de pêche la plus petite des distances aux tronçons
+-- puis comme il existe des doublons (même distance) 
+DROP TABLE IF EXISTS ia.correspondance;
+CREATE TABLE ia.correspondance as (
+        SELECT  distinct on (sub.iagid) sub.iagid ,sub.distance,the_geom,gid FROM 
+        (
+                SELECT iagid, min(distance) as distance  from ia.correspondancemult
+                group by iagid 
+                order by iagid
+        ) as sub 
+        LEFT JOIN ia.correspondancemult c on (sub.iagid=c.iagid and sub.distance=c.distance)
+);
+alter table ia.correspondance add constraint pk_correspondance PRIMARY KEY (iagid);
 
--- 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;
+SELECT '', 'ia', 'correspondance', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
+FROM ia.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
+alter table ia.correspondance add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
+alter table ia.correspondance add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
+alter table ia.correspondance add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
+CREATE INDEX indexcorrespondance ON ia.correspondance
   USING GIST ( the_geom GIST_GEOMETRY_OPS );
+}}}