Raster import is causing Postgres to close

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

Raster import is causing Postgres to close

JamesH
This post was updated on .
UPDATE: This issue is now solved. The Problem was the size of the sql file I was producing was too large, solved this by cutting down the image to a smaller one and the sql file loaded and ran successfully.

Hi all,

I am trying to import a Thermal Imagery 3-Band TIFF (approx. 27MB).

Currently in raster2pgsql I am using:
raster2pgsql -s 27700 -I -C C:\Users\Data\thermal.tif > C:\Users\Data\Thermal.sql

This executes fine in CMD, but when I open the sql file in Postgres it doesn't load and causes Postgres to stop working and close.
I thought perhaps I needed to tile it, so I tried breaking it into 64x64 tiles but this had the same result.

Can anyone advise on what could be causing this?

Kind Regards,
James
GIS Undergraduate
Reply | Threaded
Open this post in threaded view
|

Re: Raster import is causing Postgres to close

Pierre Racine-2
When you say " I open the sql file in Postgres ", what do you mean exactly? How do you load the file in the db? Any error message?

Pierre

> -----Original Message-----
> From: [hidden email] [mailto:postgis-users-
> [hidden email]] On Behalf Of JamesH
> Sent: Thursday, April 19, 2012 7:51 AM
> To: [hidden email]
> Subject: [postgis-users] Raster import is causing Postgres to close
>
> Hi all,
>
> I am trying to import a Thermal Imagery 3-Band TIFF (approx. 27MB).
>
> Currently in raster2pgsql I am using:
> raster2pgsql -s 27700 -I -C C:\Users\Data\thermal.tif >
> C:\Users\Data\Thermal.sql
>
> This executes fine in CMD, but when I open the sql file in Postgres it
> doesn't load and causes Postgres to stop working and close.
> I thought perhaps I needed to tile it, so I tried breaking it into 64x64
> tiles but this had the same result.
>
> Can anyone advise on what could be causing this?
>
> Kind Regards,
> James
>
> -----
> GIS Undergraduate
> --
> View this message in context: http://postgis.17.n6.nabble.com/Raster-import-
> is-causing-Postgres-to-close-tp4897915p4897915.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Raster import is causing Postgres to close

JamesH
"When you say " I open the sql file in Postgres ", what do you mean exactly? How do you load the file in the db?"
In the SQL query window for the database, I select File > Open and then select the sql file. Normally, once I've done this the code displays in the window and I then Execute the query to create the new table.

"Any error message?"
Yes, a pop-up that says 'pgAdmin III - PostgreSQL Tools has stopped working. A problem
caused the program to stop working correctly. Windows will close the program and notify you if a solution is available.'

James
GIS Undergraduate
Reply | Threaded
Open this post in threaded view
|

Re: Raster import is causing Postgres to close

Burgholzer, Robert (DEQ)
If by a pop-up saying "PostgreSQL Tools has stopped RESPONDING", it may be the shear size of your file.  On windows, I have observed such a phenomenon when trying to import fairly large postgresql files (the size of yours ranks up there as fairly large).

To verify if this was the cause, excerpt your file, extracting the first 20 or so inserts and see if that will run successfully.  If so, you can try ignorning the "not responding" message and hope that it completes, or split the file manually, or by some other means into several smaller files.

HTH,
r.b.
________________________________________
From: [hidden email] [[hidden email]] on behalf of JamesH [[hidden email]]
Sent: Thursday, April 19, 2012 11:05 AM
To: [hidden email]
Subject: Re: [postgis-users] Raster import is causing Postgres to close

"When you say " I open the sql file in Postgres ", what do you mean exactly?
How do you load the file in the db?"
In the SQL query window for the database, I select File > Open and then
select the sql file. Normally, once I've done this the code displays in the
window and I then Execute the query to create the new table.

"Any error message?"
Yes, a pop-up that says 'pgAdmin III - PostgreSQL Tools has stopped working.
A problem
caused the program to stop working correctly. Windows will close the program
and notify you if a solution is available.'

James

-----
GIS Undergraduate
--
View this message in context: http://postgis.17.n6.nabble.com/Raster-import-is-causing-Postgres-to-close-tp4897915p4898506.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Raster import is causing Postgres to close

Pierre Racine-2
In reply to this post by JamesH
> "When you say " I open the sql file in Postgres ", what do you mean exactly?
> How do you load the file in the db?"
> In the SQL query window for the database, I select File > Open and then
> select the sql file. Normally, once I've done this the code displays in the
> window and I then Execute the query to create the new table.
>
> "Any error message?"
> Yes, a pop-up that says 'pgAdmin III - PostgreSQL Tools has stopped working.
> A problem
> caused the program to stop working correctly. Windows will close the program
> and notify you if a solution is available.'

First you have to differentiate pgAdmin from PostgreSQL. The problem does not occur in PostgreSQL. It occurs in pgAdmin... Your SQL file is too big to be handled by pgAdmin.

Just go back to your shell where you did raster2pgsql and do (replacing yourpostgresrole and yourDatabase with the correct values)

psql -U yourpostgresrole -f "C:\Users\Data\Thermal.sql" yourDatabase

That's the way to load (or execute) large sql files into PostgreSQL.

Pierre
_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Raster import is causing Postgres to close

JamesH
This post was updated on .
Thanks to both - definitely the error was caused by the Raster file I was trying to load was too large - PgAdmin could not handle the SQL file.

I have cut the Raster down to a smaller area and am using that instead.
UPDATE: Had not thought to use psql - it works very well! Thanks.

Kind Regards,
James
GIS Undergraduate