[GRASS-dev] new v.in.geonames: problems with UTF-8 Unicode text

[Glynn Clements]

Markus Neteler wrote:

> > The PostgreSQL driver has a function to find out the column length.
> > With this change the full  "alternatename" field is imported into SQLite.
> ...
> 
> Using PostgreSQL instead, some escape problem happens (ok, slightly
> offtopic for this thread but still related to v.in.geonames):
> 
> v.in.geonames /tmp/IT.csv out=it
> ...
> D0/0: Escaped SQL: insert into it values ( 27788, 6485478, 'Hotel
> Roma', 'Hotel Roma', null, 43.72241, 10.39204, 'S', 'HTL', 'IT', null,
> '16', 'PI', null, null, 0, null, 1, 'Europe/Rome', '2007-04-15')
> D0/0: Escaped SQL: insert into it values ( 27789, 6485480, 'Villa
> Milani - Residenza d\\''epoca', 'Villa Milani - Residenza d\\''epoca',
> null, 42.74533, 12.73843, 'S', 'HTL', 'IT', null, '18', 'PG', null,
> null, 0, null, 374, 'Europe/Rome', '2007-04-15')
> WARNING:  nonstandard use of \\ in a string literal
> LINE 1: insert into it values ( 27789, 6485480, 'Villa Milani - Resi...
>                                                 ^
> HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
> WARNING:  nonstandard use of \\ in a string literal
> LINE 1: ..., 6485480, 'Villa Milani - Residenza d\\''epoca', 'Villa Mil...
>                                                              ^
> HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
> D0/0: Escaped SQL: insert into it values ( 27790, 6485481, 'Hotel Sher
> Dan', 'Hotel Sher Dan', null, 40.72241, 8.39582, 'S', 'HTL', 'IT',
> null, '1
> 
> I assume that the escape magic in db/drivers/postgres/execute.c isn't
> perfect. Any suggestions here?

Is there actually a problem? All I see are warnings.

If I try inserting the above into a test table using the psql utility,
I get exactly what I would expect.

grass=> create table it ( a INT, b INT, c TEXT, d TEXT, e TEXT, x DOUBLE PRECISION, y DOUBLE PRECISION, f TEXT, g TEXT, h TEXT, i TEXT, j TEXT, k TEXT, l TEXT, m TEXT, n INTEGER, o TEXT, p INTEGER, q TEXT, r TEXT) ;
CREATE TABLE
grass=> insert into it values ( 27788, 6485478, 'Hotel Roma', 'Hotel Roma', null, 43.72241, 10.39204, 'S', 'HTL', 'IT', null, '16', 'PI', null, null, 0, null, 1, 'Europe/Rome', '2007-04-15') ;
INSERT 9686780 1
grass=> insert into it values ( 27789, 6485480, 'Villa Milani - Residenza d\\''epoca', 'Villa Milani - Residenza d\\''epoca', null, 42.74533, 12.73843, 'S', 'HTL', 'IT', null, '18', 'PG', null, null, 0, null, 374, 'Europe/Rome', '2007-04-15') ;
INSERT 9686781 1
grass=> select * from it ;
   a   |    b    |                 c                 |                 d                 | e |    x     |    y     | f |  g  | h  | i | j  | k  | l | m | n | o |  p  |      q      |     r      
-------+---------+-----------------------------------+-----------------------------------+---+----------+----------+---+-----+----+---+----+----+---+---+---+---+-----+-------------+------------
 27788 | 6485478 | Hotel Roma                        | Hotel Roma                        |   | 43.72241 | 10.39204 | S | HTL | IT |   | 16 | PI |   |   | 0 |   |   1 | Europe/Rome | 2007-04-15
 27789 | 6485480 | Villa Milani - Residenza d\'epoca | Villa Milani - Residenza d\'epoca |   | 42.74533 | 12.73843 | S | HTL | IT |   | 18 | PG |   |   | 0 |   | 374 | Europe/Rome | 2007-04-15
(2 rows)

Note that the backslash is present in the file, which appears to be an
error; many other entries have literal apostrophes without the
backslash.

-- 
Glynn Clements <glynn at gclements.plus.com>