<!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">
I was running out of hard disk space on my root partition. I
have, for now, the geocoder database on a separate partition and
hard drive.<br>
I was debugging and capturing the output of every query and
command. So it generated a huge file in the gigabyte range
especially when each query was aborted because it was not UTF-8.
The iconv command appears to be working, albeit though twice. I
will be posting a bug report with an attached patch shortly. I
think I deleted the windows section, so if you patch it with that,
you will have to add that back. The iconv is not in the patch. I
think it will probably be a while before most distributions have the
new version of shp2pgsql. I thought that I had the latest but it
seems it is only available from subversion at this time. <br>
It is really nice having my own geocoder and tiger2010
database. Thanks for everyones help. I can also use the data in my
favorite GIS program qgis to draw maps, and even my own mapserver.<br>
<br>
On 04/13/2011 07:15 PM, Sylvain Racine wrote:
<blockquote cite="mid:BLU0-SMTP927F3422F1EA609A2315C0FDAD0@phx.gbl"
type="cite">
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
Hello,<br>
<br>
I saw a little error in your script. If you "pipe" your output to
iconv, you have to remove <-W "latin1"> from shp2pgsql
command. You should have<br>
$PGBIN/shp2pgsql -c -s 4269 -g the_geom tl_2010_27_county10.dbf
tiger_staging.mn_county10 | iconv -f latin1 -t UTF8 | $PGBIN/psql
-d $PGDATABASE <br>
<br>
If you don't remove the -W flag, your data will be converted
twice!<br>
<br>
About your running out of disk space, are you sure that you don't
lack of RAM memory instead? I am surprise you ran out of disk
space with this command. I think that the output from shp2pgsql is
put in RAM memory and then, send to iconv in one shot. If your RAM
memory is low, it is possible that the extra data are put in a
temporary file on your disk drive (in the swap partition I think).
But this temp file indicated that you first lack of RAM memory. I
am not complete sure of my theory. But in my mind, I think Linux
works like that.<br>
<br>
If you want to know the amount of memory needed by this script,
type this:<br>
<br>
$PGBIN/shp2pgsql -c -s 4269 -g the_geom tl_2010_27_county10.dbf
tiger_staging.mn_county10 >> temp.sql<br>
temp.sql >> iconv -f latin1 -t UTF8 >> anothertemp.sql<br>
anothertemp.sql >> $PGBIN/psql -d $PGDATABASE <br>
The size of the max file between temp.sql and anothertemp.sql is
the minimun amount of RAM you need to run this command.<br>
<br>
Note: The above script is slower than the first one because you
write you data on your disk drive between each operation. SATA or
IDE disk access is always slower than RAM access, except if you
use SSD disk.<br>
<br>
Hope it will be useful.<br>
<br>
Sylvain Racine<br>
<br>
On 13/04/2011 05:40, Don wrote:
<blockquote cite="mid:4DA56F8D.7030207@comcast.net" type="cite">
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
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 moz-do-not-send="true" class="moz-txt-link-abbreviated"
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>
[<a moz-do-not-send="true" 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 moz-do-not-send="true" class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<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>
</pre>
</blockquote>
<pre wrap=""><fieldset class="mimeAttachmentHeader"></fieldset>
_______________________________________________
postgis-users mailing list
<a moz-do-not-send="true" class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<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>
</pre>
</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>