Changes between Version 99 and Version 100 of WKTRasterTutorial01


Ignore:
Timestamp:
Oct 19, 2010, 7:27:22 AM (11 years ago)
Author:
pracine
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • WKTRasterTutorial01

    v99 v100  
    4848
    4949{{{
    50     >"C:/Program Files/PostgreSQL/8.4/bin/shp2pgsql"
     50 >"C:/Program Files/PostgreSQL/8.4/bin/shp2pgsql"
    5151}}}
    5252       
     
    5454
    5555{{{     
    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
    5758}}}
    5859
     
    6667
    6768{{{
    68     >psql -f C:\Temp\TutData\cariboupoints.sql tutorial01
     69 >psql -f C:\Temp\TutData\cariboupoints.sql tutorial01
    6970}}}
    7071       
     
    8283
    8384{{{
    84     SELECT id, ST_AsBinary(the_geom)
    85     FROM cariboupoints;
     85 SELECT id, ST_AsBinary(the_geom)
     86 FROM cariboupoints;
    8687}}}
    8788       
     
    9798
    9899{{{
    99     >gdal2wktraster.py -h
     100 >gdal2wktraster.py -h
    100101}}}
    101102
     
    105106
    106107{{{
    107     >gdal2wktraster.py -r C:\Temp\TutData\SRTM\tif\*.tif -t srtm_tiled -s 4326
    108            -k 50x50 -I > C:\Temp\TutData\SRTM\srtm.sql
     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
    109110}}}
    110111
     
    124125
    125126{{{
    126     >psql -f C:\Temp\TutData\SRTM\srtm.sql tutorial01
     127 >psql -f C:\Temp\TutData\SRTM\srtm.sql tutorial01
    127128}}}
    128129
     
    132133
    133134{{{
    134     CREATE INDEX srtm_tiled_rast_gist_idx ON srtm_tiled USING GIST (ST_ConvexHull(rast));
     135 CREATE INDEX srtm_tiled_rast_gist_idx ON srtm_tiled USING GIST (ST_ConvexHull(rast));
    135136}}}
    136137
     
    138139
    139140{{{
    140     SELECT (md).*, (bmd).*
    141     FROM (SELECT ST_Metadata(rast) AS md,
    142                  ST_BandMetadata(rast) AS bmd
    143           FROM srtm_tiled LIMIT 1
    144          ) foo;
     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;
    145146}}}
    146147
     
    150151
    151152{{{
    152     SELECT rid, ST_AsBinary(rast::geometry)
    153     FROM srtm_tiled;
     153 SELECT rid, ST_AsBinary(rast::geometry)
     154 FROM srtm_tiled;
    154155}}}
    155156
     
    161162
    162163{{{
    163     SELECT ST_AsBinary(ST_Buffer(ST_Union(rast::geometry), 0.000001))
    164     FROM srtm_tiled;
     164 SELECT ST_AsBinary(ST_Buffer(ST_Union(rast::geometry), 0.000001))
     165 FROM srtm_tiled;
    165166}}}
    166167
     
    173174
    174175{{{
    175     SELECT ST_AsBinary((ST_DumpAsPolygons(rast)).geom),
    176            (ST_DumpAsPolygons(rast)).val
    177     FROM srtm_tiled
    178     WHERE rid=3278;
     176 SELECT ST_AsBinary((ST_DumpAsPolygons(rast)).geom),
     177        (ST_DumpAsPolygons(rast)).val
     178 FROM srtm_tiled
     179 WHERE rid=3278;
    179180}}}
    180181
     
    192193
    193194{{{
    194     SELECT ST_BandHasNodataValue(rast), ST_BandNodataValue(rast)
    195     FROM srtm_tiled
    196     LIMIT 1;
     195 SELECT ST_BandHasNodataValue(rast), ST_BandNodataValue(rast)
     196 FROM srtm_tiled
     197 LIMIT 1;
    197198}}}
    198199
     
    200201
    201202{{{
    202     SELECT ST_AsBinary((ST_DumpAsPolygons(ST_SetBandHasNodataValue(rast, FALSE))).geom),
    203                        (ST_DumpAsPolygons(ST_SetBandHasNodataValue(rast, FALSE))).val
    204     FROM srtm_tiled
    205     WHERE rid=3278;
     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;
    206207}}}
    207208
     
    211212
    212213{{{
    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;
    253253}}}
    254254
     
    256256
    257257{{{
    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;
     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;
    264264}}}
    265265
     
    267267
    268268{{{
    269     SELECT id, ST_AsBinary(the_geom)
    270     FROM cariboupoints;
     269 SELECT id, ST_AsBinary(the_geom)
     270 FROM cariboupoints;
    271271}}}
    272272
     
    280280
    281281{{{
    282     CREATE TABLE cariboupoint_buffers AS
    283     SELECT id, ST_Buffer(the_geom, 1000) AS the_geom
    284     FROM cariboupoints;
     282 CREATE TABLE cariboupoint_buffers AS
     283 SELECT id, ST_Buffer(the_geom, 1000) AS the_geom
     284 FROM cariboupoints;
    285285}}}
    286286
     
    288288
    289289{{{
    290     SELECT id, ST_AsBinary(the_geom)
    291     FROM cariboupoint_buffers;
     290 SELECT id, ST_AsBinary(the_geom)
     291 FROM cariboupoint_buffers;
    292292}}}
    293293
     
    295295
    296296{{{
    297     CREATE TABLE cariboupoint_buffers_wgs AS
    298     SELECT id, ST_Transform(the_geom, 4326) AS the_geom
    299     FROM cariboupoint_buffers;
     297 CREATE TABLE cariboupoint_buffers_wgs AS
     298 SELECT id, ST_Transform(the_geom, 4326) AS the_geom
     299 FROM cariboupoint_buffers;
    300300}}}
    301301
     
    303303
    304304{{{
    305     SELECT id, ST_AsBinary(the_geom)
    306     FROM cariboupoint_buffers_wgs;
     305 SELECT id, ST_AsBinary(the_geom)
     306 FROM cariboupoint_buffers_wgs;
    307307}}}
    308308
     
    310310
    311311{{{
    312     CREATE TABLE cariboupoint_buffers_wgs AS
    313     SELECT id, ST_Transform(ST_Buffer(the_geom, 1000), 4326) AS the_geom
    314     FROM cariboupoints;
     312 CREATE TABLE cariboupoint_buffers_wgs AS
     313 SELECT id, ST_Transform(ST_Buffer(the_geom, 1000), 4326) AS the_geom
     314 FROM cariboupoints;
    315315}}}
    316316
     
    318318
    319319{{{
    320     CREATE INDEX cariboupoint_buffers_wgs_geom_idx ON cariboupoint_buffers_wgs USING GIST (the_geom);
     320 CREATE INDEX cariboupoint_buffers_wgs_geom_idx ON cariboupoint_buffers_wgs USING GIST (the_geom);
    321321}}}
    322322
     
    326326
    327327{{{
    328     CREATE TABLE caribou_srtm_inter AS
    329     SELECT id,
     328 CREATE TABLE caribou_srtm_inter AS
     329 SELECT id,
    330330        (ST_Intersection(rast, the_geom)).geom AS the_geom,
    331331        (ST_Intersection(rast, the_geom)).val
    332     FROM cariboupoint_buffers_wgs,
    333          srtm_tiled
    334     WHERE ST_Intersects(rast, the_geom);
     332 FROM cariboupoint_buffers_wgs,
     333      srtm_tiled
     334 WHERE ST_Intersects(rast, the_geom);
    335335}}}
    336336       
     
    338338
    339339{{{
    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;
     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;
    350350}}}
    351351
     
    358358
    359359{{{
    360     SELECT id, val, ST_AsBinary(the_geom)
    361     FROM caribou_srtm_inter;
     360 SELECT id, val, ST_AsBinary(the_geom)
     361 FROM caribou_srtm_inter;
    362362}}}
    363363
     
    370370
    371371{{{
    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;
     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;
    379379}}}
    380380
     
    382382
    383383{{{
    384     COPY result01 TO 'C:/temp/tutdata/result01.csv'
    385     WITH DELIMITER ',' CSV HEADER;
     384 COPY result01 TO 'C:/temp/tutdata/result01.csv'
     385 WITH DELIMITER ',' CSV HEADER;
    386386}}}
    387387