<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:st1="urn:schemas-microsoft-com:office:smarttags" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="country-region"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PostalCode"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="State"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="Street"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="City"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="address"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="place"/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:"Palatino Linotype";
panose-1:2 4 5 2 5 5 5 3 3 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:#606420;
text-decoration:underline;}
p
{mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman";}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Palatino Linotype";
color:black;
font-weight:normal;
font-style:normal;
text-decoration:none none;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-US link=blue vlink="#606420">
<div class=Section1>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>Hello. I’ve run into a problem with shp2pgsql, at the command
prompt, where the –W option to change the default character encoding of
UTF-8 to, for example LATIN1, is not working. If I’m correct, the
–W character encoding option should allow you to switch from the default
UTF-8 to any other character encode that is required. In my case it is LATIN1.
I used the following line below to store the .sql file.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>shp2pgsql.exe -d -s 2956:3401 -I -W "LATIN1"
"C:\0\Canada_Land_Survey_Data\test\Deliverables\CLSR_Land_Parcel.shp"
scratch.ab_clsr_land_parcel >
C:\0\Canada_Land_Survey_Data\Data_Dump\Land_Parcel_Error.sql<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>A portion of the result is shown below. <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face="Times New Roman"><span
style='font-size:12.0pt;color:blue'>SET</span></font> CLIENT_ENCODING <font
color=blue><span style='color:blue'>TO</span></font> UTF8;<o:p></o:p></p>
<p class=MsoNormal><font size=3 color=blue face="Times New Roman"><span
style='font-size:12.0pt;color:blue'>SET</span></font>
STANDARD_CONFORMING_STRINGS <font color=blue><span style='color:blue'>TO ON</span></font>;<o:p></o:p></p>
<p class=MsoNormal><font size=3 color=blue face="Times New Roman"><span
style='font-size:12.0pt;color:blue'>SELECT</span></font> DropGeometryColumn(<font
color="#993366"><span style='color:#993366'>'scratch','ab_clsr_land_parcel','geom'</span></font>);<o:p></o:p></p>
<p class=MsoNormal><font size=3 color=blue face="Times New Roman"><span
style='font-size:12.0pt;color:blue'>DROP TABLE</span></font> <font
color="#993366"><span style='color:#993366'>"scratch"."ab_clsr_land_parcel"</span></font>;<o:p></o:p></p>
<p class=MsoNormal><font size=3 color=blue face="Times New Roman"><span
style='font-size:12.0pt;color:blue'>BEGIN</span></font>;<o:p></o:p></p>
<p class=MsoNormal><font size=3 color=blue face="Times New Roman"><span
style='font-size:12.0pt;color:blue'>CREATE TABLE</span></font> <font
color="#993366"><span style='color:#993366'>"scratch"."ab_clsr_land_parcel"</span></font>
(gid serial,<o:p></o:p></p>
<p class=MsoNormal><font size=3 color="#993366" face="Times New Roman"><span
style='font-size:12.0pt;color:#993366'>"pin"</span></font> float8,<o:p></o:p></p>
<p class=MsoNormal><font size=3 color="#993366" face="Times New Roman"><span
style='font-size:12.0pt;color:#993366'>"designator"</span></font> <font
color=blue><span style='color:blue'>varchar</span></font>(<font color="#00ccff"><span
style='color:#00CCFF'>254</span></font>),<o:p></o:p></p>
<p class=MsoNormal><font size=3 color="#993366" face="Times New Roman"><span
style='font-size:12.0pt;color:#993366'>"remain_ind"</span></font> <font
color=blue><span style='color:blue'>varchar</span></font>(<font color="#00ccff"><span
style='color:#00CCFF'>254</span></font>),<o:p></o:p></p>
<p class=MsoNormal><font size=3 color="#993366" face="Times New Roman"><span
style='font-size:12.0pt;color:#993366'>"planno"</span></font> <font
color=blue><span style='color:blue'>varchar</span></font>(<font color="#00ccff"><span
style='color:#00CCFF'>30</span></font>),<o:p></o:p></p>
<p class=MsoNormal><font size=3 color="#993366" face="Times New Roman"><span
style='font-size:12.0pt;color:#993366'>"admin_code"</span></font> <font
color=blue><span style='color:blue'>varchar</span></font>(<font color="#00ccff"><span
style='color:#00CCFF'>20</span></font>),<o:p></o:p></p>
<p class=MsoNormal><font size=3 color="#993366" face="Times New Roman"><span
style='font-size:12.0pt;color:#993366'>"pcl_type"</span></font> <font
color=blue><span style='color:blue'>varchar</span></font>(<font color="#00ccff"><span
style='color:#00CCFF'>254</span></font>),<o:p></o:p></p>
<p class=MsoNormal><font size=3 color="#993366" face="Times New Roman"><span
style='font-size:12.0pt;color:#993366'>"lcselectio"</span></font> <font
color=blue><span style='color:blue'>varchar</span></font>(<font color="#00ccff"><span
style='color:#00CCFF'>100</span></font>),<o:p></o:p></p>
<p class=MsoNormal><font size=3 color="#993366" face="Times New Roman"><span
style='font-size:12.0pt;color:#993366'>"remarks"</span></font> <font
color=blue><span style='color:blue'>varchar</span></font>(<font color="#00ccff"><span
style='color:#00CCFF'>254</span></font>),<o:p></o:p></p>
<p class=MsoNormal><font size=3 color="#993366" face="Times New Roman"><span
style='font-size:12.0pt;color:#993366'>"pcl_state"</span></font> <font
color=blue><span style='color:blue'>varchar</span></font>(<font color="#00ccff"><span
style='color:#00CCFF'>254</span></font>),<o:p></o:p></p>
<p class=MsoNormal><font size=3 color="#993366" face="Times New Roman"><span
style='font-size:12.0pt;color:#993366'>"reg_pin"</span></font> <font
color=blue><span style='color:blue'>varchar</span></font>(<font color="#00ccff"><span
style='color:#00CCFF'>254</span></font>));<o:p></o:p></p>
<p class=MsoNormal><font size=3 color=blue face="Times New Roman"><span
style='font-size:12.0pt;color:blue'>ALTER TABLE</span></font> <font
color="#993366"><span style='color:#993366'>"scratch"."ab_clsr_land_parcel"</span></font>
<font color=blue><span style='color:blue'>ADD PRIMARY KEY</span></font> (gid);<o:p></o:p></p>
<p class=MsoNormal><font size=3 color=blue face="Times New Roman"><span
style='font-size:12.0pt;color:blue'>SELECT</span></font> AddGeometryColumn(<font
color="#993366"><span style='color:#993366'>'scratch','ab_clsr_land_parcel','geom','3401','MULTIPOLYGON'</span></font>,<font
color="#00ccff"><span style='color:#00CCFF'>2</span></font>);<o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>I would have expected the first line to have read <font color=blue><span
style='color:blue'>SET</span></font> CLIENT_ENCODING <font color=blue><span
style='color:blue'>TO</span></font> LATIN1 instead of <font color=blue><span
style='color:blue'>SET</span></font> CLIENT_ENCODING <font color=blue><span
style='color:blue'>TO</span></font> UTF8.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>Initially I was attempting to upload a shapefile to a database at work
using this command;<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>shp2pgsql.exe -d -s 2956:3401 -I -W "LATIN1"
"C:\0\Canada_Land_Survey_Data\test\Deliverables\CLSR_Land_Parcel.shp"
scratch.ab_clsr_land_parcel | psql.exe -h "localhost" -p
"5432" -U "postgres" -d "major_testing_db"<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>Instead of being uploaded, the resulting error occurred:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>ERROR: unterminated quoted string at or near “’”<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>LINE 1:
…o”,”remarks”,”pcl_state”,”reg_pin”,geom>
VALUES <’1115077’,’<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>
^<o:p></o:p></span></font></p>
<p class=MsoNormal><st1:place w:st="on"><st1:City w:st="on"><font size=3
face="Times New Roman"><span style='font-size:12.0pt'>Reading</span></font></st1:City></st1:place>
through the .sql file, I tracked down the problem to be a character not
supported by UTF-8 in the following line (the small arrow highlighted in red);<o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face="Times New Roman"><span
style='font-size:12.0pt;color:blue'>INSERT INTO</span></font> <font
color="#993366"><span style='color:#993366'>"scratch"."ab_clsr_land_parcel"</span></font>
(<font color="#993366"><span style='color:#993366'>"pin","designator","remain_ind","planno","admin_code","pcl_type","lcselectio","remarks","pcl_state","reg_pin",geom)
</span></font><font color=blue><span style='color:blue'>VALUES</span></font> (<font
color="#993366"><span style='color:#993366'>'1115077','</span></font><font
color="#993366"><span style='color:#993366'>EJERE K’ELNI KUE INDIAN
RESERVE 196I','No','84988 CLSR AB','09888','Inside Canada Lands',</span></font><font
color=blue><span style='color:blue'>NULL,NULL</span></font>,<font
color="#993366"><span style='color:#993366'>'ACTIVE',</span></font><font
color=blue><span style='color:blue'>NULL</span></font><font color="#993366"><span
style='color:#993366'>,</span></font>ST_Transform('<font color="#993366"><span
style='color:#993366'>01060000208C0B00…….<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>When I used the shp2pgsql-gui and selected LATIN1 for the character
encoding, the shapefile was uploaded to the database without incident. Any help
and/or explanation would be appreciated. The version of both shp2pgsql and
shp2pgsql-gui that I have used is 2.1.0. Thanks to anyone for their help.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=black face="Palatino Linotype"><span
style='font-size:10.0pt;font-family:"Palatino Linotype";color:black'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=black face="Palatino Linotype"><span
style='font-size:10.0pt;font-family:"Palatino Linotype";color:black'><o:p> </o:p></span></font></p>
<p><strong><b><font size=2 color=black face="Palatino Linotype"><span
style='font-size:10.0pt;font-family:"Palatino Linotype";color:black'>Michael
Fricker</span></font></b></strong><font size=2 color=black
face="Palatino Linotype"><span style='font-size:10.0pt;font-family:"Palatino Linotype";
color:black'> <br>
GIS Technician<br>
<st1:City w:st="on"><st1:place w:st="on">Altus</st1:place></st1:City> Geomatics<br>
Limited Partnership<br>
<br>
T: 403.234.7599 ext 2395<br>
F: 403.261.4712<br>
<br>
<st1:Street w:st="on"><st1:address w:st="on">840 7th Avenue SW, Suite 900</st1:address></st1:Street><br>
<st1:place w:st="on"><st1:City w:st="on">Calgary</st1:City>, <st1:State w:st="on">Alberta</st1:State>
<st1:PostalCode w:st="on">T2P 3G2</st1:PostalCode> <st1:country-region
w:st="on">Canada</st1:country-region></st1:place> <br>
<a href="http://"></a><br>
<em><i><font face="Palatino Linotype"><span style='font-family:"Palatino Linotype"'>This
message, and the documents attached hereto, are intended only for the addressee
and may contain privileged or confidential information. Any unauthorized
disclosure is strictly prohibited. If you have received this message in error,
please notify us immediately so that we may correct our internal records.
Please then delete the original message. Thank you.</span></font></i></em> </span></font><o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
</body>
</html>