Changes between Version 99 and Version 100 of WKTRasterTutorial01
- Timestamp:
- Oct 19, 2010, 7:27:22 AM (14 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
WKTRasterTutorial01
v99 v100 48 48 49 49 {{{ 50 50 >"C:/Program Files/PostgreSQL/8.4/bin/shp2pgsql" 51 51 }}} 52 52 … … 54 54 55 55 {{{ 56 >shp2pgsql -s 32198 -I C:\Temp\TutData\cariboupoints.shp > C:\Temp\TutData\cariboupoints.sql 56 >shp2pgsql -s 32198 -I C:\Temp\TutData\cariboupoints.shp > 57 C:\Temp\TutData\cariboupoints.sql 57 58 }}} 58 59 … … 66 67 67 68 {{{ 68 69 >psql -f C:\Temp\TutData\cariboupoints.sql tutorial01 69 70 }}} 70 71 … … 82 83 83 84 {{{ 84 85 85 SELECT id, ST_AsBinary(the_geom) 86 FROM cariboupoints; 86 87 }}} 87 88 … … 97 98 98 99 {{{ 99 100 >gdal2wktraster.py -h 100 101 }}} 101 102 … … 105 106 106 107 {{{ 107 108 108 >gdal2wktraster.py -r C:\Temp\TutData\SRTM\tif\*.tif -t srtm_tiled -s 4326 109 -k 50x50 -I > C:\Temp\TutData\SRTM\srtm.sql 109 110 }}} 110 111 … … 124 125 125 126 {{{ 126 127 >psql -f C:\Temp\TutData\SRTM\srtm.sql tutorial01 127 128 }}} 128 129 … … 132 133 133 134 {{{ 134 135 CREATE INDEX srtm_tiled_rast_gist_idx ON srtm_tiled USING GIST (ST_ConvexHull(rast)); 135 136 }}} 136 137 … … 138 139 139 140 {{{ 140 141 142 143 144 141 SELECT (md).*, (bmd).* 142 FROM (SELECT ST_Metadata(rast) AS md, 143 ST_BandMetadata(rast) AS bmd 144 FROM srtm_tiled LIMIT 1 145 ) foo; 145 146 }}} 146 147 … … 150 151 151 152 {{{ 152 153 153 SELECT rid, ST_AsBinary(rast::geometry) 154 FROM srtm_tiled; 154 155 }}} 155 156 … … 161 162 162 163 {{{ 163 164 164 SELECT ST_AsBinary(ST_Buffer(ST_Union(rast::geometry), 0.000001)) 165 FROM srtm_tiled; 165 166 }}} 166 167 … … 173 174 174 175 {{{ 175 176 177 178 176 SELECT ST_AsBinary((ST_DumpAsPolygons(rast)).geom), 177 (ST_DumpAsPolygons(rast)).val 178 FROM srtm_tiled 179 WHERE rid=3278; 179 180 }}} 180 181 … … 192 193 193 194 {{{ 194 195 196 195 SELECT ST_BandHasNodataValue(rast), ST_BandNodataValue(rast) 196 FROM srtm_tiled 197 LIMIT 1; 197 198 }}} 198 199 … … 200 201 201 202 {{{ 202 203 204 205 203 SELECT ST_AsBinary((ST_DumpAsPolygons(ST_SetBandHasNodataValue(rast, FALSE))).geom), 204 (ST_DumpAsPolygons(ST_SetBandHasNodataValue(rast, FALSE))).val 205 FROM srtm_tiled 206 WHERE rid=3278; 206 207 }}} 207 208 … … 211 212 212 213 {{{ 213 CREATE OR REPLACE FUNCTION ST_RandomPoints(geom geometry, nb int) 214 RETURNS SETOF geometry AS 215 $$ 216 DECLARE 217 pt geometry; 218 xmin float8; 219 xmax float8; 220 ymin float8; 221 ymax float8; 222 xrange float8; 223 yrange float8; 224 srid int; 225 count integer := 0; 226 bcontains boolean := FALSE; 227 gtype text; 228 BEGIN 229 SELECT ST_GeometryType(geom) 230 INTO gtype; 231 IF ( gtype != 'ST_Polygon' ) AND ( gtype != 'ST_MultiPolygon' ) THEN 232 RAISE EXCEPTION 'Attempting to get random point in a non polygon geometry'; 233 END IF; 234 SELECT ST_XMin(geom), ST_XMax(geom), ST_YMin(geom), ST_YMax(geom), ST_SRID(geom) 235 INTO xmin, xmax, ymin, ymax, srid; 236 SELECT xmax - xmin, ymax - ymin 237 INTO xrange, yrange; 238 239 WHILE count < nb LOOP 240 SELECT ST_SetSRID(ST_MakePoint(xmin + xrange * random(), ymin + yrange * random()), srid) 241 INTO pt; 242 SELECT ST_Contains(geom, pt) 243 INTO bcontains; 244 IF bcontains THEN 245 count := count + 1; 246 RETURN NEXT pt; 247 END IF; 248 END LOOP; 249 RETURN; 250 END; 251 $$ 252 LANGUAGE 'plpgsql' IMMUTABLE STRICT; 214 CREATE OR REPLACE FUNCTION ST_RandomPoints(geom geometry, nb int) 215 RETURNS SETOF geometry AS 216 $$ 217 DECLARE 218 pt geometry; 219 xmin float8; 220 xmax float8; 221 ymin float8; 222 ymax float8; 223 xrange float8; 224 yrange float8; 225 srid int; 226 count integer := 0; 227 bcontains boolean := FALSE; 228 gtype text; 229 BEGIN 230 SELECT ST_GeometryType(geom) 231 INTO gtype; 232 IF ( gtype != 'ST_Polygon' ) AND ( gtype != 'ST_MultiPolygon' ) THEN 233 RAISE EXCEPTION 'Attempting to get random point in a non polygon geometry'; 234 END IF; 235 SELECT ST_XMin(geom), ST_XMax(geom), ST_YMin(geom), ST_YMax(geom), ST_SRID(geom) 236 INTO xmin, xmax, ymin, ymax, srid; 237 SELECT xmax - xmin, ymax - ymin 238 INTO xrange, yrange; 239 WHILE count < nb LOOP 240 SELECT ST_SetSRID(ST_MakePoint(xmin + xrange * random(), ymin + yrange * random()), srid) 241 INTO pt; 242 SELECT ST_Contains(geom, pt) 243 INTO bcontains; 244 IF bcontains THEN 245 count := count + 1; 246 RETURN NEXT pt; 247 END IF; 248 END LOOP; 249 RETURN; 250 END; 251 $$ 252 LANGUAGE 'plpgsql' IMMUTABLE STRICT; 253 253 }}} 254 254 … … 256 256 257 257 {{{ 258 259 260 261 262 263 258 CREATE TABLE cariboupoints AS 259 SELECT generate_series(1, 814) AS id, 260 ST_Transform(ST_RandomPoints(the_geom, 814), 32198) AS the_geom 261 FROM (SELECT ST_SetSRID(ST_Extent(rast::geometry), 4326) AS the_geom 262 FROM srtm_tiled 263 ) foo; 264 264 }}} 265 265 … … 267 267 268 268 {{{ 269 270 269 SELECT id, ST_AsBinary(the_geom) 270 FROM cariboupoints; 271 271 }}} 272 272 … … 280 280 281 281 {{{ 282 283 284 282 CREATE TABLE cariboupoint_buffers AS 283 SELECT id, ST_Buffer(the_geom, 1000) AS the_geom 284 FROM cariboupoints; 285 285 }}} 286 286 … … 288 288 289 289 {{{ 290 291 290 SELECT id, ST_AsBinary(the_geom) 291 FROM cariboupoint_buffers; 292 292 }}} 293 293 … … 295 295 296 296 {{{ 297 298 299 297 CREATE TABLE cariboupoint_buffers_wgs AS 298 SELECT id, ST_Transform(the_geom, 4326) AS the_geom 299 FROM cariboupoint_buffers; 300 300 }}} 301 301 … … 303 303 304 304 {{{ 305 306 305 SELECT id, ST_AsBinary(the_geom) 306 FROM cariboupoint_buffers_wgs; 307 307 }}} 308 308 … … 310 310 311 311 {{{ 312 313 314 312 CREATE TABLE cariboupoint_buffers_wgs AS 313 SELECT id, ST_Transform(ST_Buffer(the_geom, 1000), 4326) AS the_geom 314 FROM cariboupoints; 315 315 }}} 316 316 … … 318 318 319 319 {{{ 320 320 CREATE INDEX cariboupoint_buffers_wgs_geom_idx ON cariboupoint_buffers_wgs USING GIST (the_geom); 321 321 }}} 322 322 … … 326 326 327 327 {{{ 328 329 328 CREATE TABLE caribou_srtm_inter AS 329 SELECT id, 330 330 (ST_Intersection(rast, the_geom)).geom AS the_geom, 331 331 (ST_Intersection(rast, the_geom)).val 332 333 334 332 FROM cariboupoint_buffers_wgs, 333 srtm_tiled 334 WHERE ST_Intersects(rast, the_geom); 335 335 }}} 336 336 … … 338 338 339 339 {{{ 340 341 342 343 344 345 346 347 348 349 340 CREATE TABLE caribou_srtm_inter AS 341 SELECT id, 342 (gv).geom AS the_geom, 343 (gv).val 344 FROM (SELECT id, 345 ST_Intersection(rast, the_geom) AS gv 346 FROM srtm_tiled, 347 cariboupoint_buffers_wgs 348 WHERE ST_Intersects(rast, the_geom) 349 ) foo; 350 350 }}} 351 351 … … 358 358 359 359 {{{ 360 361 360 SELECT id, val, ST_AsBinary(the_geom) 361 FROM caribou_srtm_inter; 362 362 }}} 363 363 … … 370 370 371 371 {{{ 372 373 374 375 376 377 378 372 CREATE TABLE result01 AS 373 SELECT id, 374 sum(ST_Area(ST_Transform(the_geom, 32198)) * val) / 375 sum(ST_Area(ST_Transform(the_geom, 32198))) AS meanelev 376 FROM caribou_srtm_inter 377 GROUP BY id 378 ORDER BY id; 379 379 }}} 380 380 … … 382 382 383 383 {{{ 384 385 384 COPY result01 TO 'C:/temp/tutdata/result01.csv' 385 WITH DELIMITER ',' CSV HEADER; 386 386 }}} 387 387
