<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
Hello,<br>
<br>
To convert you data without W flag of shp2pgsql (who seem be the
problem), you can use SET CLIENT_ENCODING TO 'latin1' at the begin
of the sql file. This will convert all string type column only
(e.g. varchar, text, char).<br>
<br>
Or more simply, try this. I didn't check the following script
because I don't have latin1 shp file, but it should work. I suppose
you use Bash script on Linux and I suppose iconv is installed on
your box.<br>
<br>
${loader} -a -s 4269 -g the_geom $z
${staging_schema}.${state_abbrev}_${table_name} | iconv -f latin1 -t
UTF8 | $PGBIN/psql -d $PGDATABASE;<br>
<br>
I try it with a French pure text instead of shp file and it works
fine on PostgreSQL 8.4. This command should by pass shp2pgsql
internal converter.<br>
<br>
If that don't work, there is some other way. If you plan to load
periodically shp files into your database, you can try the excellent
(but expensive) FME converter (<a class="moz-txt-link-freetext" href="http://www.safe.com">http://www.safe.com</a>) who runs on a
Windows box. It converts any geometric data in any formats, included
PostGIS. I think you can access directly PostgreSQL through network
connection and create batch script.<br>
<br>
Finally, if you need to use open source software on Linux, there is
gvSIG (<a class="moz-txt-link-freetext" href="http://www.gvsig.org">http://www.gvsig.org</a>) (don't miss to click on the little
Union Jack Flag to get English langage). It's a free GIS software
who runs on Java. You need to load your shp file as a layer in a
project and then, to export each one manually to PostGIS. Not
obvious at a glance, difficult to understand the process at the
begin, but finally work fine with PostGIS 1.5. I didn't check with
latin1 encoding shp file. There is scripting option that I don't
check.<br>
<br>
Regards<br>
<br>
Sylvain Racine<br>
<br>
<br>
On 2011-04-12 03:08, Don wrote:
<blockquote cite="mid:4DA3FA56.5000008@comcast.net" type="cite">
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
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 moz-do-not-send="true" 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 moz-do-not-send="true" 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 moz-do-not-send="true" class="moz-txt-link-abbreviated"
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<br>
<a moz-do-not-send="true" 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 moz-do-not-send="true" class="moz-txt-link-abbreviated"
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<br>
<a moz-do-not-send="true" 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>
<pre wrap=""><fieldset class="mimeAttachmentHeader"></fieldset>
_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</body>
</html>