Database Reference
In-Depth Information
Comparingthetextof
srtext
tothePRISMraster'smetadataspatialattributes,we
find that the raster is in WGS72 (SRID 4322).
You can load the PRISM rasters into the
chap5.prism
table with
raster2pgsql
.
> raster2pgsql -s 4322 -t 100x100 -F -I -C -Y
C:\postgis_cookbook\data\chap5\PRISM\us_tmin_2012.*.asc
chap5.prism | psql -d postgis_cookbook
The
raster2pgsql
command is called with the following flags:
•
-s
: This flag assignsSRID
4322
to the imported rasters.
•
-t
:Thisflagdenotesthetilesize.Itchunkstheimportedrastersintosmaller
andmoremanageablepieces;eachrecordaddedtothetablewillbeatmost
100 x 100 pixels.
•
-F
: This flag adds a column to the table and fills it with the raster's filename.
•
-I
: This flag creates a GIST spatial index on the table's raster column.
•
-C
:Thisflagappliesthestandardsetofconstraintsonthetable.Thestand-
ardsetofconstraintsincludeschecksfordimension,scale,skew,upper-left
coordinate, and SRID.
•
-Y
: This flag instructs raster2pgsql to use
COPY
statements instead of
INSERT
statements.
COPY
is typically faster than
INSERT
.
Thereisareasonwhywepassed
-F
to
raster2pgsql
.Ifyoulookatthefilenames
ofthePRISMrasters,you'llnotetheyearandmonth.So,let'sconvertthevaluein
the
filename
column to a date in the table.
ALTER TABLE chap5.prism ADD COLUMN month_year
DATE;
UPDATE chap5.prism SET month_year = (
split_part(split_part(filename, ''.'',
1), ''_'', 3) || ''-'' ||
split_part(filename, ''.'', 2) || ''-01''
)::date;