Loading raster data to postgis

Question

I'm a newbie in GIS technology and need some help in loading a rainfall radar raster data into PostGIS. The raster image is shown here:

What I need is to store in the database the radar reflectivity values. I supose that a previous image processing is needed, in order to extract the background and get an image with only the reflectivity pixels represented by colors. No problem with this, I can easily convert it. It uses a polar stereographic projection and I know the coordinates of the image center.

At this point, I'm wondering which is the best way to achieve this. A quick look up in google points me to use the raster2pgsql process.

My questions are:

1) Is really raster2pgsql the best tool for my purposes?

2) Could you give me some help in how to use raster2pgsql to achieve this?

3) Is there any easy way to check the projection of the image? I mean, is there any tool in which i can easily charge the image as a layer and try to match it to a projection to verify that the image projection is the expected?

Thanks in advance.

Answer 1

The biggest problem as I see it in your problem is your data, using a valid SRID, before the actual insertion. If you can achieve that using the raster2pgsql program is pretty straightforward.

raster2pgsql  -s <srid>       #The srid you chose.
              -t WIDTHxHEIGH  #Dimensions of each block that contains your data. usually, 50x50 for vector/raster analysis is good.
              -f              #Name of raster column. Usually named rast
              -I              #Create Gist index. You want this for using spatial indexes.
              -Y              #Use copy instead of insert when importing the data. Much faster. harder to debug if there are problems.

So keeping with the above, when you have your raster to import it just run

raster2pgsql -s <srid> -t 50x50 -f -I -Y myRaster.tif rasterSchema.RasterName > raster.sql`
psql -h localhost -U someuser -d mydb -f raster.sql

Answer 2

1.) Is raster2pgsql the best loading tool?

That depends on your style. I like to figure out the options for a procedure and put the configuration in a script. Even if it is a one time load, I have the file as reference later. QGIS could be another option.

2.) How do you use raster2pgsql?

I am using PostGIS 2.1. You have to understand the limits of what the tool will do. Make sure that you include the

a.) -s with your srid

b.) -F option so that the name of the file that you loaded is included with your data. You will need to have a file naming scheme to find a file in a database query. Otherwise, you will only have a rid and rast column in your table.

c.) -I to generate an index

d.) -C to generate the constraints

You will have to adjust these and other raster2pgsql options for your needs.

3.) Is there any easy way to check the projection of the image?

Before the file is loaded into the database you can use

gdalinfo radar_filename.tif

to find the SRID for the -s option. After the file has been loaded into the database you can use

gdalinfo PG":host=localhost port=5432 dbname=my_dbname user=my_user password=my_password schema=my_schema table=my_raster_table_name"

The database and file use of gdalinfo should provide the same information. You can make a visual check of the raster with QGIS like tools by loading other vector data around Spain. Consistently using SRIDs solves most alignment issues.

Answer 3

For question 3, the simplest would be to load the raster into QGIS just as a regular raster and check that it is aligned with something using a known projection. Whether it is in PostGIS or not should not make a difference in this regard.