[postgis-users] Subject: Re: raster2pgsql 2.1.1 on Windows 7 64bit - ERREUR: erreur de syntaxe

[Guillaume Drolet]

Hi Mateusz,

Thanks for helping. What you suggested, i.e. outputting raster2pgsql
to an SQL file instead of piping it to psql, I have done already. I
also posted examples of where the lines are broken in previous post
(see also Jean-Daniel Sylvain's older post on the same topic).

It doesn't seem to break on non-ASCII characters. For example, this
one breaks in the middle of the file name at line 168:

...
INSERT INTO "mod09a1.a2000049.h12v04.005.2006268184041_sur_refl_b01"
("rast","filename") VALUES
('0100000100A544B7031A11713FA544B7031A1171BF86BE196C857752C095AFEE8CEE6E474000000000000000000000000000000000E610000032001F008700000000004D4F44303941312E41323030303034392E6831327630342E3030352E323030363236383138343034315F7375725F7265666C5F6230312E74696600'::raster,'MOD09A1.A2000049.h12v04.005.2006268184041_sur_refl_b01.tif');
INSERT INTO "mod09a1.a2000049.h12v04.005.2006268184041_sur_refl_b01"
("rast","filename") VALUES
('0100000100A544B7031A11713FA544B7031A1171BF59D7C60F306A52C095AFEE8CEE6E474000000000000000000000000000000000E610000032001F008700000000004D4F44303941312E41323030303034392E6831327630342E3030352E323030363236383138343034315F7375725F7265666C5F6230312E74696600'::raster,'MOD09A1.A2000049.h12v04.005.2006268184041_sur_refl_b01.tif');
INSERT INTO "mod09a1.a2000049.h12v04.005.2006268184041_sur_refl_b01"
("rast","filename") VALUES
('0100000100A544B7031A11713FA544B7031A1171BF2BF073B3DA5C52C095AFEE8CEE6E474000000000000000000000000000000000E610000032001F008700000000004D4F44303941312E41323030303034392E6831327630342E3030352E323030363236383138343034315F7375725F7265666C5F6230312E74696600'::raster,'MOD09A1.A2000049.h12v04.005.2006268184041_sur_refl_b01.tif');
INSERT INTO "mod09a1.a2000049.h12v04.005.20062

Or, this one (same tif file; I only shortened the filename) breaks in
the middle of a cell (or tile?) representation, still at line 168:

...
INTO "mod09a1.a2000049.sur_refl_b01" ("rast","filename") VALUES
('0100000100A544B7031A11713FA544B7031A1171BF86BE196C857752C095AFEE8CEE6E474000000000000000000000000000000000E610000032001F008700000000004D4F44303941312E41323030303034392E7375725F7265666C5F6230312E74696600'::raster,'MOD09A1.A2000049.sur_refl_b01.tif');
INSERT INTO "mod09a1.a2000049.sur_refl_b01" ("rast","filename") VALUES
('0100000100A544B7031A11713FA544B7031A1171BF59D7C60F306A52C095AFEE8CEE6E474000000000000000000000000000000000E610000032001F008700000000004D4F44303941312E41323030303034392E7375725F7265666C5F6230312E74696600'::raster,'MOD09A1.A2000049.sur_refl_b01.tif');
INSERT INTO "mod09a1.a2000049.sur_refl_b01" ("rast","filename") VALUES
('0100000100A544B7031A11713FA544B7031A1171BF2BF073B3DA5C52C095AFEE8CEE6E474000000000000000000000000000000000E610000032001F008700000000004D4F44303941312E41323030303034392E7375725F7265666C5F6230312E74696600'::raster,'MOD09A1.A2000049.sur_refl_b01.tif');
INSERT INTO "mod09a1.a2000049.sur_refl_b01" ("rast","filename") VALUES
('0100000100A544B7031A11713FA544B7031A117

This is VERY frustrating and I hope an answer will be found!

Guillaume


Message: 2
Date: Thu, 24 Apr 2014 21:43:43 +0200
From: Mateusz ?oskot <mateusz at loskot.net>
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org
>
Subject: Re: [postgis-users] Subject: Re: raster2pgsql 2.1.1 on
        Windows 7 64bit - ERREUR: erreur de syntaxe
Message-ID:
        <CABUeae-w_cXrPh_jP3oHBKKaqE9sMi+0bG1zdapOcenpXmqW4w at mail.gmail.com>
Content-Type: text/plain; charset=UTF-8

On 24 April 2014 21:19, Guillaume Drolet <droletguillaume at gmail.com> wrote:
> I tried the same command as in my previous post on my PC at home. It's
> Windows 7 64bit as well and the cluster has the same locale
> ('French.Canadian.1252'.) and encoding (UTF8).
>
> The command was successful so my work colleague and I, who experiences
> the same behaviour as the one described previously on this list,
> suspect that it may be due to some problems or conflict between
> PostGIS and the hardware (we have similar systems): is this a sensible
> hypothesis?

I doubt it. I use raster2pgsql frequently on Windows, since PostGIS pre-2.0
and I have never experienced any such issues.

raster2pgsql is a standalone command line utility that generates
SQL commands and outputs them to stdout.
raster2pgsql has no run-time dependency on PostgreSQL or PostGIS.

First thing to check is: does raster2pgsql generate valid output,
if redirected to file, does the SQL commands in output file
look well-formed and complete?

If not, which SQL lines are broken?
Do the broken lines correspond to any character data in non-ASCII encodings?
For example, check if you run raster2pgsql with -F switch to add
column with the name of
your raster file and the raster file name contains characters with
French accents, etc.

I'd suggest, don't use psql.exe or load any data to PostgreSQL until
you confirm raster2pgsql generates text output with complete well-formed and
valid SQL commands first.

Best regards,
--
Mateusz  ?oskot, http://mateusz.loskot.net