<!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">
Here is the version:<br>
RELEASE: 2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 (r$Id:
shp2pgsql-core.h 6358 2010-12-13 20:09:26Z pramsey $)<br>
USAGE: shp2pgsql [<options>] <shapefile>
[<schema>.]<table><br>
<br>
I had tried the iconv fix which is a great idea. It seemed to work,
but I ran out of disk space.<br>
$PGBIN/shp2pgsql -c -s 4269 -g the_geom -W "latin1"
tl_2010_27_county10.dbf tiger_staging.mn_county10 | iconv -f latin1
-t UTF8 | $PGBIN/psql -d $PGDATABASE <br>
So I have been spending time on creating more disk space instead of
fixing this.<br>
I would like to share the patches that I have so that others can try
to improve it especially those who are more familiar with the
tiger2010 file formats.<br>
It would be nice to have a working tiger2010 decoder for linux in
the next postgis release.<br>
<br>
I don't remember offhand which state had the utf problem. Several
of them did.<br>
I would run ./tiger_load >& del with a "set -x" in the file
for debugging. (You can then search that file for "aborted" and
backtrack and find out which file it was processing.) It got rather
large and so did my log files.<br>
I was trying to get more info on this when I ran out of disk space.
I could not even vaccuum any databases. I see many large files in
my postgres directories and wonder what they are all for. I have
been using 2 different database clusters one on a new larger drive
for the geocoder. It seems that my cluster on the small drive has a
lot of files on it thought that are taking up a lot of space.<br>
<br>
On 04/13/2011 01:32 AM, Paragon Corporation wrote:
<blockquote cite="mid:20A7B7AEECF349C98E5E5618B32A9139@J"
type="cite">
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<meta name="GENERATOR" content="MSHTML 8.00.7601.17537">
<div dir="ltr" align="left"><span class="884322206-13042011"><font
color="#0000ff" face="Arial" size="2">Don,</font></span></div>
<div dir="ltr" align="left"><span class="884322206-13042011"></span> </div>
<div dir="ltr" align="left"><span class="884322206-13042011"><font
color="#0000ff" face="Arial" size="2">Which state were you
processing? I can check it out and see if I get similar
errors on my shp2pgsql. You could be right and the file
just isn't Latin1.</font></span></div>
<div dir="ltr" align="left"><span class="884322206-13042011"></span> </div>
<div dir="ltr" align="left"><span class="884322206-13042011"><font
color="#0000ff" face="Arial" size="2">The regress test did
seem to pass for me once that ticket was fixed.</font></span></div>
<div dir="ltr" align="left"><span class="884322206-13042011"></span> </div>
<div dir="ltr" align="left"><span class="884322206-13042011"><font
color="#0000ff" face="Arial" size="2">Also to confirm you
are running the latest version of shp2pgsql </font></span></div>
<div dir="ltr" align="left"><span class="884322206-13042011"></span> </div>
<div dir="ltr" align="left"><span class="884322206-13042011"><font
color="#0000ff" face="Arial" size="2">If you run </font></span></div>
<div dir="ltr" align="left"><span class="884322206-13042011"></span> </div>
<div dir="ltr" align="left"><span class="884322206-13042011"><font
color="#0000ff" face="Arial" size="2">shp2pgsql from
commandline, it should output the version. Mine for example
reads</font></span></div>
<div dir="ltr" align="left"><span class="884322206-13042011"></span> </div>
<div dir="ltr" align="left"><span class="884322206-13042011"><font
color="#0000ff" face="Arial" size="2">RELEASE: 2.0
USE_GEOS=1 USE_PROJ=1 USE_STATS=1 (r$Id: shp2pgsql-core.h
6925 2011-03-18 16:24:33Z pramsey $)<br>
</font></span><span class="884322206-13042011"></span></div>
<div dir="ltr" align="left"><span class="884322206-13042011"><font
color="#0000ff" face="Arial" size="2">The version
unfortunately isn't quite accurate since its evidentally
looking at the .h file instead of .c file. So though
my version says 6925, its really</font></span></div>
<div dir="ltr" align="left"><span class="884322206-13042011"><font
color="#0000ff" face="Arial" size="2">6932 or later.</font> </span></div>
<div dir="ltr" align="left"><span class="884322206-13042011"></span> </div>
<div dir="ltr" align="left"><span class="884322206-13042011"><font
color="#0000ff" face="Arial" size="2"><a
moz-do-not-send="true"
href="http://trac.osgeo.org/postgis/changeset/6932">http://trac.osgeo.org/postgis/changeset/6932</a></font></span></div>
<div dir="ltr" align="left"><span class="884322206-13042011"></span> </div>
<div dir="ltr" align="left"><span class="884322206-13042011"><font
color="#0000ff" face="Arial" size="2">Hope that helps,</font></span></div>
<div dir="ltr" align="left"><span class="884322206-13042011"><font
color="#0000ff" face="Arial" size="2">Regina</font></span></div>
<div dir="ltr" align="left"><span class="884322206-13042011"><font
color="#0000ff" face="Arial" size="2"><a
moz-do-not-send="true" href="http://www.postgis.us">http://www.postgis.us</a></font></span></div>
<div dir="ltr" align="left"><span class="884322206-13042011"></span> </div>
<div dir="ltr" align="left"><span class="884322206-13042011"></span> </div>
<div dir="ltr" align="left">
<hr tabindex="-1">
</div>
<div dir="ltr" align="left"><font face="Tahoma" size="2"><b>From:</b>
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>
[<a class="moz-txt-link-freetext" href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</a>] <b>On
Behalf Of </b>Don<br>
<b>Sent:</b> Tuesday, April 12, 2011 3:08 AM<br>
<b>To:</b> PostGIS Users Discussion<br>
<b>Subject:</b> Re: [postgis-users] "Linux" geocoder script ?<br>
</font><br>
</div>
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>