[GIS] Loading raster data to postgis

postgisrasterraster2pgsql

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:

enter image description 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.

Best Answer

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

Related Solutions

[GIS] R – Plotting shapefile with geographical (raster) data

To me there are really 2 unrelated questions here.

A) What is the projection of my shapefile and how can I merge/plot it with another layer.

B) What is the form of these raster data, and why are there in a table when they could actually be a grid?

There's a lot of possibilities here and not enough to go on. I'd like to know the answers to these:

I would find out the projection of the shapefile, and you cannot do that with maptools::readShapeLines.

1) read it with rgdal and transform:

library(rgdal)
ned.lines <- readOGR("d:/readShapefiles", "NLD_adm/NLD_adm1")

## this will tell you what the projection is
## find out and tell us
 proj4string(ned.lines)

You might find this makes it work with your grid data, but there's a lot of guessing here:

plot(spTransform(ned.lines, CRS("+proj=longlat +datum=WGS84")))

2) Find out the actual projection and get back to us, you might need this if you cannot install rgdal.

readLines("d:/readShapefiles/NLD_adm/NLD_adm1.prj")

(Note that this is reading text lines, not geometry).

If that fails because the file doesn't exist, then there's only more guesses we can do. It looks like you don't have a grid of the radar data, so I would go back to the source and get an actual grid, or you'll need to rasterize the data from the irregular points.

QGIS PostGIS – Troubleshooting Very Slow Loading of PostGIS Raster Layer in QGIS

As User30184 pointed out in the comments I was missing the -r switch.

After adding it I got the regular blocking working for all the pyramid layers, but not for the main layer as it was reported by raster2pgsql to be bigger than the maximum 65535x65535 pixels. With some more digging I found out that there is a ticket about this problem, which is planed to be fixed in PostGIS 3.0.

Using:

SELECT AddRasterConstraints('public', 'rasters'::name, 'rast'::name);

PostGIS created constraints that are appropriate for my data.

QGIS was now loading the raster quicker, but still it took a long time. Checking the raster_columns table I noticed that the "extent" column is NULL for all the tables. Using SQL commands:

ALTER TABLE rasters VALIDATE CONSTRAINT enforce_max_extent_rast;
ALTER TABLE o_2_rasters VALIDATE CONSTRAINT enforce_max_extent_rast;
ALTER TABLE o_4_rasters VALIDATE CONSTRAINT enforce_max_extent_rast;
ALTER TABLE o_8_rasters VALIDATE CONSTRAINT enforce_max_extent_rast;
ALTER TABLE o_16_rasters VALIDATE CONSTRAINT enforce_max_extent_rast;
ALTER TABLE o_32_rasters VALIDATE CONSTRAINT enforce_max_extent_rast;
ALTER TABLE o_64_rasters VALIDATE CONSTRAINT enforce_max_extent_rast;
ALTER TABLE o_128_rasters VALIDATE CONSTRAINT enforce_max_extent_rast;
ALTER TABLE o_256_rasters VALIDATE CONSTRAINT enforce_max_extent_rast;

PostGIS calculated the extents and QGIS loaded my rasters table for 1 second!

Thank you User30184 for the hint about -r and for the link to the documentation.

Best Answer

Related Solutions

[GIS] R – Plotting shapefile with geographical (raster) data

QGIS PostGIS – Troubleshooting Very Slow Loading of PostGIS Raster Layer in QGIS

Related Question