<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.7601.17573"></HEAD>
<BODY bgColor=#ffffff text=#000000>
<DIV dir=ltr align=left><SPAN class=930304210-13042011><FONT color=#0000ff
size=2 face=Arial>Don,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=930304210-13042011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=930304210-13042011><FONT color=#0000ff
size=2 face=Arial>Okay so it looks like your shp2pgsql is before the fix was in
place which explains why it doesn't work for you since your version doesn't have
the bug fix.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=930304210-13042011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=930304210-13042011><FONT color=#0000ff
size=2 face=Arial>Is there any way you can get a newer shp2pgsql (e.g. recompile
the latest tarball of PostGIS) or use the one from the 1.5 release?
That sounds like probably the easiest way to go.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=930304210-13042011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=930304210-13042011><FONT color=#0000ff
size=2 face=Arial>As far as files in your postgres directories, the ones in the
pg_log folder (end in .log) you can safely delete since those are just sql log
errors etc which depending on the verbosity of your error in postgresql.conf
could get huge. The other folder ones you don't want to touch since
they'll damage your cluster.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=930304210-13042011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=930304210-13042011><FONT color=#0000ff
size=2 face=Arial>Thanks for testing this out on Linux. I'll strip out all
the windows line breaks we accidentally introduced in the code and
look forward to any patches you can offer.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=930304210-13042011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=930304210-13042011><FONT color=#0000ff
size=2 face=Arial>Thanks,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=930304210-13042011><FONT color=#0000ff
size=2 face=Arial>Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=930304210-13042011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=930304210-13042011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left>
<HR tabIndex=-1>
</DIV>
<DIV dir=ltr align=left><FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Don<BR><B>Sent:</B> Wednesday, April 13, 2011 5:40 AM<BR><B>To:</B> PostGIS
Users Discussion<BR><B>Subject:</B> Re: [postgis-users] "Linux" geocoder script
?<BR></FONT><BR></DIV>
<DIV></DIV>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 name=GENERATOR content="MSHTML 8.00.7601.17537">
<DIV dir=ltr align=left><SPAN class=884322206-13042011><FONT color=#0000ff
size=2 face=Arial>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
size=2 face=Arial>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
size=2 face=Arial>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
size=2 face=Arial>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
size=2 face=Arial>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
size=2 face=Arial>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
size=2 face=Arial>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
size=2 face=Arial>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
size=2 face=Arial>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
size=2 face=Arial><A href="http://trac.osgeo.org/postgis/changeset/6932"
moz-do-not-send="true">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
size=2 face=Arial>Hope that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=884322206-13042011><FONT color=#0000ff
size=2 face=Arial>Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=884322206-13042011><FONT color=#0000ff
size=2 face=Arial><A href="http://www.postgis.us"
moz-do-not-send="true">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 size=2 face=Tahoma><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
class=moz-txt-link-freetext href="http://trac.osgeo.org/postgis/ticket/808"
moz-do-not-send="true">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"
moz-do-not-send="true">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"
moz-do-not-send="true">postgis-users@postgis.refractions.net</A> <BR><A
class=moz-txt-link-freetext
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
moz-do-not-send="true">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"
moz-do-not-send="true">postgis-users@postgis.refractions.net</A> <BR><A
class=moz-txt-link-freetext
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
moz-do-not-send="true">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>