Re: [postgis-users] Changing transaction behaviour in shp2pgsql -summary
I use the external recoding for shapefiles and other sql files witch are in LATIN9. The postgres recoding to utf-8 sometimes fail. The external program "recode" do a lazy recoding and that output is accepted by postgres. Maybe the problem occurs only für me. Gr Ralf Am Montag 15 März 2010 10:02:13 schrieb Paragon Corporation: > Ralf, > > Apologize if you tried this already since we are entering the conversation > late. > > Are you sure your shapefiles are in UTF-8. The -W switch you give to > shp2pgsql has to be the encoding of the shapefile not the encoding of the > database. > The reason is that shp2pgsql converts from the source encoding -W to the > UTF-8 and then does a SET client_encoding UTF-8 during load as I recall. > > So I would assume your shapefile are in LATIN-1 or WIN something or other > in which case > > shp2pgsql -s 28350 -W "latin1" > > Might work better. > > Hope that helps, > Leo and Regina > http://www.postgis.us > > > > -Original Message- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Ralf > Suhr > Sent: Monday, March 15, 2010 4:38 AM > To: postgis-users@postgis.refractions.net > Subject: Re: [postgis-users] Changing transaction behaviour in shp2pgsql > -summary > > Hello Ben, > > I'm doing recoding of shapefiles without postgres when postgres can not do > so. > > shp2pgsql [...] | recode shapefile_encoding..utf8 | psql database. > > Gr Ralf > > Am Montag 15 März 2010 00:51:00 schrieb Ben Madin: > > Thanks all for the feedback - to summarise : > > > > shp2pgsql - Currently, all output is 'chunked' transactions - changing > > this behaviour not currently supported. - A patch could be provided to > > shp2pgsql to do this. > > > > - The suggestion is a -T switch with options such as single, chunked, > > > > no transactions. - I'm happy to look at submitting a patch... but it > > won't be until after I've submitted my thesis ... > > > > The text editor approach is to direct output into a file and find / > > replace the BEGIN / COMMIT lines. > > > > The on-the-fly alternative is to use a pattern matching program, with > > two solutions offered : > > > > Using sed (thanks Brent) > > > > shp2pgsql -s 28350 -W UTF8 \ > > /Users/owner/Spatial/country/AUS/WA/MRWA/mrwa_network.shp \ > > gis.mrwa_gda94 | sed 's/BEGIN//' | sed 's/COMMIT//' psql australia > > > > Using grep (thanks Steve) > > > > shp2pgsql ... | grep -v BEGIN | grep -v COMMIT | psql australia > > > > cheers > > > > Ben > > > > On 15/03/2010, at 4:35 , strk wrote: > > > On Sun, Mar 14, 2010 at 04:58:02PM +0700, Ben Madin wrote: > > >> in the event I can't work out the encoding, is there any way to > > >> turn off the transaction behaviour, ie I'm happy to miss 4 records, > > >> but not 1000 as a result of the transactions being aborted. (short > > >> of putting it all into a text file and removing all the begin and > > >> commit commands...) > > > > > > Feel like working on a patch to make "transaction policy" selectable > > > with a switch ? > > > > > > -T transaction policy (single*,none) > > > > > > Current policy is chunked, dunno for what rationale.. > > > > > > --strk; > > > > > > () Free GIS & Flash consultant/developer > > > /\ http://strk.keybit.net/services.html > > > > > > ___ > > > postgis-users mailing list > > > postgis-users@postgis.refractions.net > > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > ___ > > postgis-users mailing list > > postgis-users@postgis.refractions.net > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Changing transaction behaviour in shp2pgsql -summary
Ralf, Apologize if you tried this already since we are entering the conversation late. Are you sure your shapefiles are in UTF-8. The -W switch you give to shp2pgsql has to be the encoding of the shapefile not the encoding of the database. The reason is that shp2pgsql converts from the source encoding -W to the UTF-8 and then does a SET client_encoding UTF-8 during load as I recall. So I would assume your shapefile are in LATIN-1 or WIN something or other in which case shp2pgsql -s 28350 -W "latin1" Might work better. Hope that helps, Leo and Regina http://www.postgis.us -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Ralf Suhr Sent: Monday, March 15, 2010 4:38 AM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] Changing transaction behaviour in shp2pgsql -summary Hello Ben, I'm doing recoding of shapefiles without postgres when postgres can not do so. shp2pgsql [...] | recode shapefile_encoding..utf8 | psql database. Gr Ralf Am Montag 15 März 2010 00:51:00 schrieb Ben Madin: > Thanks all for the feedback - to summarise : > > shp2pgsql - Currently, all output is 'chunked' transactions - changing > this behaviour not currently supported. - A patch could be provided to > shp2pgsql to do this. > - The suggestion is a -T switch with options such as single, chunked, > no transactions. - I'm happy to look at submitting a patch... but it > won't be until after I've submitted my thesis ... > > The text editor approach is to direct output into a file and find / > replace the BEGIN / COMMIT lines. > > The on-the-fly alternative is to use a pattern matching program, with > two solutions offered : > > Using sed (thanks Brent) > > shp2pgsql -s 28350 -W UTF8 \ > /Users/owner/Spatial/country/AUS/WA/MRWA/mrwa_network.shp \ > gis.mrwa_gda94 | sed 's/BEGIN//' | sed 's/COMMIT//' psql australia > > > Using grep (thanks Steve) > > shp2pgsql ... | grep -v BEGIN | grep -v COMMIT | psql australia > > cheers > > Ben > > On 15/03/2010, at 4:35 , strk wrote: > > On Sun, Mar 14, 2010 at 04:58:02PM +0700, Ben Madin wrote: > >> in the event I can't work out the encoding, is there any way to > >> turn off the transaction behaviour, ie I'm happy to miss 4 records, > >> but not 1000 as a result of the transactions being aborted. (short > >> of putting it all into a text file and removing all the begin and > >> commit commands...) > > > > Feel like working on a patch to make "transaction policy" selectable > > with a switch ? > > > > -T transaction policy (single*,none) > > > > Current policy is chunked, dunno for what rationale.. > > > > --strk; > > > > () Free GIS & Flash consultant/developer > > /\ http://strk.keybit.net/services.html > > > > ___ > > postgis-users mailing list > > postgis-users@postgis.refractions.net > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Changing transaction behaviour in shp2pgsql - summary
Hello Ben, I'm doing recoding of shapefiles without postgres when postgres can not do so. shp2pgsql [...] | recode shapefile_encoding..utf8 | psql database. Gr Ralf Am Montag 15 März 2010 00:51:00 schrieb Ben Madin: > Thanks all for the feedback - to summarise : > > shp2pgsql - Currently, all output is 'chunked' transactions - changing this > behaviour not currently supported. - A patch could be provided to > shp2pgsql to do this. > - The suggestion is a -T switch with options such as single, chunked, no > transactions. - I'm happy to look at submitting a patch... but it won't be > until after I've submitted my thesis ... > > The text editor approach is to direct output into a file and find / replace > the BEGIN / COMMIT lines. > > The on-the-fly alternative is to use a pattern matching program, with two > solutions offered : > > Using sed (thanks Brent) > > shp2pgsql -s 28350 -W UTF8 \ > /Users/owner/Spatial/country/AUS/WA/MRWA/mrwa_network.shp \ > gis.mrwa_gda94 | sed 's/BEGIN//' | sed 's/COMMIT//' psql australia > > > Using grep (thanks Steve) > > shp2pgsql ... | grep -v BEGIN | grep -v COMMIT | psql australia > > cheers > > Ben > > On 15/03/2010, at 4:35 , strk wrote: > > On Sun, Mar 14, 2010 at 04:58:02PM +0700, Ben Madin wrote: > >> in the event I can't work out the encoding, is there any way to turn off > >> the transaction behaviour, ie I'm happy to miss 4 records, but not 1000 > >> as a result of the transactions being aborted. (short of putting it all > >> into a text file and removing all the begin and commit commands...) > > > > Feel like working on a patch to make "transaction policy" selectable > > with a switch ? > > > > -T transaction policy (single*,none) > > > > Current policy is chunked, dunno for what rationale.. > > > > --strk; > > > > () Free GIS & Flash consultant/developer > > /\ http://strk.keybit.net/services.html > > > > ___ > > postgis-users mailing list > > postgis-users@postgis.refractions.net > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Changing transaction behaviour in shp2pgsql - summary
Thanks all for the feedback - to summarise : shp2pgsql - Currently, all output is 'chunked' transactions - changing this behaviour not currently supported. - A patch could be provided to shp2pgsql to do this. - The suggestion is a -T switch with options such as single, chunked, no transactions. - I'm happy to look at submitting a patch... but it won't be until after I've submitted my thesis ... The text editor approach is to direct output into a file and find / replace the BEGIN / COMMIT lines. The on-the-fly alternative is to use a pattern matching program, with two solutions offered : Using sed (thanks Brent) shp2pgsql -s 28350 -W UTF8 \ /Users/owner/Spatial/country/AUS/WA/MRWA/mrwa_network.shp \ gis.mrwa_gda94 | sed 's/BEGIN//' | sed 's/COMMIT//' psql australia Using grep (thanks Steve) shp2pgsql ... | grep -v BEGIN | grep -v COMMIT | psql australia cheers Ben On 15/03/2010, at 4:35 , strk wrote: > On Sun, Mar 14, 2010 at 04:58:02PM +0700, Ben Madin wrote: > >> in the event I can't work out the encoding, is there any way to turn off the >> transaction behaviour, ie I'm happy to miss 4 records, but not 1000 as a >> result of the transactions being aborted. (short of putting it all into a >> text file and removing all the begin and commit commands...) > > Feel like working on a patch to make "transaction policy" selectable > with a switch ? > > -T transaction policy (single*,none) > > Current policy is chunked, dunno for what rationale.. > > --strk; > > () Free GIS & Flash consultant/developer > /\ http://strk.keybit.net/services.html > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Changing transaction behaviour in shp2pgsql
On Sun, Mar 14, 2010 at 04:58:02PM +0700, Ben Madin wrote: > in the event I can't work out the encoding, is there any way to turn off the > transaction behaviour, ie I'm happy to miss 4 records, but not 1000 as a > result of the transactions being aborted. (short of putting it all into a > text file and removing all the begin and commit commands...) Feel like working on a patch to make "transaction policy" selectable with a switch ? -T transaction policy (single*,none) Current policy is chunked, dunno for what rationale.. --strk; () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Changing transaction behaviour in shp2pgsql
Hi Ben
You might remove the begin & commit commands via your command line with sed, or
see if ogr2ogr gives you a more robust conversion.
For the first, something like:
shp2pgsql -s 28350 -W UTF8 \
/Users/owner/Spatial/country/AUS/WA/MRWA/mrwa_network.shp \
gis.mrwa_gda94 | sed 's/BEGIN//' | sed 's/COMMIT//' psql australia
If this fails, or you want to try ogr2ogr, ask again :-)
Cheers,
Brent
--- On Sun, 3/14/10, Ben Madin wrote:
> From: Ben Madin
> Subject: [postgis-users] Changing transaction behaviour in shp2pgsql
> To: "PostGIS Users Discussion"
> Date: Sunday, March 14, 2010, 10:58 PM
> G'day all,
>
> I'm using shp2pgsql to import a road network, of which the
> fine detail is not so important.
>
> Mac OS X 10.6.2 POSTGIS="1.4.1"
> GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.7.1, 23 September 2009"
> USE_STATS (thanks William K)
>
>
> $ shp2pgsql -s 28350
> /Users/owner/Spatial/country/AUS/WA/MRWA/mrwa_network.shp
> gis.mrwa_gda94 | psql australia
>
> During the import a number of times (4) I get the
>
> ERROR: invalid byte sequence for
> encoding "UTF8": 0xec5343
> 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".
> ERROR: current transaction is
> aborted, commands ignored until end of transaction block
>
> and the next several hundred records are skipped.
>
> When I put the -W UTF8 flag in , I miss the errors but die
> when I get to about 101000 (out of 15) records in :
>
> INSERT 0 1
> INSERT 0 1
> INSERT 0 1
> INSERT 0 1
> ERROR: syntax error at end of input
> LINE 1: ...e_geom) VALUES ('1.1557400e+005','GILMORE ST
> (SCADDAN)',
>
>
> in the event I can't work out the encoding, is there any
> way to turn off the transaction behaviour, ie I'm happy to
> miss 4 records, but not 1000 as a result of the transactions
> being aborted. (short of putting it all into a text file and
> removing all the begin and commit commands...)
>
> cheers
>
> Ben
>
>
>
>
>
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Changing transaction behaviour in shp2pgsql
Ben,
You can probably just strip the BEGIN and COMMIT lines like
shp2pgsql ... | grep -v BEGIN | grep -v COMMIT | psql australia
You might also want to look at the file:
shp2pgsql ... > data.sql
vi data.sql
HTH,
-Steve
Ben Madin wrote:
G'day all,
I'm using shp2pgsql to import a road network, of which the fine detail is not so important.
Mac OS X 10.6.2 POSTGIS="1.4.1" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.7.1, 23
September 2009" USE_STATS (thanks William K)
$ shp2pgsql -s 28350 /Users/owner/Spatial/country/AUS/WA/MRWA/mrwa_network.shp
gis.mrwa_gda94 | psql australia
During the import a number of times (4) I get the
ERROR: invalid byte sequence for encoding "UTF8": 0xec5343
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".
ERROR: current transaction is aborted, commands ignored until end of
transaction block
and the next several hundred records are skipped.
When I put the -W UTF8 flag in , I miss the errors but die when I get to about
101000 (out of 15) records in :
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
ERROR: syntax error at end of input
LINE 1: ...e_geom) VALUES ('1.1557400e+005','GILMORE ST (SCADDAN)',
in the event I can't work out the encoding, is there any way to turn off the
transaction behaviour, ie I'm happy to miss 4 records, but not 1000 as a result
of the transactions being aborted. (short of putting it all into a text file
and removing all the begin and commit commands...)
cheers
Ben
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Changing transaction behaviour in shp2pgsql
G'day all,
I'm using shp2pgsql to import a road network, of which the fine detail is not
so important.
Mac OS X 10.6.2 POSTGIS="1.4.1" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.7.1, 23
September 2009" USE_STATS (thanks William K)
$ shp2pgsql -s 28350 /Users/owner/Spatial/country/AUS/WA/MRWA/mrwa_network.shp
gis.mrwa_gda94 | psql australia
During the import a number of times (4) I get the
ERROR: invalid byte sequence for encoding "UTF8": 0xec5343
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".
ERROR: current transaction is aborted, commands ignored until end of
transaction block
and the next several hundred records are skipped.
When I put the -W UTF8 flag in , I miss the errors but die when I get to about
101000 (out of 15) records in :
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
ERROR: syntax error at end of input
LINE 1: ...e_geom) VALUES ('1.1557400e+005','GILMORE ST (SCADDAN)',
in the event I can't work out the encoding, is there any way to turn off the
transaction behaviour, ie I'm happy to miss 4 records, but not 1000 as a result
of the transactions being aborted. (short of putting it all into a text file
and removing all the begin and commit commands...)
cheers
Ben
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
