Changes between Version 3 and Version 4 of WKTRasterTutorial01


Ignore:
Timestamp:
Jun 10, 2010, 1:39:23 PM (14 years ago)
Author:
pracine
Comment:

—

Legend:

Unmodified
Added
Removed
Modified

  • WKTRasterTutorial01

    v3 v4  
     1= WKT Raster Tutorial 1 =
     2
    13= Intersecting vector buffers with large raster coverage using PostGIS WKT Raster =
    24
    … …  
    2931
    3032{{{
    31         >"c:/Program Files/PostgreSQL/8.X/bin/shp2pgsql"
     33    >"c:/Program Files/PostgreSQL/8.X/bin/shp2pgsql"
    3234}}}
    3335       
    3436shp2pgsql works in two steps: The first step is to create a .sql file that you then have to execute using psql, an application which comes with PostgreSQL. To convert the caribou point shapefile in cariboupoint.sql, we do the following:
    3537
    36 {{{     >shp2pgsql.exe -s 32198 -I C:\Temp\Pierre\Data\CaribouPoints\cariboupoints.shp > C:\Temp\
    37 Pierre\Data\CaribouPoints\cariboupoints.sql
     38{{{     
     39    >shp2pgsql.exe -s 32198 -I C:\Temp\cariboupoints.shp > C:\Temp\cariboupoints.sql
    3840}}}
    3941
    … …  
    4749
    4850{{{
    49         >psql -f C:\Temp\Pierre\Data\CaribouPoints\cariboupoints.sql demodb     
     51    >psql -f C:\Temp\cariboupoints.sql demodb   
    5052}}}
    5153       
    … …  
    6365
    6466{{{
    65         SELECT id, ST_AsBinary(the_geom)
    66         FROM cariboupoints;
     67    SELECT id, ST_AsBinary(the_geom)
     68    FROM cariboupoints;
    6769}}}
    6870       
    … …  
    7678
    7779{{{
    78         >gdal2wktraster.py -h
     80    >gdal2wktraster.py -h
    7981}}}
    8082
    … …  
    8486
    8587{{{
    86         >gdal2wktraster.py -r C:\Temp\Pierre\Data\SRTM\tif\*.tif -t srtm_tiled -s 4326 -k 100x100 -I > C:\Temp\Pierre\Data\SRTM\srtm.sql
     88    >gdal2wktraster.py -r C:\Temp\Pierre\Data\SRTM\tif\*.tif -t srtm_tiled -s 4326 -k 100x100 -I > C:\Temp\Pierre\Data\SRTM\srtm.sql
    8789}}}
    8890
    … …  
    98100
    99101{{{
    100         CREATE INDEX srtm_tiled_gist_idx ON srtm_tiled USING GIST (ST_ConvexHull(rast));
     102    CREATE INDEX srtm_tiled_gist_idx ON srtm_tiled USING GIST (ST_ConvexHull(rast));
    101103}}}
    102104
    … …  
    106108
    107109{{{
    108         >psql -f C:\Temp\Pierre\Data\SRTM\srtm.sql demodb
     110    >psql -f C:\Temp\Pierre\Data\SRTM\srtm.sql demodb
    109111}}}
    110112
    … …  
    114116
    115117{{{
    116         SELECT rid, ST_AsBinary(rast::geometry) FROM srtm_tiled;
     118    SELECT rid, ST_AsBinary(rast::geometry) FROM srtm_tiled;
    117119}}}
    118120       
    … …  
    120122
    121123{{{
    122         SELECT ST_AsBinary(ST_Buffer(ST_Union(rast::geometry), 0.000001)) FROM srtm_tiled;
     124    SELECT ST_AsBinary(ST_Buffer(ST_Union(rast::geometry), 0.000001)) FROM srtm_tiled;
    123125}}}
    124126       
    … …  
    128130
    129131{{{
    130         SELECT ST_AsBinary((ST_DumpAsPolygons(rast)).geom), (ST_DumpAsPolygons(rast)).val
    131         FROM srtm_tiled
    132         WHERE rid=34;
     132    SELECT ST_AsBinary((ST_DumpAsPolygons(rast)).geom), (ST_DumpAsPolygons(rast)).val
     133    FROM srtm_tiled
     134    WHERE rid=34;
    133135}}}
    134136       
    … …  
    138140
    139141{{{
    140         SELECT ST_BandHasNodataValue(rast), ST_BandNodataValue(rast)
    141         FROM srtm_tiled
    142         LIMIT 1;
     142    SELECT ST_BandHasNodataValue(rast), ST_BandNodataValue(rast)
     143    FROM srtm_tiled
     144    LIMIT 1;
    143145}}}
    144146
    … …  
    146148
    147149{{{
    148         SELECT ST_AsBinary((ST_DumpAsPolygons(ST_SetBandHasNodataValue(rast, FALSE) )).geom), (ST_DumpAsPolygons(ST_SetBandHasNodataValue(rast, FALSE))).val
    149         FROM srtm_tiled
    150         WHERE rid=34;
     150    SELECT ST_AsBinary((ST_DumpAsPolygons(ST_SetBandHasNodataValue(rast, FALSE))).geom),
     151                       (ST_DumpAsPolygons(ST_SetBandHasNodataValue(rast, FALSE))).val
     152    FROM srtm_tiled
     153    WHERE rid=34;
    151154}}}
    152155
    … …  
    160163    $$
    161164    DECLARE
    162                 pt geometry;
    163                 xmin float8;
    164                 xmax float8;
    165                 ymin float8;
    166                 ymax float8;
    167                 xrange float8;
    168                 yrange float8;
    169                 srid int;
    170                 count integer := 0;
    171                 bcontains boolean := FALSE;
    172                 gtype text;
     165        pt geometry;
     166        xmin float8;
     167        xmax float8;
     168        ymin float8;
     169        ymax float8;
     170        xrange float8;
     171        yrange float8;
     172        srid int;
     173        count integer := 0;
     174        bcontains boolean := FALSE;
     175        gtype text;
    173176    BEGIN
    174177        SELECT ST_GeometryType(geom)
    … …  
    177180            RAISE EXCEPTION 'Attempting to get random point in a non polygon geometry';
    178181        END IF;
    179                 SELECT ST_XMin(geom), ST_XMax(geom), ST_YMin(geom), ST_YMax(geom), ST_SRID(geom)
    180                 INTO xmin, xmax, ymin, ymax, srid;
    181                 SELECT xmax - xmin, ymax - ymin
    182                 INTO xrange, yrange;
    183 
    184                 WHILE count < nb LOOP
    185                         SELECT ST_SetSRID(ST_MakePoint(xmin + xrange * random(), ymin + yrange * random()), srid)
    186                         INTO pt;
    187                         SELECT ST_Contains(geom, pt)
    188                         INTO bcontains;
    189                         IF bcontains THEN
    190                                 count := count + 1;
    191                                 RETURN NEXT pt;
    192                         END IF;
    193                 END LOOP;
    194                 RETURN;
    195         END;
     182        SELECT ST_XMin(geom), ST_XMax(geom), ST_YMin(geom), ST_YMax(geom), ST_SRID(geom)
     183        INTO xmin, xmax, ymin, ymax, srid;
     184        SELECT xmax - xmin, ymax - ymin
     185        INTO xrange, yrange;
     186
     187        WHILE count < nb LOOP
     188            SELECT ST_SetSRID(ST_MakePoint(xmin + xrange * random(), ymin + yrange * random()), srid)
     189            INTO pt;
     190            SELECT ST_Contains(geom, pt)
     191            INTO bcontains;
     192            IF bcontains THEN
     193                count := count + 1;
     194                RETURN NEXT pt;
     195            END IF;
     196        END LOOP;
     197        RETURN;
     198    END;
    196199    $$
    197200    LANGUAGE 'plpgsql' IMMUTABLE STRICT;
    … …  
    201204
    202205{{{
    203         CREATE TABLE cariboupoints AS
    204         SELECT generate_series(1,1000) id,
    205                ST_RandomPoint(the_geom, 1000) the_geom
    206         FROM (SELECT ST_Transform(ST_Extent(rast::geometry), 32198) FROM srtm_tiled) foo;
     206    CREATE TABLE cariboupoints AS
     207    SELECT generate_series(1,1000) id,
     208           ST_RandomPoint(the_geom, 1000) the_geom
     209    FROM (SELECT ST_Transform(ST_Extent(rast::geometry), 32198)
     210          FROM srtm_tiled
     211         ) foo;
    207212}}}
    208213
    … …  
    210215
    211216{{{
    212         SELECT id, ST_AsBinary(the_geom)
    213         FROM cariboupoints;
     217    SELECT id, ST_AsBinary(the_geom)
     218    FROM cariboupoints;
    214219}}}
    215220
    … …  
    221226
    222227{{{
    223         CREATE TABLE cariboupoint_buffers AS
    224         SELECT id, ST_Buffer(the_geom, 1000) the_geom
    225         FROM cariboupoints;
     228    CREATE TABLE cariboupoint_buffers AS
     229    SELECT id, ST_Buffer(the_geom, 1000) the_geom
     230    FROM cariboupoints;
    226231}}}
    227232
    … …  
    229234
    230235{{{
    231         SELECT id, ST_AsBinary(the_geom)
    232         FROM cariboupoint_buffers;
     236    SELECT id, ST_AsBinary(the_geom)
     237    FROM cariboupoint_buffers;
    233238}}}
    234239
    … …  
    236241
    237242{{{
    238         CREATE TABLE cariboupoint_buffers_wgs AS
    239         SELECT id, ST_Transform(the_geom, 4326) the_geom
    240         FROM cariboupoint_buffers;
     243    CREATE TABLE cariboupoint_buffers_wgs AS
     244    SELECT id, ST_Transform(the_geom, 4326) the_geom
     245    FROM cariboupoint_buffers;
    241246}}}
    242247
    … …  
    244249
    245250{{{
    246         SELECT id, ST_AsBinary(the_geom)
    247         FROM cariboupoint_buffers_wgs;
     251    SELECT id, ST_AsBinary(the_geom)
     252    FROM cariboupoint_buffers_wgs;
    248253}}}
    249254
    … …  
    251256
    252257{{{
    253         CREATE TABLE cariboupoint_buffers_wgs AS
    254         SELECT id, ST_Transform(ST_Buffer(the_geom, 1000), 4326) the_geom
    255         FROM cariboupoints;
     258    CREATE TABLE cariboupoint_buffers_wgs AS
     259    SELECT id, ST_Transform(ST_Buffer(the_geom, 1000), 4326) the_geom
     260    FROM cariboupoints;
    256261}}}
    257262
    … …  
    259264
    260265{{{
    261         CREATE INDEX cariboupoint_buffers_wgs_geom_idx ON cariboupoint_buffers_wgs USING GIST (the_geom);
     266    CREATE INDEX cariboupoint_buffers_wgs_geom_idx ON cariboupoint_buffers_wgs USING GIST (the_geom);
    262267}}}
    263268
    … …  
    267272
    268273{{{
    269         CREATE TABLE caribou_srtm_inter AS
    270         SELECT id,
    271               (st_intersection(rast, the_geom)).geom the_geom,
    272               (st_intersection(rast, the_geom)).val
    273         FROM cariboupoint_buffers_wgs,
    274              srtm_tiled
    275         WHERE st_intersects(rast, the_geom);
     274    CREATE TABLE caribou_srtm_inter AS
     275    SELECT id,
     276        (st_intersection(rast, the_geom)).geom the_geom,
     277        (st_intersection(rast, the_geom)).val
     278    FROM cariboupoint_buffers_wgs,
     279        srtm_tiled
     280    WHERE st_intersects(rast, the_geom);
    276281}}}
    277282       
    … …  
    279284
    280285{{{
    281         CREATE TABLE caribou_srtm_inter AS
    282         SELECT id,
    283                (gv).geom the_geom,
    284                (gv).val
    285         FROM (SELECT id,
    286                      st_intersection(rast, the_geom) gv
    287               FROM srtm_tiled,
    288                    cariboupoint_buffers_wgs
    289               WHERE st_intersects(rast, the_geom)
    290              ) foo;
     286    CREATE TABLE caribou_srtm_inter AS
     287    SELECT id,
     288           (gv).geom the_geom,
     289           (gv).val
     290    FROM (SELECT id,
     291                 st_intersection(rast, the_geom) gv
     292          FROM srtm_tiled,
     293               cariboupoint_buffers_wgs
     294          WHERE st_intersects(rast, the_geom)
     295         ) foo;
    291296}}}
    292297       
    … …  
    296301
    297302{{{
    298         SELECT id, val, ST_AsBinary(the_geom)
    299         FROM caribou_srtm_inter;
     303    SELECT id, val, ST_AsBinary(the_geom)
     304    FROM caribou_srtm_inter;
    300305}}}
    301306
    … …  
    308313
    309314{{{
    310         CREATE TABLE result01 AS
    311         SELECT id,
     315    CREATE TABLE result01 AS
     316    SELECT id,
    312317               sum(st_area(ST_Transform(the_geom, 32198)) * val) / sum(st_area(ST_Transform(the_geom, 32198))) as meanelev
    313         FROM caribou_srtm_inter1
    314         GROUP BY id
    315         ORDER BY id;
    316 }}}
     318    FROM caribou_srtm_inter1
     319    GROUP BY id
     320    ORDER BY id;
     321}}}