<div dir="ltr">When using raster2pgsql, you wouldn't pass the -C flag. If you're going to use the generated SQL file, remove the SELECT AddRasterConstraints... line.<div><br></div><div>You can add the raster constraints that you want added later with the AddRasterConstraints() function.</div>
<div><br></div><div>-bborie</div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Thu, Feb 27, 2014 at 8:31 AM, Eloi <span dir="ltr"><<a href="mailto:eloi@openmailbox.org" target="_blank">eloi@openmailbox.org</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Thanks bborie,<br>
It is possible to do that in the import statement or just after the import?<br>
<br>
Cheers,<br>
<br>
Eloi<br>
<div class="HOEnZb"><div class="h5"><br>
On February 27, 2014 5:11:23 PM CET, Bborie Park <<a href="mailto:dustymugs@gmail.com">dustymugs@gmail.com</a>> wrote:<br>
>Do you need the maximum extent constraint? Generally, it is safe to<br>
>disable<br>
>the maximum extent constraint.<br>
><br>
>-bborie<br>
><br>
><br>
>On Thu, Feb 27, 2014 at 7:06 AM, Eloi Ribeiro <<a href="mailto:eloi@openmailbox.org">eloi@openmailbox.org</a>><br>
>wrote:<br>
><br>
>> On 2014-02-27 11:19, Rémi Cura wrote:<br>
>>><br>
>>>> Maybe you can try to separate raster2pgsql and psql .<br>
>>>><br>
>>>> First use raster2pgsqland output it to a file on you filesystem.<br>
>>>> Then use psql<br>
>>>> to execute this file.<br>
>>>><br>
>>>> You could see how much time each step takes, and how much space<br>
>the<br>
>>>> sql temp file is.<br>
>>>><br>
>>><br>
>> The sql file went up to 6.6 GB.<br>
>><br>
>><br>
>> Maybe you could use the "-Y" flag to make it faster?<br>
>>>><br>
>>><br>
>> (...)<br>
>> INSERT 0 1<br>
>> INSERT 0 1<br>
>> INSERT 0 1<br>
>><br>
>> # Til here with the -Y flag was much faster, jumped from 1h 30 min to<br>
>only<br>
>> 13 min.<br>
>><br>
>> CREATE INDEX<br>
>> ANALYZE<br>
>> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Adding<br>
>SRID<br>
>> constraint<br>
>> CONTEXT: PL/pgSQL function "addrasterconstraints" line 53 at RETURN<br>
>> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Adding<br>
>scale-X<br>
>> constraint<br>
>> CONTEXT: PL/pgSQL function "addrasterconstraints" line 53 at RETURN<br>
>> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Adding<br>
>scale-Y<br>
>> constraint<br>
>> CONTEXT: PL/pgSQL function "addrasterconstraints" line 53 at RETURN<br>
>> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Adding<br>
>> blocksize-X constraint<br>
>> CONTEXT: PL/pgSQL function "addrasterconstraints" line 53 at RETURN<br>
>> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Adding<br>
>> blocksize-Y constraint<br>
>> CONTEXT: PL/pgSQL function "addrasterconstraints" line 53 at RETURN<br>
>> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Adding<br>
>> alignment constraint<br>
>> CONTEXT: PL/pgSQL function "addrasterconstraints" line 53 at RETURN<br>
>> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Adding<br>
>number<br>
>> of bands constraint<br>
>> CONTEXT: PL/pgSQL function "addrasterconstraints" line 53 at RETURN<br>
>> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Adding<br>
>pixel<br>
>> type constraint<br>
>> CONTEXT: PL/pgSQL function "addrasterconstraints" line 53 at RETURN<br>
>> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Adding<br>
>nodata<br>
>> value constraint<br>
>> CONTEXT: PL/pgSQL function "addrasterconstraints" line 53 at RETURN<br>
>> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Adding<br>
>> out-of-database constraint<br>
>> CONTEXT: PL/pgSQL function "addrasterconstraints" line 53 at RETURN<br>
>><br>
>> # Til here took about 30 minutes. And from here starts the slow part.<br>
>><br>
>> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Unable to<br>
>get<br>
>> the extent of a sample raster. Attempting memory efficient (slower)<br>
>approach<br>
>> CONTEXT: PL/pgSQL function "addrasterconstraints" line 100 at<br>
>assignment<br>
>> PL/pgSQL function "addrasterconstraints" line 53 at RETURN<br>
>><br>
>> # 3h 30min have passed and still the same.<br>
>><br>
>> The message is clear and searching for this message I came across<br>
>with the<br>
>> code I think is executing [1].<br>
>> There I can see that when this notice is raised it try again with<br>
>function<br>
>> 'st_memunion' (memory friendly but slower) after failing with<br>
>'st_collect'.<br>
>> The machine where I am running this has 16 GB RAM and even so it was<br>
>not<br>
>> able to preform this task with 'st_collect'.<br>
>><br>
>> Any suggestions?<br>
>><br>
>> Thanks.<br>
>><br>
>> Cheers,<br>
>><br>
>> Eloi Ribeiro<br>
>><br>
>> [1] <a href="https://git.osgeo.org/postgis/tags/2.0.3/raster/rt_pg/" target="_blank">https://git.osgeo.org/postgis/tags/2.0.3/raster/rt_pg/</a><br>
>> rtpostgis.sql.in.c<br>
>><br>
>><br>
>><br>
>><br>
>> _______________________________________________<br>
>> postgis-users mailing list<br>
>> <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
>> <a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
>><br>
<br>
</div></div><span class="HOEnZb"><font color="#888888">--<br>
Sent from my Android device with K-9 Mail. Please excuse my brevity.<br>
</font></span><div class="HOEnZb"><div class="h5">_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></div></div></blockquote></div><br></div>