Changes between Version 97 and Version 98 of CookBook join BDMAP_CCM
- Timestamp:
- May 21, 2010 2:31:26 PM (15 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
CookBook join BDMAP_CCM
v97 v98 10 10 * ticket #20 11 11 {{{ 12 #!sql 12 13 create schema csp; 13 14 drop table if exists csp.codier; … … 61 62 Press F5 on BDMAP in pgAdminIII, the table stationsp has been created in BDMAP-Schémas-public-Tables (not in BDMAP-Schémas-bdmap-tables ? is it normal ?) > stationsp (20 Colonnes and 4 contraintes)[[BR]][[BR]] 62 63 {{{ 64 #!sql 63 65 SELECT ST_SRID(the_geom) from stationsp; 64 66 }}} … … 70 72 If shp2psql does not populate the geometry column table, you have to do it manually, see ["CookBook Postgis"] Geometry column section for more details 71 73 {{{ 74 #!sql 72 75 INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type") 73 76 SELECT '', 'public', 'stationsp', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom) … … 79 82 * We first create a table using the AS SELECT command, it is populated with colums from station, and with columns from stationsp2, note the CASE WHEN syntax, which allow to use station.the_geom when stationsp.the_geom is not present in the table. So in practise we use all the old coordinates plus the new ones which have not been verified. Also noteworthy in the script is the CREATE TABLE ... WITH OIDS. The OIDS are mandatory if you want to display the table in Qgis. 80 83 {{{ 84 #!sql 81 85 DROP TABLE IF EXISTS stationsp2; 82 86 CREATE TABLE stationsp2 WITH OIDS AS( … … 133 137 * ah, and now if you want to display the new stations using Qgis, adding a further column saying whether or not the column X is filled is usefull, the result is displayed there : [screenshot:4], 134 138 {{{ 139 #!sql 135 140 ALTER TABLE stationsp2 ADD COLUMN newstation boolean; 136 141 UPDATE stationsp2 set newstation=TRUE where x is null; … … 139 144 * now some results ... 140 145 {{{ 146 #!sql 141 147 select count(*) from stationsp2 --10203 142 148 select count(*) from bdmap.station --10203 … … 146 152 * those are the wrong stations [screenshot:5] 147 153 {{{ 148 154 #!sql 149 155 select * from stationsp2 where st_codecsp IN ('03890162','062B0086','01620140','03270062','03270061','03270063','03270064','062160126','06420055','06070241') 150 156 }}} … … 154 160 * if you want to drop those stations 155 161 {{{ 162 #!sql 156 163 delete from stationsp2 where st_codecsp IN ('03890162','062B0086','01620140','03270062','03270061','03270063','03270064','062160126','06420055','06070241') 157 164 }}} … … 192 199 You will also have to populate the geometry_column table manually 193 200 {{{ 201 #!sql 194 202 INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type") 195 203 SELECT '', 'bd_map', 'stationsp2', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom) … … 198 206 199 207 {{{ 208 #!sql 200 209 alter table bd_map.stationsp2 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); 201 210 alter table bd_map.stationsp2 add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL); … … 210 219 211 220 {{{ 221 #!sql 212 222 CREATE INDEX indexriversegments ON ccm21.riversegments 213 223 USING GIST ( the_geom GIST_GEOMETRY_OPS ); … … 219 229 Joining the riversegment and stationsp2, the_geom is that of stationsp2 220 230 {{{ 221 231 #!sql 222 232 -- creation de la table correspondance, je rajoute un the_geom a la requete pour pouvoir voir la table dans Qgis 223 233 DROP TABLE IF EXISTS bd_map.correspondance; … … 248 258 249 259 {{{ 260 #!sql 250 261 select count(*) from bd_map.correspondance ;--9272 -- 8884 a 300m 251 262 select count(*) from bd_map.stationsp2 ;--10203
