[postgis-users] Problem with shp2pgsql and the -W character encoding option

[Michael Fricker]

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.

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

A portion of the result is shown below.

SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
SELECT DropGeometryColumn('scratch','ab_clsr_land_parcel','geom');
DROP TABLE "scratch"."ab_clsr_land_parcel";
BEGIN;
CREATE TABLE "scratch"."ab_clsr_land_parcel" (gid serial,
"pin" float8,
"designator" varchar(254),
"remain_ind" varchar(254),
"planno" varchar(30),
"admin_code" varchar(20),
"pcl_type" varchar(254),
"lcselectio" varchar(100),
"remarks" varchar(254),
"pcl_state" varchar(254),
"reg_pin" varchar(254));
ALTER TABLE "scratch"."ab_clsr_land_parcel" ADD PRIMARY KEY (gid);
SELECT AddGeometryColumn('scratch','ab_clsr_land_parcel','geom','3401','MULTIPOLYGON',2);

I would have expected the first line to have read SET CLIENT_ENCODING TO LATIN1  instead of SET CLIENT_ENCODING TO UTF8.

Initially I was attempting to upload a shapefile to a database at work using this command;

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"

Instead of being uploaded, the resulting error occurred:

ERROR:  unterminated quoted string at or near "'"
LINE 1:   ...o","remarks","pcl_state","reg_pin",geom> VALUES <'1115077','
                                                                                                                                ^
Reading 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);

INSERT INTO "scratch"."ab_clsr_land_parcel" ("pin","designator","remain_ind","planno","admin_code","pcl_type","lcselectio","remarks","pcl_state","reg_pin",geom) VALUES ('1115077','EJERE K'ELNI KUE INDIAN RESERVE 196I','No','84988 CLSR AB','09888','Inside Canada Lands',NULL,NULL,'ACTIVE',NULL,ST_Transform('01060000208C0B00.......

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.



Michael Fricker
GIS Technician
Altus Geomatics
Limited Partnership

T: 403.234.7599 ext 2395
F: 403.261.4712

840 7th Avenue SW, Suite 900
Calgary, Alberta  T2P 3G2  Canada
<http://>
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.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140207/a6aaa43a/attachment.html>