raster2pgsql is not creating a raster table in postgis

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

raster2pgsql is not creating a raster table in postgis

garret
Hello,

Im am attempting to follow along in the book Postgis Cookbook, and am importing a raster via raster2pgsql.  So far, unsuccesfully.

the original code shows: (Ive altered it only to fit my current set up)
raster2pgsql -I -C -M -F -t 100x100 worldclim/tmax1.bil chp1.tmax01 | psql -d postgis_cookbook -U me -f tmax1.sql

But I get an error stating, Processing 1/1: tmax1.bil tmax1.sql: No such file or directory.

So, Ive altered the query to this:
raster2pgsql -I -C -M -F -t 100x100 tmax1.bil chp1.tmax1 | psql -d postgisCookbook -U gisuser

the output is:
Processing 1/1: tmax1.bil
BEGIN
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
CREATE INDEX
ANALYZE
NOTICE:  Adding SRID constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding scale-X constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding scale-Y constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding blocksize-X constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding blocksize-Y constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding alignment constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding number of bands constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding pixel type constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding nodata value constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding out-of-database constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding maximum extent constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
 addrasterconstraints
----------------------
 t
(1 row)

COMMIT
VACUUM

=======================================

So it seems to me to have completed successfully. But then when I go to check the raster in QGIS I only see a table with no geometry.

I have checked the process of using raster2pgsql with many sources and this seems to be the recommended way. Has anyone had this same experience, or know how to fix this.

BTW, Ive also tried this using the 2 step process without the pipe and I getting the same result.

Here's a quick screen shot of the table in QGIS.



Im using Ubuntu 14.04, postgis 2.1, QGIS 2.6.1

I appreciate all the help.

Garret
Reply | Threaded
Open this post in threaded view
|

Re: raster2pgsql is not creating a raster table in postgis

Bborie Park-2
Comments interspersed...

On Wed, Dec 31, 2014 at 12:40 PM, garret <[hidden email]> wrote:
Hello,

Im am attempting to follow along in the book Postgis Cookbook, and am
importing a raster via raster2pgsql.  So far, unsuccesfully.

*the original code shows:* (Ive altered it only to fit my current set up)
raster2pgsql -I -C -M -F -t 100x100 worldclim/tmax1.bil chp1.tmax01 | psql
-d postgis_cookbook -U me -f tmax1.sql

*But I get an error stating, Processing 1/1: tmax1.bil tmax1.sql: No such
file or directory.*


This is happening because you have "-f tmax1.sql" specified to psql.

 
*So, Ive altered the query to this:*
raster2pgsql -I -C -M -F -t 100x100 tmax1.bil chp1.tmax1 | psql -d
postgisCookbook -U gisuser

*the output is:*
Processing 1/1: tmax1.bil
BEGIN
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1

snip...
 
 addrasterconstraints
----------------------
 t
(1 row)

COMMIT
VACUUM

=======================================

So it seems to me to have completed successfully. But then when I go to
check the raster in QGIS I only see a table with no geometry.


raster loading through raster2pgsql does not generate any geometries. 
 
You're better off checking the metadata of the loaded rasters...

SELECT ST_Metadata(rast) FROM chp1.tmax1 LIMIT 1

And...

SELECT ST_BandMetadata(rast, 1) FROM chp1.tmax1 LIMIT 1

-bborie

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: raster2pgsql is not creating a raster table in postgis

garret
Hi Borie,

Thank you for you quick response, especially on a New Years Eve.

I took a look at the meta data,

Here is what I get:

    SELECT ST_Metadata(rast) FROM chp1.tmax1 LIMIT 1;
    "(-180,90,100,100,0.166666666666667,-0.166666666666667,0,0,4326,1)"

   
    SELECT ST_BandMetadata(rast, 1) FROM chp1.tmax1 LIMIT 1;
    "(16BSI,-9999,f,)"

Since this is my first time using rasters in postgis, I dont know if this is typical output or not.

fyi, here is the gdalinfo output from the original file:

Files: tmax1.bil
       tmax1.hdr
Size is 2160, 900
Coordinate System is:
GEOGCS["WGS 84",
    DATUM["WGS_1984",
        SPHEROID["WGS 84",6378137,298.257223563,
            AUTHORITY["EPSG","7030"]],
        TOWGS84[0,0,0,0,0,0,0],
        AUTHORITY["EPSG","6326"]],
    PRIMEM["Greenwich",0,
        AUTHORITY["EPSG","8901"]],
    UNIT["degree",0.0174532925199433,
        AUTHORITY["EPSG","9108"]],
    AUTHORITY["EPSG","4326"]]
Origin = (-180.000000000000057,90.000000000000000)
Pixel Size = (0.166666666666667,-0.166666666666667)
Corner Coordinates:
Upper Left  (-180.0000000,  90.0000000) (180d 0' 0.00"W, 90d 0' 0.00"N)
Lower Left  (-180.0000000, -60.0000000) (180d 0' 0.00"W, 60d 0' 0.00"S)
Upper Right ( 180.0000000,  90.0000000) (180d 0' 0.00"E, 90d 0' 0.00"N)
Lower Right ( 180.0000000, -60.0000000) (180d 0' 0.00"E, 60d 0' 0.00"S)
Center      (   0.0000000,  15.0000000) (  0d 0' 0.00"E, 15d 0' 0.00"N)
Band 1 Block=2160x1 Type=Int16, ColorInterp=Undefined
  Min=-478.000 Max=418.000
  NoData Value=-9999

the statement; SELECT * FROM raster_columns;
comes out a little odd, but again Im not sure what to expect. The output is long but I can post a summary of it if you think it would be helpful.

So Im not sure where to go from here. Any recommendations?

Thank you,
Garret
Reply | Threaded
Open this post in threaded view
|

Re: raster2pgsql is not creating a raster table in postgis

Pierre Racine-2
Try

SELECT (ST_Metadata(rast)).* FROM chp1.tmax1 LIMIT 1;

instead. It's more verbose. Your raster is there. Don't worry.

Pierre
________________________________________
From: [hidden email] [[hidden email]] On Behalf Of garret [[hidden email]]
Sent: Sunday, January 04, 2015 11:04 PM
To: [hidden email]
Subject: Re: [postgis-users] raster2pgsql is not creating a raster table in     postgis

Hi Borie,

Thank you for you quick response, especially on a New Years Eve.

I took a look at the meta data,

Here is what I get:

    SELECT ST_Metadata(rast) FROM chp1.tmax1 LIMIT 1;
    "(-180,90,100,100,0.166666666666667,-0.166666666666667,0,0,4326,1)"


    SELECT ST_BandMetadata(rast, 1) FROM chp1.tmax1 LIMIT 1;
    "(16BSI,-9999,f,)"

Since this is my first time using rasters in postgis, I dont know if this is
typical output or not.

fyi, here is the gdalinfo output from the original file:

Files: tmax1.bil
       tmax1.hdr
Size is 2160, 900
Coordinate System is:
GEOGCS["WGS 84",
    DATUM["WGS_1984",
        SPHEROID["WGS 84",6378137,298.257223563,
            AUTHORITY["EPSG","7030"]],
        TOWGS84[0,0,0,0,0,0,0],
        AUTHORITY["EPSG","6326"]],
    PRIMEM["Greenwich",0,
        AUTHORITY["EPSG","8901"]],
    UNIT["degree",0.0174532925199433,
        AUTHORITY["EPSG","9108"]],
    AUTHORITY["EPSG","4326"]]
Origin = (-180.000000000000057,90.000000000000000)
Pixel Size = (0.166666666666667,-0.166666666666667)
Corner Coordinates:
Upper Left  (-180.0000000,  90.0000000) (180d 0' 0.00"W, 90d 0' 0.00"N)
Lower Left  (-180.0000000, -60.0000000) (180d 0' 0.00"W, 60d 0' 0.00"S)
Upper Right ( 180.0000000,  90.0000000) (180d 0' 0.00"E, 90d 0' 0.00"N)
Lower Right ( 180.0000000, -60.0000000) (180d 0' 0.00"E, 60d 0' 0.00"S)
Center      (   0.0000000,  15.0000000) (  0d 0' 0.00"E, 15d 0' 0.00"N)
Band 1 Block=2160x1 Type=Int16, ColorInterp=Undefined
  Min=-478.000 Max=418.000
  NoData Value=-9999

the statement; SELECT * FROM raster_columns;
comes out a little odd, but again Im not sure what to expect. The output is
long but I can post a summary of it if you think it would be helpful.

So Im not sure where to go from here. Any recommendations?

Thank you,
Garret



--
View this message in context: http://postgis.17.x6.nabble.com/raster2pgsql-is-not-creating-a-raster-table-in-postgis-tp5007520p5007525.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: raster2pgsql is not creating a raster table in postgis

garret
Thanks for the help Pierre and Borie

after doing what you had suggested, as well as testing additional raster files, it seemed to me that I may be barking up the wrong tree. So I looked into any current QGIS issues with PostGIS rasters and found this:

http://hub.qgis.org/issues/10871

So I opened DB Manager plugin (which I dont use typically) and viola! the raster appeared.  So it seems that in QGIS version 2.6.1, you can not add postgis rasters via the "Browser", nor the "Add PostGIS Layers" button - they both end up only adding a table to the Layer.

Thanks again for you patience and help,
Garret