[postgis-users] import raster

[Bborie Park]

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.

You can add the raster constraints that you want added later with the
AddRasterConstraints() function.

-bborie


On Thu, Feb 27, 2014 at 8:31 AM, Eloi <eloi at openmailbox.org> wrote:

> Thanks bborie,
> It is possible to do that in the import statement or just after the import?
>
> Cheers,
>
> Eloi
>
> On February 27, 2014 5:11:23 PM CET, Bborie Park <dustymugs at gmail.com>
> wrote:
> >Do you need the maximum extent constraint? Generally, it is safe to
> >disable
> >the maximum extent constraint.
> >
> >-bborie
> >
> >
> >On Thu, Feb 27, 2014 at 7:06 AM, Eloi Ribeiro <eloi at openmailbox.org>
> >wrote:
> >
> >> On 2014-02-27 11:19, Rémi Cura wrote:
> >>>
> >>>> Maybe you can try to separate raster2pgsql and psql .
> >>>>
> >>>> First use raster2pgsqland output it to a file on you filesystem.
> >>>> Then use psql
> >>>> to execute this file.
> >>>>
> >>>>  You could see how much time each step takes, and how much space
> >the
> >>>> sql temp file is.
> >>>>
> >>>
> >> The sql file went up to 6.6 GB.
> >>
> >>
> >>  Maybe you could use the   "-Y" flag to make it faster?
> >>>>
> >>>
> >> (...)
> >> INSERT 0 1
> >> INSERT 0 1
> >> INSERT 0 1
> >>
> >> # Til here with the -Y flag was much faster, jumped from 1h 30 min to
> >only
> >> 13 min.
> >>
> >> CREATE INDEX
> >> ANALYZE
> >> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
> >SRID
> >> constraint
> >> CONTEXT:  PL/pgSQL function "addrasterconstraints" line 53 at RETURN
> >> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
> >scale-X
> >> constraint
> >> CONTEXT:  PL/pgSQL function "addrasterconstraints" line 53 at RETURN
> >> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
> >scale-Y
> >> constraint
> >> CONTEXT:  PL/pgSQL function "addrasterconstraints" line 53 at RETURN
> >> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
> >> blocksize-X constraint
> >> CONTEXT:  PL/pgSQL function "addrasterconstraints" line 53 at RETURN
> >> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
> >> blocksize-Y constraint
> >> CONTEXT:  PL/pgSQL function "addrasterconstraints" line 53 at RETURN
> >> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
> >> alignment constraint
> >> CONTEXT:  PL/pgSQL function "addrasterconstraints" line 53 at RETURN
> >> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
> >number
> >> of bands constraint
> >> CONTEXT:  PL/pgSQL function "addrasterconstraints" line 53 at RETURN
> >> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
> >pixel
> >> type constraint
> >> CONTEXT:  PL/pgSQL function "addrasterconstraints" line 53 at RETURN
> >> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
> >nodata
> >> value constraint
> >> CONTEXT:  PL/pgSQL function "addrasterconstraints" line 53 at RETURN
> >> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
> >> out-of-database constraint
> >> CONTEXT:  PL/pgSQL function "addrasterconstraints" line 53 at RETURN
> >>
> >> # Til here took about 30 minutes. And from here starts the slow part.
> >>
> >> psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Unable to
> >get
> >> the extent of a sample raster. Attempting memory efficient (slower)
> >approach
> >> CONTEXT:  PL/pgSQL function "addrasterconstraints" line 100 at
> >assignment
> >> PL/pgSQL function "addrasterconstraints" line 53 at RETURN
> >>
> >> # 3h 30min have passed and still the same.
> >>
> >> The message is clear and searching for this message I came across
> >with the
> >> code I think is executing [1].
> >> There I can see that when this notice is raised it try again with
> >function
> >> 'st_memunion' (memory friendly but slower) after failing with
> >'st_collect'.
> >> The machine where I am running this has 16 GB RAM and even so it was
> >not
> >> able to preform this task with 'st_collect'.
> >>
> >> Any suggestions?
> >>
> >> Thanks.
> >>
> >> Cheers,
> >>
> >> Eloi Ribeiro
> >>
> >> [1] https://git.osgeo.org/postgis/tags/2.0.3/raster/rt_pg/
> >> rtpostgis.sql.in.c
> >>
> >>
> >>
> >>
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at lists.osgeo.org
> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >>
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140227/dfd0e206/attachment.html>