<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
My database is encoded as<br>
geocoder | drh | UTF8 | C | en_US.UTF-8 | .<br>
All my shp2pgsql statements have the -W option like this.<br>
${loader} -a -s 4269 -g the_geom -W "latin1" $z
${staging_schema}.${state_abbrev}_${table_name} | $PGBIN/psql -d
$PGDATABASE;<br>
<br>
Here is the bug that I was referring to.<br>
<a class="moz-txt-link-freetext" href="http://trac.osgeo.org/postgis/ticket/808">http://trac.osgeo.org/postgis/ticket/808</a><br>
In one case I had a very large number of inserts processed for the
shape file and then got that error.<br>
<br>
From your link it says:<br>
"To enable automatic character set conversion, you have to tell <span
class="PRODUCTNAME">PostgreSQL</span> the character set (encoding)
you would like to use in the client. There are several ways to
accomplish this: "<br>
Perhaps I need to use <br>
<pre class="PROGRAMLISTING">SET CLIENT_ENCODING TO '<tt class="REPLACEABLE"><i>value</i></tt>'; in psql or is shp2pgsql supposed to do that when I use the -W option?
postgis is expecting utf-8 when it should be expecting latin1 and converting it to utf-8.
Could data type for a column have some effect on this?
</pre>
<br>
On 04/11/2011 08:52 PM, Sylvain Racine wrote:
<blockquote cite="mid:BLU0-SMTP81E01A70134256809324A6FDAB0@phx.gbl"
type="cite">Hello,
<br>
<br>
This is not a shp2pgsql bug. You get this error when you try to
insert string data in PostgreSQL from another encoding that the
one of your database Ex: Your data is formatted in Latin1
(ISO-8859-1) and you insert them in a UTF-8 database. To fix the
error message, you need to convert your data.
<br>
<br>
PostgreSQL have a internal converter. shp2pgsql have it too. Try
shp2pgsql -W <encoding> where <encoding> is the format
of you DBase file .dbf. This is called the "client encoding" in
PostgreSQL. See list of valid encoding type:
<br>
<a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/9.0/static/multibyte.html">http://www.postgresql.org/docs/9.0/static/multibyte.html</a>
<br>
<br>
Don't mix it with the database encoding. It is the one you us to
create your databse. There is also a default database charset,
depending of your OS. It is the one you use to create template1
database in init-db. Mine is "UTF8" on Ubuntu.
<br>
<br>
Hope that this information will help you
<br>
<br>
Regards
<br>
<br>
Sylvain Racine
<br>
<br>
On 2011-04-11 21:22, Don wrote:
<br>
<blockquote type="cite">I have got the tiger2010 geodecoder to
work on my Opensuse system.
<br>
geocoder=#
<br>
geocoder=# SELECT g.rating,
<br>
geocoder-# ST_X(geomout) As lon,
<br>
geocoder-# ST_Y(geomout) As lat, (addy).*
<br>
geocoder-# FROM geocode('1731 New Hampshire Avenue Northwest,
Washington, DC 20010') As g;
<br>
rating | lon | lat | address |
predirabbrev | streetname | streettypeabbrev | postdirabbrev
| internal | location | stateabbrev | zip | parsed
<br>
--------+-------------------+------------------+---------+--------------+---------------+------------------+---------------+----------+------------+-------------+-------+--------
<br>
0 | -77.0399013800607 | 38.9134181361424 | 1731
| | New Hampshire | Ave |
NW | | Washington | DC | 20009 | t
<br>
(1 row)
<br>
There are a few glitches. I noticed that I am getting this
message sometimes.
<br>
INSERT 0 1
<br>
INSERT 0 1
<br>
INSERT 0 1
<br>
INSERT 0 1
<br>
ERROR: invalid byte sequence for encoding "UTF8": 0xed6f20
<br>
HINT: This error can also happen if the byte sequence does not
match the encoding expected by the server, which is controlled
by "client_encoding".
<br>
ERROR: current transaction is aborted, commands ignored until
end of transaction block
<br>
ERROR: current transaction is aborted, commands ignored until
end of transaction block
<br>
ERROR: current transaction is aborted, commands ignored until
end of transaction block
<br>
I researched this some and it appears to be a shp2pgsql bug.
<br>
But I am using postgis-utils-2.0.0SVN-1.2.x86_64
<br>
postgis-2.0.0SVN-1.2.x86_64 where this has supposedly been
fixed. Or could the census data be corrupted?
<br>
So I have "lost" some of the data due to this error.
<br>
I had problems with psql generating ctrl-m instead of \n which
would really mess up the script when it ran.
<br>
So after I generated my load tiger script I ran this command
<br>
tr "\r" "\n" < load_tiger > load_tiger2
<br>
<br>
_______________________________________________
<br>
postgis-users mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<br>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
<br>
<br>
<br>
</blockquote>
_______________________________________________
<br>
postgis-users mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<br>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
<br>
<br>
</blockquote>
</body>
</html>