Changes between Version 18 and Version 19 of CookBook join ROE_CCM

Timestamp:

Mar 16, 2010 3:41:22 PM (15 years ago)

Author:

cedric

Comment:

--

CookBook join ROE_CCM

@@ -70 +70 @@
 REM Cédric =======================
 CD C:\Documents and Settings\cedric\Mes documents\Migrateur\eda\couches_SIG\france
-C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -I DEPARTEMENT france.departement> departement.sql
-C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -c "DROP schema if exists france"
-C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -c "CREATE schema france"
+C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -I DEPARTEMENT_3035 france.departement> departement.sql
+C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -c "DROP schema if exists france CASCADE"
+C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -c "CREATE schema france "
 C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -f departement.sql
 REM Céline =======================
@@ -81 +81 @@
 C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5432 -f departement.sql
 }}}
+Join query we have 43937 lines
+{{{
+SELECT count(ref_id) from geobs.obstacle_referentiel -- 43937
+}}}
+The following does not work and it makes sense, we should only have one geom of france
+{{{
+SELECT  count(ref_id) from geobs.obstacle_referentiel o
+ join france.departement f ON ST_DWithin(f.the_geom, o.ref_position_etrs89, 2000); --49468
+}}}
+I first aggregate all departements and then join this glued layer with the dam layer, the distance is 2000
+{{{
+SELECT  count(ref_id) from geobs.obstacle_referentiel o
+join (SELECT ST_Union(f.the_geom) as singlegeom
+         FROM france.departement  As f) as sub
+ON ST_DWithin(sub.singlegeom, o.ref_position_etrs89, 2000) --43909
+}}}
+
+
 
 == Build a view allowing to know the score of the different dams ==