[postgis-users] pgsql2shp

[Bob Pawley]

Hi

I am using the following

 pgsql2shp -f Test.shp -u postgres -P London2342 -k PDW p_id.image -g the_geom

to export a shape file from postgis.

Even tho I specify a single geometry column to be exported I get warnings that 
column names have been truncated.

Command prompt also informs me that "No prj file will be generated"

Also a warning 'cannot determine spatial reference". I thought the default was 
-1?

I also get messages-
 "Done (postgis major version: 1)
Output shape: Polyline
Dumping: xx [8 rows]"

What am I doing wrong???

Bob

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] pgsql2shp

[Raffaele Morelli]

Hi,

I am working with R and using pgsql2shp to export shape files from pg. The
shapes are created by pgsql2shp but when I try to import them in a R
enviromnent I get this error

Error in res[i, ] <- Map$Shapes[[i]]$verts : replacement has length zero

I can load the shape files in Qgis but not in R. Moreover, if I load one of
those files in Qgis and then export it again... this file is correctly
loaded by R.

I really cant' guess what's wrong with pgsql2shp. Any ideas?

Regards
Raffaele

-- 
*L'unica speranza di catarsi, ammesso che ne esista una, resta affidata
all'istinto di ribellione, alla rivolta non isterilita in progetti, alla
protesta violenta e viscerale.*
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] pgsql2shp

[toni hernández]

Hi,

I have some issues with pgsql2shp.

I am using postgis 1.5.3 over postgresql 9.0 on windows.
The database I am working with is utf8.

I have several spatial tables and I want some of them to be exported to 
a shape file.
pgsql2shp seems to be working fine but when I load the generated shape 
files, then  they appear to be empty. No error thrown (on OpenJump).


I have been testing it for a while and I came to realize that pgsql2shp 
does not like tables that only have one field (the spatial one). I can 
not export a table (any) with only one column but if I add a column to 
that very table (type character varying) then the exportation works fine 
and I can load the shp file perfectly


At first I thought it was something about the encoding but now I am lost.

Any ideas how to solve this?
thanks
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] pgsql2shp

[James Sewell]

Hello,

When I run pgsql2shp like so:

  pgsql2shp -u plm25 -g the_geom database table

I get the following output:


Xcolumn number -1 is out of range 0..1
column number -1 is out of range 0..1
column number -1 is out of range 0..1
column number -1 is out of range 0..1
column number -1 is out of range 0..1
column number -1 is out of range 0..1
column number -1 is out of range 0..1
column number -1 is out of range 0..1
column number -1 is out of range 0..1
column number -1 is out of range 0..1
column number -1 is out of range 0..1
column number -1 is out of range 0..1

This continues in blocks for (I assume) each row in the table.

When I go to load the shape I get null values for all rows as follows:

INSERT INTO "table" (the_geom) VALUES (NULL);

My versions are:

PostgreSQL 9.1.4 on sparc-sun-solaris2.10, compiled by cc: Sun C 5.11 
SunOS_sparc 145354-05 2011/09/13, 64-bit

POSTGIS="2.0.1 r9979" GEOS="3.3.5-CAPI-1.7.5" PROJ="Rel. 4.8.0, 6 March 2012" 
GDAL="GDAL 1.9.1, released 2012/05/15 GDAL_DATA not found" LIBXML="2.7.8" 
LIBJSON="UNKNOWN" R
ASTER

Any ideas? This is a fresh table, not migrated form an old version.

Cheers,


James Sewell
Solutions Architect
LISAsoft



The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this email 
is free of viruses or other defects. If you have received this communication in 
error, you may not copy or distribute any part of it or otherwise disclose its 
contents to anyone. Please advise the sender of your incorrect receipt of this 
correspondence.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] pgsql2shp help

[Dara Olson]

Greetings.  I am VERY new to all of this and teaching myself, but I am 
attempting to convert one of the tables back to an ESRI shapefile using the 
following:

pgsql2shp -f d:/shapefiles/dara -p 5432 -U dara -g the_geom postgis invspp

[postgis (database) and invspp (table)]

I get an error stating "invalid option or command parameters".  I have done 
many searches and used various examples, but can't seem to get it to work.  
Would anyone know what is wrong with the command?
Any help would be greatly appreciated!
miigwech!___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] pgsql2shp problem

[Murray Richardson]

Hello postgis users,

I frequently have a problem when running the pgsql2shp dumper whereby 
the program is executed but it does not actually  complete.  It only 
creates an empty shapefile.  The Dumping output  indicates only a single 
"X", as shown below:


C:\Program Files\PostgreSQL\8.3\bin>pgsql2shp -u postgres -P creemore -f 
V:\Lida

rData\utm17_3.shp spatialDB bare_earth_utm17
Initializing... Done (postgis major version: 1).
Output shape: Point
Dumping: X
C:\Program Files\PostgreSQL\8.3\bin>

This results in an empty shapefile.

Often, the execution works properly on the second or third attempt.  In 
general this is not a problem except when I need to run it 
programmatically, in which case I need a more reliable execution.


Any ideas?

Thanks

Murray
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] pgsql2shp usage

[Anisha Kaul]

Hello to all,

I hope this is the right mailing lists w.r.t pgsql2shp.

I downloaded an OSM map file namely "india.osm.bz2".
I transported it in PostgreSQL database "gis" with the command :

./osm2pgsql -m -d gis india.osm.bz2 

The above command resulted in the creation of the following tables filled with 
data in database "gis"

gis=# \d
 List of relations
 Schema |Name|   Type   |  Owner
++--+--
 public | geometry_columns   | table| anisha
 public | planet_osm_line| table| anisha
 public | planet_osm_point   | table| anisha
 public | planet_osm_polygon | table| anisha
 public | planet_osm_roads   | table| anisha
 public | polygon_oid_seq| sequence | postgres
 public | spatial_ref_sys| table| anisha

Now to convert the map data of one of the above tables in a shape file, I 
issued the following command:

pgsql2shp -f myfile gis planet_osm_line

Output:
Initializing... Warning, field addr:housenumber renamed to ADDR:HOUSE
Warning, field addr:interpolation renamed to ADDR:INTER
Warning, field admin_level renamed to ADMIN_LEVE
Warning, field construction renamed to CONSTRUCTI
Warning, field power_source renamed to POWER_SOUR
Done (postgis major version: 1).
Output shape: PolyLine
Dumping: 
XX
 
X
 
[126149 rows].

Questions :
___
Are these the right steps to load an OSM file in postgres ?
How should I verify that the shape file resultant of the above command is a 
valid and uncorrupted one ?
___

I am worried because the projection file namely "myfile.prj" contains the 
following !

PROJCS["WGS84 / Simple Mercator",GEOGCS["WGS 
84",DATUM["WGS_1984",SPHEROID["WGS_1984", 6378137.0, 
298.257223563]],PRIMEM["Greenwich", 0.0],UNIT["degree", 
0.017453292519943295],AXIS["Longitude", 
EAST],AXIS["Latitude", 
NORTH]],PROJECTION["Mercator_1SP_Google"],PARAMETER["latitude_of_origin", 
0.0],PARAMETER["central_meridian", 0.0],PARAMETER["scale_factor", 
1.0],PARAMETER["false_easting", 
0.0],PARAMETER["false_northing", 0.0],UNIT["m", 1.0],AXIS["x", EAST],AXIS["y", 
NORTH],AUTHORITY["EPSG","900913"]]

The latitude/longitude values don't seem to be of India 

Kindly help !!
Thanking you in anticipation,

Regards,
Anisha Kaul

-
Hi-Tech Gears Limited, Gurgaon, India




___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] pgsql2shp error

[smas036]

Hello, I am trying to output a shapefile from PostGIS using a command with
the following format:

pgsql2shp -f [path] -h [myhost] -p [port] -u [user] -P [password] [database]
"SELECT * FROM table1 WHERE id < '20'"

But I receive the following error:

Preparing table for user query... Failed: ERROR: column "id" does not exist

I am able to access this column from the PostgreSQL shell. Any ideas?

Cheers

Sina
-- 
View this message in context: 
http://old.nabble.com/pgsql2shp-error-tp29384180p29384180.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] pgsql2shp fails

[Denis Rykov]

Hi, just updated to 2.0svn and it seems that new pgsql2shp fails with new
db, but works with 1.5 ones, pgsql2shp from 1.5 works fine with both
keeps saying "column "gid" does not exist" even on fresh tables where SELECT
AddGeometryColumn was done
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] pgsql2shp revision

[Denis Rykov]

Is the pgsl2shp show correct SVN version of revision?
After updated pgsql2shp from svn and start pgsql2shp without options I get:

RCSID: $Id: pgsql2shp-core.h 5870 2010-08-28 09:16:32Z mcayland $ RELEASE:
> 2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1


But last SVN revision number is not 5870.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp

[Stephen Woodbridge]

On 10/10/2010 1:52 PM, Bob Pawley wrote:

Hi
I am using the following
pgsql2shp -f Test.shp -u postgres -P London2342 -k PDW p_id.image -g
the_geom
to export a shape file from postgis.
Even tho I specify a single geometry column to be exported I get
warnings that column names have been truncated.


DBF files can only have a maximum of 11 character column names. You 
probably have some that are longer. You can work around this problem by 
using a query at the end of the commandline like:


'select col1, col2, col3whichisverylong as col3, ... the_geom from 
p_id.image'



Command prompt also informs me that "No prj file will be generated"
Also a warning 'cannot determine spatial reference". I thought the
default was -1?


-1 is the default and it means that you have not defined a projection so 
it can not create a .prj file. It is generally a good idea to always 
define a projection for your data.


-Steve W


I also get messages-
"Done (postgis major version: 1)
Output shape: Polyline
Dumping: xx [8 rows]"
What am I doing wrong???
Bob



___
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] pgsql2shp

[Bob Pawley]

I'm still having problems.

My pgsql2shp version is -
RCSID: $Id: pgsql2shp.c 4914 2009-11-27 18:12:43Z  pramsey $ RELEASE: 1.4 


Is this the right version to be using on Win 7??

Bob

--
From: "Stephen Woodbridge" 
Sent: Sunday, October 10, 2010 11:14 AM
To: 
Subject: Re: [postgis-users] pgsql2shp


On 10/10/2010 1:52 PM, Bob Pawley wrote:

Hi
I am using the following
pgsql2shp -f Test.shp -u postgres -P London2342 -k PDW p_id.image -g
the_geom
to export a shape file from postgis.
Even tho I specify a single geometry column to be exported I get
warnings that column names have been truncated.


DBF files can only have a maximum of 11 character column names. You 
probably have some that are longer. You can work around this problem by 
using a query at the end of the commandline like:


'select col1, col2, col3whichisverylong as col3, ... the_geom from 
p_id.image'



Command prompt also informs me that "No prj file will be generated"
Also a warning 'cannot determine spatial reference". I thought the
default was -1?


-1 is the default and it means that you have not defined a projection so 
it can not create a .prj file. It is generally a good idea to always 
define a projection for your data.


-Steve W


I also get messages-
"Done (postgis major version: 1)
Output shape: Polyline
Dumping: xx [8 rows]"
What am I doing wrong???
Bob



___
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] pgsql2shp

[Mike Toews]

As far as I know, all versions of pgsql2shp work just fine on Win7,
and they should do exactly what they are documented to do for the
situation you describe. What exactly are your problems? If the command
had an error and didn't produce any files, what did you type?

-Mike

On 10 October 2010 14:03, Bob Pawley  wrote:
> I'm still having problems.
>
> My pgsql2shp version is -
> RCSID: $Id: pgsql2shp.c 4914 2009-11-27 18:12:43Z  pramsey $ RELEASE: 1.4
> Is this the right version to be using on Win 7??
>
> Bob
>
> --
> From: "Stephen Woodbridge" 
> Sent: Sunday, October 10, 2010 11:14 AM
> To: 
> Subject: Re: [postgis-users] pgsql2shp
>
>> On 10/10/2010 1:52 PM, Bob Pawley wrote:
>>>
>>> Hi
>>> I am using the following
>>> pgsql2shp -f Test.shp -u postgres -P London2342 -k PDW p_id.image -g
>>> the_geom
>>> to export a shape file from postgis.
>>> Even tho I specify a single geometry column to be exported I get
>>> warnings that column names have been truncated.
>>
>> DBF files can only have a maximum of 11 character column names. You
>> probably have some that are longer. You can work around this problem by
>> using a query at the end of the commandline like:
>>
>> 'select col1, col2, col3whichisverylong as col3, ... the_geom from
>> p_id.image'
>>
>>> Command prompt also informs me that "No prj file will be generated"
>>> Also a warning 'cannot determine spatial reference". I thought the
>>> default was -1?
>>
>> -1 is the default and it means that you have not defined a projection so
>> it can not create a .prj file. It is generally a good idea to always define
>> a projection for your data.
>>
>> -Steve W
>>
>>> I also get messages-
>>> "Done (postgis major version: 1)
>>> Output shape: Polyline
>>> Dumping: xx [8 rows]"
>>> What am I doing wrong???
>>> Bob
>>>
>>>
>>>
>>> ___
>>> 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] pgsql2shp

[Bob Pawley]

The reason I asked is that I had tyo upgrade pgdump in order to get it to 
work.


I'm using this (and variations of it)

pgsql2shp -f c:\user\bob\ShapeTest.shp -h localhost -u postgres -P 
London2342 PDW

"Select the_geom from p_id.image where p_id_id = '1534'"

or

pgsql2shp -f myfile.shp -h localhost -u postgres -P London2342 PDW 
p_id.image -g the_geom


The message just tells me that "No prj file will be generated".

I am using 8.4 if that helps.

Bob

--
From: "Mike Toews" 
Sent: Monday, October 11, 2010 1:30 PM
To: "PostGIS Users Discussion" 
Subject: Re: [postgis-users] pgsql2shp


As far as I know, all versions of pgsql2shp work just fine on Win7,
and they should do exactly what they are documented to do for the
situation you describe. What exactly are your problems? If the command
had an error and didn't produce any files, what did you type?

-Mike

On 10 October 2010 14:03, Bob Pawley  wrote:

I'm still having problems.

My pgsql2shp version is -
RCSID: $Id: pgsql2shp.c 4914 2009-11-27 18:12:43Z  pramsey $ RELEASE: 1.4
Is this the right version to be using on Win 7??

Bob

--
From: "Stephen Woodbridge" 
Sent: Sunday, October 10, 2010 11:14 AM
To: 
Subject: Re: [postgis-users] pgsql2shp


On 10/10/2010 1:52 PM, Bob Pawley wrote:


Hi
I am using the following
pgsql2shp -f Test.shp -u postgres -P London2342 -k PDW p_id.image -g
the_geom
to export a shape file from postgis.
Even tho I specify a single geometry column to be exported I get
warnings that column names have been truncated.


DBF files can only have a maximum of 11 character column names. You
probably have some that are longer. You can work around this problem by
using a query at the end of the commandline like:

'select col1, col2, col3whichisverylong as col3, ... the_geom from
p_id.image'


Command prompt also informs me that "No prj file will be generated"
Also a warning 'cannot determine spatial reference". I thought the
default was -1?


-1 is the default and it means that you have not defined a projection so
it can not create a .prj file. It is generally a good idea to always 
define

a projection for your data.

-Steve W


I also get messages-
"Done (postgis major version: 1)
Output shape: Polyline
Dumping: xx [8 rows]"
What am I doing wrong???
Bob



___
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] pgsql2shp

[Paragon Corporation]

Bob,

You can ignore that notice.  If you are getting no data, then it usually
means your tables are empty or there is some other problem which it should
notify you of.

The "No proj file will be generated" is just a warning that your data will
lack the meta data required to reproject it with common GIS software like
ArcGIS or MapInfo.  If you don't care about people overlaying data in
various projections on top of yours, you can safely ignore that warning.

As Steve mentioned, its generally good to have a spatial reference system
defined for your data, but as we recall you saying in the past, you are
using PostGIS to do process modeling, so the whole spatial reference system
argument is pretty irrelevant for your purposes.

To answer your question -- 1.4 is not the latest on windows -- there is a
PostGIS 1.5.2 pgsql2shp which I think has some bug fixes that the 1.4 is
currently lacking.  If you don't want to install 1.5, you can just download
the binaries and use the packaged pgsql2shp in the bin folder.  It will work
fine with a  PostGIS 1.4 database.

http://www.postgis.org/download/windows/#windbinaries

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 Bob
Pawley
Sent: Monday, October 11, 2010 6:26 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] pgsql2shp

The reason I asked is that I had tyo upgrade pgdump in order to get it to
work.

I'm using this (and variations of it)

pgsql2shp -f c:\user\bob\ShapeTest.shp -h localhost -u postgres -P
London2342 PDW
"Select the_geom from p_id.image where p_id_id = '1534'"

or

pgsql2shp -f myfile.shp -h localhost -u postgres -P London2342 PDW
p_id.image -g the_geom

The message just tells me that "No prj file will be generated".

I am using 8.4 if that helps.

Bob

--
From: "Mike Toews" 
Sent: Monday, October 11, 2010 1:30 PM
To: "PostGIS Users Discussion" 
Subject: Re: [postgis-users] pgsql2shp

> As far as I know, all versions of pgsql2shp work just fine on Win7, 
> and they should do exactly what they are documented to do for the 
> situation you describe. What exactly are your problems? If the command 
> had an error and didn't produce any files, what did you type?
>
> -Mike
>
> On 10 October 2010 14:03, Bob Pawley  wrote:
>> I'm still having problems.
>>
>> My pgsql2shp version is -
>> RCSID: $Id: pgsql2shp.c 4914 2009-11-27 18:12:43Z  pramsey $ RELEASE: 
>> 1.4 Is this the right version to be using on Win 7??
>>
>> Bob
>>
>> ------
>> From: "Stephen Woodbridge" 
>> Sent: Sunday, October 10, 2010 11:14 AM
>> To: 
>> Subject: Re: [postgis-users] pgsql2shp
>>
>>> On 10/10/2010 1:52 PM, Bob Pawley wrote:
>>>>
>>>> Hi
>>>> I am using the following
>>>> pgsql2shp -f Test.shp -u postgres -P London2342 -k PDW p_id.image 
>>>> -g the_geom to export a shape file from postgis.
>>>> Even tho I specify a single geometry column to be exported I get 
>>>> warnings that column names have been truncated.
>>>
>>> DBF files can only have a maximum of 11 character column names. You 
>>> probably have some that are longer. You can work around this problem 
>>> by using a query at the end of the commandline like:
>>>
>>> 'select col1, col2, col3whichisverylong as col3, ... the_geom from 
>>> p_id.image'
>>>
>>>> Command prompt also informs me that "No prj file will be generated"
>>>> Also a warning 'cannot determine spatial reference". I thought the 
>>>> default was -1?
>>>
>>> -1 is the default and it means that you have not defined a 
>>> projection so it can not create a .prj file. It is generally a good 
>>> idea to always define a projection for your data.
>>>
>>> -Steve W
>>>
>>>> I also get messages-
>>>> "Done (postgis major version: 1)
>>>> Output shape: Polyline
>>>> Dumping: xx [8 rows]"
>>>> What am I doing wrong???
>>>> Bob
>>>>
>>>>
>>>>
>>>> ___
>>>> postgis-users mailing list
>>>> postgis-users@postgis.refractions.net
>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>> ___
>>>

Re: [postgis-users] pgsql2shp

[Ben Madin]

Raffaele

Have you considered using gdal to directly bring the data into R :

using something like :

library(rgdal)
mymap <- readOGR("PG:dbname=mydbname user=mydbuser password=mydbpassword", 
"map")

cheers

Ben



On 26/09/2011, at 4:42 PM, Raffaele Morelli wrote:

> Hi,
> 
> I am working with R and using pgsql2shp to export shape files from pg. The 
> shapes are created by pgsql2shp but when I try to import them in a R 
> enviromnent I get this error
> 
> Error in res[i, ] <- Map$Shapes[[i]]$verts : replacement has length zero
> 
> I can load the shape files in Qgis but not in R. Moreover, if I load one of 
> those files in Qgis and then export it again... this file is correctly loaded 
> by R.
> 
> I really cant' guess what's wrong with pgsql2shp. Any ideas?
> 
> Regards
> Raffaele
> 
> -- 
> L'unica speranza di catarsi, ammesso che ne esista una, resta affidata 
> all'istinto di ribellione, alla rivolta non isterilita in progetti, alla 
> protesta violenta e viscerale.
> ___
> 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] pgsql2shp

[Mark Cave-Ayland]

On 26/09/11 09:42, Raffaele Morelli wrote:


Hi,

I am working with R and using pgsql2shp to export shape files from pg.
The shapes are created by pgsql2shp but when I try to import them in a R
enviromnent I get this error

Error in res[i, ] <- Map$Shapes[[i]]$verts : replacement has length zero

I can load the shape files in Qgis but not in R. Moreover, if I load one
of those files in Qgis and then export it again... this file is
correctly loaded by R.

I really cant' guess what's wrong with pgsql2shp. Any ideas?

Regards
Raffaele


Hi Raffaele,

Are you able to provide an example shapefile for us to look at? And also 
which version of PostGIS/shp2pgsql are you using?



ATB,

Mark.

--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] pgsql2shp error

[Christian Jauvin]

Hi,

I am trying to use pgsql2shp on Ubuntu 10.04 64-bit:

$ dpkg -l | grep postgis
ii  postgis  1.5.2-2~lucid1
ii  postgresql-8.4-postgis   1.5.2-2~lucid1

The problem:

$ pgsql2shp geo_db "select * from sdr_2001"
Preparing table for user query... Done.
Initializing... Geometry type unknown (have you enabled postgis?)
WARNING: Cannot determine spatial reference (empty table or unknown
spatial ref). No prj file will be generated.
Done (postgis major version: 1).
Output shape: Polygon
Dumping:  [4700 rows].

"geo_db" is a perfectly valid PostGIS database:

geo_db=# select postgis_full_version();
  postgis_full_version

 POSTGIS="1.4.0" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23
September 2009" USE_STATS

Any idea about what I'm doing wrong?

Thanks,

Christian
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp

[Mark Cave-Ayland]

On 01/12/11 08:32, toni hernández wrote:


Hi,

I have some issues with pgsql2shp.

I am using postgis 1.5.3 over postgresql 9.0 on windows.
The database I am working with is utf8.

I have several spatial tables and I want some of them to be exported to
a shape file.
pgsql2shp seems to be working fine but when I load the generated shape
files, then they appear to be empty. No error thrown (on OpenJump).

I have been testing it for a while and I came to realize that pgsql2shp
does not like tables that only have one field (the spatial one). I can
not export a table (any) with only one column but if I add a column to
that very table (type character varying) then the exportation works fine
and I can load the shp file perfectly

At first I thought it was something about the encoding but now I am lost.

Any ideas how to solve this?
thanks


Hi Toni,

I don't suppose you've been able to try pgsql2shp from one of the 
PostGIS 2.0 pre-releases at all? It's had a fairly hefty rewrite and so 
you might find that this will have solved the issue.



ATB,

Mark.

--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp

[toni hernández]

Mark,

No, I haven't.
Right now I am preparing some material for a course and I need some 
stable release but I'll keep that  in mind.

Thanks.

On 01/12/2011 11:50, Mark Cave-Ayland wrote:

It's had a fairly hefty rewrite

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp

[Mark Cave-Ayland]

On 03/07/12 07:14, James Sewell wrote:


Hello,

When I run pgsql2shp like so:

   pgsql2shp -u plm25 -g the_geom database table

I get the following output:


Xcolumn number -1 is out of range 0..1
column number -1 is out of range 0..1
column number -1 is out of range 0..1
column number -1 is out of range 0..1
column number -1 is out of range 0..1
column number -1 is out of range 0..1
column number -1 is out of range 0..1
column number -1 is out of range 0..1
column number -1 is out of range 0..1
column number -1 is out of range 0..1
column number -1 is out of range 0..1
column number -1 is out of range 0..1

This continues in blocks for (I assume) each row in the table.

When I go to load the shape I get null values for all rows as follows:

INSERT INTO "table" (the_geom) VALUES (NULL);

My versions are:

PostgreSQL 9.1.4 on sparc-sun-solaris2.10, compiled by cc: Sun C 5.11 
SunOS_sparc 145354-05 2011/09/13, 64-bit

POSTGIS="2.0.1 r9979" GEOS="3.3.5-CAPI-1.7.5" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.1, released 
2012/05/15 GDAL_DATA not found" LIBXML="2.7.8" LIBJSON="UNKNOWN" R
ASTER

Any ideas? This is a fresh table, not migrated form an old version.


That's strange - I don't see that error message within the PostGIS 
codebase at all, so maybe it is coming from libpq. Do you see that error 
with just that one table, or with a shapefile you have loaded using 
shp2pgsql?


Any chance of sharing your table schema with us, e.g. the output of \d 
 in psql?



ATB,

Mark.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp help

[lisek lichu]

use that:

pgsql2shp -f output.shp -P password -u user -g the_geom dbname tablemane

in my databaseit works :P

On Fri, Oct 31, 2008 at 4:50 PM, Dara Olson <[EMAIL PROTECTED]> wrote:
> Greetings.  I am VERY new to all of this and teaching myself, but I am
> attempting to convert one of the tables back to an ESRI shapefile using the
> following:
>
> pgsql2shp -f d:/shapefiles/dara -p 5432 -U dara -g the_geom postgis invspp
>
> [postgis (database) and invspp (table)]
>
> I get an error stating "invalid option or command parameters".  I have done
> many searches and used various examples, but can't seem to get it to work.
> Would anyone know what is wrong with the command?
> Any help would be greatly appreciated!
> miigwech!
> ___
> 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] pgsql2shp help

[lisek lichu]

You need to look when You are using UPPERCASE options beacuse that matters :)
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

RE: [postgis-users] pgsql2shp help

[Burgholzer,Robert]

Dara,

I think that it might have a problem with your file path. As I think the
previous respondent was indicating, it may work OK if you use a file
name without directory prefixes.

 

Robert W. Burgholzer

Surface Water Modeler

Office of Water Supply and Planning

Virginia Department of Environmental Quality

[EMAIL PROTECTED]

804-698-4405

Open Source Modeling Tools:

http://sourceforge.net/projects/npsource/

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dara
Olson
Sent: Friday, October 31, 2008 12:51 PM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] pgsql2shp help

 

Greetings.  I am VERY new to all of this and teaching myself, but I am
attempting to convert one of the tables back to an ESRI shapefile using
the following:

 

pgsql2shp -f d:/shapefiles/dara -p 5432 -U dara -g the_geom postgis
invspp

 

[postgis (database) and invspp (table)]

 

I get an error stating "invalid option or command parameters".  I have
done many searches and used various examples, but can't seem to get it
to work.  Would anyone know what is wrong with the command?

Any help would be greatly appreciated!

miigwech!

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

RE: [postgis-users] pgsql2shp help

[Obe, Regina]

The U should be lower case u.  
pgsql2shp -f "/path/to/streets" -h myserver -u apguser -P apgpassword
mygisdb ma.streets
 
This cheatsheet might help
 
http://www.bostongis.com/pgsql2shp_shp2pgsql_quickguide.bqg?outputformat
=PDF
 
Hope that helps,
Regina



From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Burgholzer,Robert
Sent: Friday, October 31, 2008 4:01 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] pgsql2shp help



Dara,

I think that it might have a problem with your file path. As I think the
previous respondent was indicating, it may work OK if you use a file
name without directory prefixes.

 

Robert W. Burgholzer

Surface Water Modeler

Office of Water Supply and Planning

Virginia Department of Environmental Quality

[EMAIL PROTECTED]

804-698-4405

Open Source Modeling Tools:

http://sourceforge.net/projects/npsource/

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dara
Olson
Sent: Friday, October 31, 2008 12:51 PM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] pgsql2shp help

 

Greetings.  I am VERY new to all of this and teaching myself, but I am
attempting to convert one of the tables back to an ESRI shapefile using
the following:

 

pgsql2shp -f d:/shapefiles/dara -p 5432 -U dara -g the_geom postgis
invspp

 

[postgis (database) and invspp (table)]

 

I get an error stating "invalid option or command parameters".  I have
done many searches and used various examples, but can't seem to get it
to work.  Would anyone know what is wrong with the command?

Any help would be greatly appreciated!

miigwech!



-
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

RE: [postgis-users] pgsql2shp help

[Obe, Regina]

Dara,
 
I just noticed your confusion, and its PostGIS fault.  psql uses
uppercase U to denote user, and pgsql2shp uses lowercase u to denote
user.
 
Very confusing I know.  Just noticed it.  That must be immensely
frustrating.
 
 



From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Burgholzer,Robert
Sent: Friday, October 31, 2008 4:01 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] pgsql2shp help



Dara,

I think that it might have a problem with your file path. As I think the
previous respondent was indicating, it may work OK if you use a file
name without directory prefixes.

 

Robert W. Burgholzer

Surface Water Modeler

Office of Water Supply and Planning

Virginia Department of Environmental Quality

[EMAIL PROTECTED]

804-698-4405

Open Source Modeling Tools:

http://sourceforge.net/projects/npsource/

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dara
Olson
Sent: Friday, October 31, 2008 12:51 PM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] pgsql2shp help

 

Greetings.  I am VERY new to all of this and teaching myself, but I am
attempting to convert one of the tables back to an ESRI shapefile using
the following:

 

pgsql2shp -f d:/shapefiles/dara -p 5432 -U dara -g the_geom postgis
invspp

 

[postgis (database) and invspp (table)]

 

I get an error stating "invalid option or command parameters".  I have
done many searches and used various examples, but can't seem to get it
to work.  Would anyone know what is wrong with the command?

Any help would be greatly appreciated!

miigwech!



-
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp problem

[Mark Cave-Ayland]

Murray Richardson wrote:

Hello postgis users,

I frequently have a problem when running the pgsql2shp dumper whereby 
the program is executed but it does not actually  complete.  It only 
creates an empty shapefile.  The Dumping output  indicates only a single 
"X", as shown below:


C:\Program Files\PostgreSQL\8.3\bin>pgsql2shp -u postgres -P creemore -f 
V:\Lida

rData\utm17_3.shp spatialDB bare_earth_utm17
Initializing... Done (postgis major version: 1).
Output shape: Point
Dumping: X
C:\Program Files\PostgreSQL\8.3\bin>

This results in an empty shapefile.

Often, the execution works properly on the second or third attempt.  In 
general this is not a problem except when I need to run it 
programmatically, in which case I need a more reliable execution.


Any ideas?


Not sure, as a single X doesn't really give much in the way of debugging 
information :(  Some PostgreSQL on Windows users have reported problems 
with 3rd party firewalls and/or virus checkers interfering with normal 
operation - it may be worth trying to disable these temporarily and 
trying again.



HTH,

Mark.

--
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] pgsql2shp : Encoding headache

[Arnaud Lesauvage]

Hi all!

I have an UTF8 encoded shapefile, and an UTF8 encoded postgis-enabled 
database. I want my shapefile to be encoded in WIN1252, and a particular 
field to be in uppercase.


Since I am on windows, I don't have an iconv executable. Therefore, I am 
trying to :

- dump the shapefile with shp2pgsql to an sql text file
- load the resulting sql file into a postgresql table
- dump this table into a shapefile with pgsql2shp (in WIN1252)

To load the shapefile into postgresql, I had to dump it without 
specifying a '-W' argument, set my client_encoding to UTF8, then load 
the sql file into postgresql.


If I look at the data with pgAdmin (with client_encoding set to UTF8), 
it looks good : accentuation is fine, special characters are fine.


To dump the data in a WIN1252-encoded shapefile, pgsql2shp has no 
encoding argument, so I set my client encoding to WIN1252 thtough the 
environment variable PGCLIENTENCODING.


If I just dump the file this way, it seems to be fine. So this command 
works fine :
pgsql2shp -f myouput.shp -u postgres -g mygeom mydatabase "SELECT 
mytext, mygeom FROM mytemptable"

->  [621679 rows]

But then, if I dump it through a query to have my field in uppercase, I 
get an error 'character 0xc29f of encoding UTF8 has no equivalent in 
WIN1252' (translated by myself, the message is in French)

The command is simply :
pgsql2shp -f myouput.shp -u postgres -g mygeom mydatabase "SELECT 
upper(mytext) as mytext, mygeom FROM mytemptable"


So I guess there is a problem with my 'upper' conversion, but I have no 
idea what this 0xc29f character could be.


Any help would be greatly appreciated.
Thanks a lot !

--
Arnaud Lesauvage
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp usage

[Paragon Corporation]

Anish,

Question 1
The warnings.  That's because esri shape file format is dbf which only
allows 10 characters for a field name.  So the warning is telling you its
truncating the names so they are within 10 characters.  Your field names in
the dbf will be slightly different is all

Question 2
Never worked with osm data, but since I created the .prj generator in
pgsql2shp, I can tell you it reads the srid of the geometry and looks up the

srtext in spatial ref and outputs that to the .prj file.

I suspect osm data is in Mercator not long lat.  If you want your output to
be in long lat, you need to transform and use an sql statement

pgsql2shp -f "/path/to/filename" -h myserver -u apguser -P apgpassword
mygisdb 
"SELECT field1 AS hs_num, field2, ST_Transform(the_geom,4326) As
the_geom FROM planet_osm_line"


Or you can create a view with a transformed geometry.  

Using an SQL statement or a view will also allow you to control how the
fields are renamed since you can alias them as names less than 10
characters,


Hope that helps,
Regina  

-Original Message-
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Anisha
Kaul
Sent: Saturday, January 09, 2010 7:37 AM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] pgsql2shp usage

Hello to all,

I hope this is the right mailing lists w.r.t pgsql2shp.

I downloaded an OSM map file namely "india.osm.bz2".
I transported it in PostgreSQL database "gis" with the command :

./osm2pgsql -m -d gis india.osm.bz2 

The above command resulted in the creation of the following tables filled
with data in database "gis"

gis=# \d
 List of relations
 Schema |Name|   Type   |  Owner
++--+--
 public | geometry_columns   | table| anisha
 public | planet_osm_line| table| anisha
 public | planet_osm_point   | table| anisha
 public | planet_osm_polygon | table| anisha
 public | planet_osm_roads   | table| anisha
 public | polygon_oid_seq| sequence | postgres
 public | spatial_ref_sys| table| anisha

Now to convert the map data of one of the above tables in a shape file, I
issued the following command:

pgsql2shp -f myfile gis planet_osm_line

Output:
Initializing... Warning, field addr:housenumber renamed to ADDR:HOUSE
Warning, field addr:interpolation renamed to ADDR:INTER Warning, field
admin_level renamed to ADMIN_LEVE Warning, field construction renamed to
CONSTRUCTI Warning, field power_source renamed to POWER_SOUR Done (postgis
major version: 1).
Output shape: PolyLine
Dumping: 













XX
 



X
[126149 rows].

Questions :

___
Are these the right steps to load an OSM file in postgres ?
How should I verify that the shape file resultant of the above command is a
valid and uncorrupted one ?

___

I am worried because the projection file namely "myfile.prj" contains the
following !

PROJCS["WGS84 / Simple Mercator",GEOGCS["WGS
84",DATUM["WGS_1984",SPHEROID["WGS_1984", 6378137.0,
298.257223563]],PRIMEM["Greenwich", 0.0],UNIT["degree",
0.017453292519943295],AXIS["Longitude",
EAST],AXIS["Latitude",
NORTH]],PROJECTION["Mercator_1SP_Google"],PARAMETER["latitude_of_origin",
0.0],PARAMETER["central_meridian", 0.0],PARAMETER["scale_factor",
1.0],PARAMETER["false_easting", 0.0],

Re: [postgis-users] pgsql2shp usage

[Chris Hermansen]

Anisha, why are you converting to shape file?
Chris Hermansenchris.herman...@timberline.ca
tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.840.4625
Timberline Natural Resource Group · www.timberline.ca
401 · 958 West 8th Avenue  · Vancouver BC · Canada · V5Z 1E5


-Original Message-
From: Anisha Kaul 
Date: Sat, 9 Jan 2010 18:06:34 
To: 
Subject: [postgis-users] pgsql2shp usage

Hello to all,

I hope this is the right mailing lists w.r.t pgsql2shp.

I downloaded an OSM map file namely "india.osm.bz2".
I transported it in PostgreSQL database "gis" with the command :

./osm2pgsql -m -d gis india.osm.bz2 

The above command resulted in the creation of the following tables filled with 
data in database "gis"

gis=# \d
 List of relations
 Schema |Name|   Type   |  Owner
++--+--
 public | geometry_columns   | table| anisha
 public | planet_osm_line| table| anisha
 public | planet_osm_point   | table| anisha
 public | planet_osm_polygon | table| anisha
 public | planet_osm_roads   | table| anisha
 public | polygon_oid_seq| sequence | postgres
 public | spatial_ref_sys| table| anisha

Now to convert the map data of one of the above tables in a shape file, I 
issued the following command:

pgsql2shp -f myfile gis planet_osm_line

Output:
Initializing... Warning, field addr:housenumber renamed to ADDR:HOUSE
Warning, field addr:interpolation renamed to ADDR:INTER
Warning, field admin_level renamed to ADMIN_LEVE
Warning, field construction renamed to CONSTRUCTI
Warning, field power_source renamed to POWER_SOUR
Done (postgis major version: 1).
Output shape: PolyLine
Dumping: 
XX
 
X
 
[126149 rows].

Questions :
___
Are these the right steps to load an OSM file in postgres ?
How should I verify that the shape file resultant of the above command is a 
valid and uncorrupted one ?
___

I am worried because the projection file namely "myfile.prj" contains the 
following !

PROJCS["WGS84 / Simple Mercator",GEOGCS["WGS 
84",DATUM["WGS_1984",SPHEROID["WGS_1984", 6378137.0, 
298.257223563]],PRIMEM["Greenwich", 0.0],UNIT["degree", 
0.017453292519943295],AXIS["Longitude", 
EAST],AXIS["Latitude", 
NORTH]],PROJECTION["Mercator_1SP_Google"],PARAMETER["latitude_of_origin", 
0.0],PARAMETER["central_meridian", 0.0],PARAMETER["scale_factor", 
1.0],PARAMETER["false_easting", 
0.0],PARAMETER["false_northing", 0.0],UNIT["m", 1.0],AXIS["x", EAST],AXIS["y", 
NORTH],AUTHORITY["EPSG","900913"]]

The latitude/longitude values don't seem to be of India 

Kindly help !!
Thanking you in anticipation,

Regards,
Anisha Kaul

-
Hi-Tech Gears Limited, Gurgaon, India




___
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] pgsql2shp usage

[Anisha Kaul]

Hi,

Many thanks to all of you for bothering to reply !!

>From Chris Hermansen 
> Anisha, why are you converting to shape file?

I failed to load the map directly from the database !
So I thought it will be a boostup for me if I manage to load the map from a 
shape file, and then I can go for the 
database reading !

I didn't have any problem in loading a world map from a shape file directly 
downloaded from the internet, but I 
am facing problems when I convert the postgres data in a shape file and then 
try to load it.

>From Regina
> Never worked with osm data, but since I created the .prj generator in
> pgsql2shp, I can tell you it reads the srid of the geometry and looks up the

> srtext in spatial ref and outputs that to the .prj file.

> I suspect osm data is in Mercator not long lat.  If you want your output to
> be in long lat, you need to transform and use an sql statement

> pgsql2shp -f "/path/to/filename" -h myserver -u apguser -P apgpassword
> mygisdb 
>   "SELECT field1 AS hs_num, field2, ST_Transform(the_geom,4326) As
> the_geom FROM planet_osm_line"

I tried what u said as follows:
__
pgsql2shp -f ../data/myfile gis "SELECT *, ST_Transform(way,4326) As the_geom 
FROM planet_osm_line"

Output:

Preparing table for user query... Failed: ERROR:  AddToPROJ4SRSCache: Cannot 
find SRID (4326) in spatial_ref_sys

__

Then With reference to this 
http://postgis.refractions.net/pipermail/postgis-users/2008-February/018486.html
 web 
page I executed the following in the postgres database

__
gis=# SELECT UpdateGeometrySRID ('public', 'planet_osm_polygon','way', 4236);

Output:

ERROR:  constraint "enforce_srid_way" of relation "planet_osm_polygon" does not 
exist
CONTEXT:  SQL statement "ALTER TABLE public.planet_osm_polygon DROP constraint 
enforce_srid_way"
PL/pgSQL function "updategeometrysrid" line 53 at EXECUTE statement
SQL statement "SELECT UpdateGeometrySRID('', $1 , $2 , $3 , $4 )"
PL/pgSQL function "updategeometrysrid" line 4 at SQL statement
__

I don't know what to do now, please guide !

-- 
Regards,
Anisha Kaul


-
Hi-Tech Gears Limited, Gurgaon, India

<>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp usage

[Anisha Kaul]

Hi,

In continuation from the previous mail, 

I droped the database "gis", and recreated it !

Now when I did execute the following command with the options changed

./osm2pgsql -l -E EPSG:4326 -d gis ~/mapnik-0.6.0/osmFiles/india.osm.bz2


It resulted in the following output:

osm2pgsql SVN version 0.69-19270

Using projection SRS 4326 (Latlong)
Setting up table: planet_osm_point
NOTICE:  table "planet_osm_point" does not exist, skipping
NOTICE:  table "planet_osm_point_tmp" does not exist, skipping
SELECT AddGeometryColumn('planet_osm_point', 'way', 4326, 'POINT', 2 );
 failed: ERROR:  AddGeometryColumns() - invalid SRID
CONTEXT:  SQL statement "SELECT AddGeometryColumn('','', $1 , $2 , $3 , $4 , $5 
)"
PL/pgSQL function "addgeometrycolumn" line 4 at SQL statement

Error occurred, cleaning up


Then I executed the following command:
psql -f 900913.sql -d gis

It resulted in the following output:
INSERT 0 1


Then I executed the following command 

./osm2pgsql -l -E EPSG:4326 -d gis ~/mapnik-0.6.0/osmFiles/india.osm.bz2


and again it resulted in the following output:

osm2pgsql SVN version 0.69-19270

Using projection SRS 4326 (Latlong)
Setting up table: planet_osm_point
NOTICE:  table "planet_osm_point" does not exist, skipping
NOTICE:  table "planet_osm_point_tmp" does not exist, skipping
SELECT AddGeometryColumn('planet_osm_point', 'way', 4326, 'POINT', 2 );
 failed: ERROR:  AddGeometryColumns() - invalid SRID
CONTEXT:  SQL statement "SELECT AddGeometryColumn('','', $1 , $2 , $3 , $4 , $5 
)"
PL/pgSQL function "addgeometrycolumn" line 4 at SQL statement

Error occurred, cleaning up


Kindly guide !

-- 
Regards,
Anisha Kaul

-
Hi-Tech Gears Limited, Gurgaon, India




___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp usage

[Anisha Kaul]

Hi,

Many thanks to all of you for bothering to reply !!

>From Chris Hermansen 
> Anisha, why are you converting to shape file?

I failed to load the map directly from the database !
So I thought it will be a boostup for me if I manage to load the map from a 
shape file, and then I can go for the 
database reading !

I didn't have any problem in loading a world map from a shape file directly 
downloaded from the internet, but I 
am facing problems when I convert the postgres data in a shape file and then 
try to load it.

>From Regina
> Never worked with osm data, but since I created the .prj generator in
> pgsql2shp, I can tell you it reads the srid of the geometry and looks up the

> srtext in spatial ref and outputs that to the .prj file.

> I suspect osm data is in Mercator not long lat.  If you want your output to
> be in long lat, you need to transform and use an sql statement

> pgsql2shp -f "/path/to/filename" -h myserver -u apguser -P apgpassword
> mygisdb 
>   "SELECT field1 AS hs_num, field2, ST_Transform(the_geom,4326) As
> the_geom FROM planet_osm_line"

I tried what u said as follows:
__
pgsql2shp -f ../data/myfile gis "SELECT *, ST_Transform(way,4326) As the_geom 
FROM planet_osm_line"

Output:

Preparing table for user query... Failed: ERROR:  AddToPROJ4SRSCache: Cannot 
find SRID (4326) in spatial_ref_sys

__

Then With reference to this 
http://postgis.refractions.net/pipermail/postgis-users/2008-February/018486.html
 web 
page I executed the following in the postgres database

__
gis=# SELECT UpdateGeometrySRID ('public', 'planet_osm_polygon','way', 4236);

Output:

ERROR:  constraint "enforce_srid_way" of relation "planet_osm_polygon" does not 
exist
CONTEXT:  SQL statement "ALTER TABLE public.planet_osm_polygon DROP constraint 
enforce_srid_way"
PL/pgSQL function "updategeometrysrid" line 53 at EXECUTE statement
SQL statement "SELECT UpdateGeometrySRID('', $1 , $2 , $3 , $4 )"
PL/pgSQL function "updategeometrysrid" line 4 at SQL statement
__

I don't know what to do now, please guide !

-- 
Regards,
Anisha Kaul

-
Hi-Tech Gears Limited, Gurgaon, India




___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp usage

[Anisha Kaul]

Hi,

The data was missing in my previous mails  i.e.
http://postgis.refractions.net/pipermail/postgis-users/2010-January/025560.html

Many thanks to all of you for bothering to reply !!

Chris Hermansen wrote:
> Anisha, why are you converting to shape file?

I failed to load the map directly from the database !
So I thought it will be a boostup for me if I manage to load the map from a 
shape file, and then I can go for the 
database reading !

I didn't have any problem in loading a world map from a shape file directly 
downloaded from the internet, but I 
am facing problems when I convert the postgres data in a shape file and then 
try to load it.

Regina wrote:
> Never worked with osm data, but since I created the .prj generator in
> pgsql2shp, I can tell you it reads the srid of the geometry and looks up the

> srtext in spatial ref and outputs that to the .prj file.

> I suspect osm data is in Mercator not long lat.  If you want your output to
> be in long lat, you need to transform and use an sql statement

> pgsql2shp -f "/path/to/filename" -h myserver -u apguser -P apgpassword
> mygisdb 
>   "SELECT field1 AS hs_num, field2, ST_Transform(the_geom,4326) As
> the_geom FROM planet_osm_line"

I tried what u said as follows:
__
pgsql2shp -f ../data/myfile gis "SELECT *, ST_Transform(way,4326) As the_geom 
FROM planet_osm_line"

Output:

Preparing table for user query... Failed: ERROR:  AddToPROJ4SRSCache: Cannot 
find SRID (4326) in spatial_ref_sys

__

Then With reference to this 
http://postgis.refractions.net/pipermail/postgis-users/2008-February/018486.html
 web 
page I executed the following in the postgres database

__
gis=# SELECT UpdateGeometrySRID ('public', 'planet_osm_polygon','way', 4236);

Output:

ERROR:  constraint "enforce_srid_way" of relation "planet_osm_polygon" does not 
exist
CONTEXT:  SQL statement "ALTER TABLE public.planet_osm_polygon DROP constraint 
enforce_srid_way"
PL/pgSQL function "updategeometrysrid" line 53 at EXECUTE statement
SQL statement "SELECT UpdateGeometrySRID('', $1 , $2 , $3 , $4 )"
PL/pgSQL function "updategeometrysrid" line 4 at SQL statement
__

I don't know what to do now, please guide !

-- 
Regards,
Anisha Kaul

-
Hi-Tech Gears Limited, Gurgaon, India




___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp usage

[Paragon Corporation]

It sounds like your spatial_ref_sys table is empty.  Load it up with the
entries in spatial_ref_sys.sql

Hope that helps,
Regina 

-Original Message-
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Anisha
Kaul
Sent: Monday, January 11, 2010 2:23 AM
To: postgis-users@postgis.refractions.net
Subject: Re: [postgis-users] pgsql2shp usage

Hi,

Many thanks to all of you for bothering to reply !!

>From Chris Hermansen 
> Anisha, why are you converting to shape file?

I failed to load the map directly from the database !
So I thought it will be a boostup for me if I manage to load the map from a
shape file, and then I can go for the database reading !

I didn't have any problem in loading a world map from a shape file directly
downloaded from the internet, but I am facing problems when I convert the
postgres data in a shape file and then try to load it.

>From Regina
> Never worked with osm data, but since I created the .prj generator in 
> pgsql2shp, I can tell you it reads the srid of the geometry and looks 
> up the

> srtext in spatial ref and outputs that to the .prj file.

> I suspect osm data is in Mercator not long lat.  If you want your 
> output to be in long lat, you need to transform and use an sql 
> statement

> pgsql2shp -f "/path/to/filename" -h myserver -u apguser -P apgpassword 
> mygisdb
>   "SELECT field1 AS hs_num, field2, ST_Transform(the_geom,4326) As

> the_geom FROM planet_osm_line"

I tried what u said as follows:
__
pgsql2shp -f ../data/myfile gis "SELECT *, ST_Transform(way,4326) As
the_geom FROM planet_osm_line"

Output:

Preparing table for user query... Failed: ERROR:  AddToPROJ4SRSCache: Cannot
find SRID (4326) in spatial_ref_sys

__

Then With reference to this
http://postgis.refractions.net/pipermail/postgis-users/2008-February/018486.
html web page I executed the following in the postgres database

__
gis=# SELECT UpdateGeometrySRID ('public', 'planet_osm_polygon','way',
4236);

Output:

ERROR:  constraint "enforce_srid_way" of relation "planet_osm_polygon" does
not exist
CONTEXT:  SQL statement "ALTER TABLE public.planet_osm_polygon DROP
constraint enforce_srid_way"
PL/pgSQL function "updategeometrysrid" line 53 at EXECUTE statement SQL
statement "SELECT UpdateGeometrySRID('', $1 , $2 , $3 , $4 )"
PL/pgSQL function "updategeometrysrid" line 4 at SQL statement
__

I don't know what to do now, please guide !

--
Regards,
Anisha Kaul

-
Hi-Tech Gears Limited, Gurgaon, India




___
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] pgsql2shp usage

[Chris Hermansen]

Ok well it seems to me that your second step, namely pgsql2shp, is not 
necessary if you have no use for a shape file in the end.  So let's 
focus on your osm2pgsql command


In your first e-mail, you said:


I downloaded an OSM map file namely "india.osm.bz2".
I transported it in PostgreSQL database "gis" with the command :

./osm2pgsql -m -d gis india.osm.bz2 


The above command resulted in the creation of the following tables filled with data in 
database "gis"

gis=# \d
 List of relations
 Schema |Name|   Type   |  Owner
++--+--
 public | geometry_columns   | table| anisha
 public | planet_osm_line| table| anisha
 public | planet_osm_point   | table| anisha
 public | planet_osm_polygon | table| anisha
 public | planet_osm_roads   | table| anisha
 public | polygon_oid_seq| sequence | postgres
 public | spatial_ref_sys| table| anisha

So what is wrong with this?  Looks like your data is in the postgis database...



Anisha Kaul wrote:


Hi,

Many thanks to all of you for bothering to reply !!

From Chris Hermansen

> Anisha, why are you converting to shape file?

I failed to load the map directly from the database !

So I thought it will be a boostup for me if I manage to load the map 
from a shape file, and then I can go for the database reading !


I didn't have any problem in loading a world map from a shape file 
directly downloaded from the internet, but I am facing problems when I 
convert the postgres data in a shape file and then try to load it.


From Regina

> Never worked with osm data, but since I created the .prj generator in

> pgsql2shp, I can tell you it reads the srid of the geometry and looks up the
> srtext in spatial ref and outputs that to the .prj file.
> I suspect osm data is in Mercator not long lat.  If you want your output to
> be in long lat, you need to transform and use an sql statement
> pgsql2shp -f "/path/to/filename" -h myserver -u apguser -P apgpassword
> mygisdb 
> 	"SELECT field1 AS hs_num, field2, ST_Transform(the_geom,4326) As

> the_geom FROM planet_osm_line"
I tried what u said as follows:
__

pgsql2shp -f ../data/myfile gis "SELECT *, ST_Transform(way,4326) As 
the_geom FROM planet_osm_line"


Output:

Preparing table for user query... Failed: ERROR: AddToPROJ4SRSCache: 
Cannot find SRID (4326) in spatial_ref_sys


__

Then With reference to this 
http://postgis.refractions.net/pipermail/postgis-users/2008-February/018486.html 
web page I executed the following in the postgres database


__

gis=# SELECT UpdateGeometrySRID ('public', 'planet_osm_polygon','way', 
4236);


Output:

ERROR: constraint "enforce_srid_way" of relation "planet_osm_polygon" 
does not exist


CONTEXT: SQL statement "ALTER TABLE public.planet_osm_polygon DROP 
constraint enforce_srid_way"


PL/pgSQL function "updategeometrysrid" line 53 at EXECUTE statement

SQL statement "SELECT UpdateGeometrySRID('', $1 , $2 , $3 , $4 )"

PL/pgSQL function "updategeometrysrid" line 4 at SQL statement

__

I don't know what to do now, please guide !

--

Regards,

Anisha Kaul




Hi-Tech Gears Limited, Gurgaon, India





___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users



--
Regards,

Chris Hermansen · chris.herman...@timberline.ca · skype:clhermansen
tel+1.604.714.2878   ·  fax+1.604.733.0631   ·   mob+1.778.840.4625
Timberline Natural Resource Group Ltd   ·  http://www.timberline.ca
401  ·  958 West 8th Avenue  ·  Vancouver BC  ·  Canada  ·  V5Z 1E5

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp error

[smas036]

Update: The error is the same whether the column id is lower case or
uppercase.

Using single quotes 'id' produces a different error. Possibly an
improvement:

ERROR: Cannot determine geometry type (empty table).

The dump works fine when a table is specified and there is no query, however
there is a warning:

WARNING: Cannot determine spatial reference (empty table or unknown spatial
ref). No prj file will be generated.

Any help is appreciated

Sina
-- 
View this message in context: 
http://old.nabble.com/pgsql2shp-error-tp29384180p29396396.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp error

[Jeff Adams]

This doesn't explain or fix anything, but you could try using a temp table:

create table temp_for_export as select * from table1 where id < '20'

Then

pgsql2shp -f [path] -h [myhost] -p [port] -u [user] -P [password] [database]
temp_for_export

and see what that does.

Is there any chance your host, user or database is incorrect on the
pgsql2shp command line, so pgsql2shp is accessing a different "table1" than
you're looking at in the shell?


On Tue, Aug 10, 2010 at 6:44 AM, smas036 wrote:

>
> Update: The error is the same whether the column id is lower case or
> uppercase.
>
> Using single quotes 'id' produces a different error. Possibly an
> improvement:
>
> ERROR: Cannot determine geometry type (empty table).
>
> The dump works fine when a table is specified and there is no query,
> however
> there is a warning:
>
> WARNING: Cannot determine spatial reference (empty table or unknown spatial
> ref). No prj file will be generated.
>
> Any help is appreciated
>
> Sina
> --
> View this message in context:
> http://old.nabble.com/pgsql2shp-error-tp29384180p29396396.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
>
> ___
> 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] pgsql2shp error

[smas036]

Thanks for the replies, the most promising command so far is:

pgsql2shp -f [path] -h [myhost] -p [port] -u [user] -P [password] [database]
 "SELECT * FROM table1 WHERE 'ID' < '20'"

Which has the output:

Preparing table for user query... Done.
Initializing... WARNING: Cannot determine spatial reference (empty table or
unknown spatial ref). No prj file will be generated. 
ERROR: Cannot determine geometry type (empty table)

I think the query is correct as other combinations if single and double
quotes around field values produce
"...column does not exist..." or "The system cannot find the file
specified."

I believe the command parameters before the query are correct as the command
without a query:

pgsql2shp -f [path] -h [myhost] -p [port] -u [user] -P [password] [database]
[table]

works fine.

It would be possible to produce a table from a query and use this command
but I wonder if there is a performance loss in doing so...



-- 
View this message in context: 
http://old.nabble.com/pgsql2shp-error-tp29384180p29406665.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp error

[Jeff Adams]

Did you verify that the data in the shapefile is correct when you run the
command with just the table name?

Hang on a second...  WHERE 'ID' < '20' is never true.  That's why you're
getting an empty table.  'ID' is a string, as is '20'.  I think you mean to
say where your ID column value is less than 20, correct?  Single quotes mean
'this is a string'.

Are your ID values strings (varchars) or integers?  If integers, it'll be
faster to use 20 as an integer (without the single quotes) rather than as a
string.

Maybe the problem is with 'id' by itself, so try fully qualifying it:

pgsql2shp -f [path] -h [myhost] -p [port] -u [user] -P [password] [database]
 "SELECT * FROM table1 WHERE table1.id < 20"

On Wed, Aug 11, 2010 at 5:50 AM, smas036 wrote:

>
> Thanks for the replies, the most promising command so far is:
>
> pgsql2shp -f [path] -h [myhost] -p [port] -u [user] -P [password]
> [database]
>  "SELECT * FROM table1 WHERE 'ID' < '20'"
>
> Which has the output:
>
> Preparing table for user query... Done.
> Initializing... WARNING: Cannot determine spatial reference (empty table or
> unknown spatial ref). No prj file will be generated.
> ERROR: Cannot determine geometry type (empty table)
>
> I think the query is correct as other combinations if single and double
> quotes around field values produce
> "...column does not exist..." or "The system cannot find the file
> specified."
>
> I believe the command parameters before the query are correct as the
> command
> without a query:
>
> pgsql2shp -f [path] -h [myhost] -p [port] -u [user] -P [password]
> [database]
> [table]
>
> works fine.
>
> It would be possible to produce a table from a query and use this command
> but I wonder if there is a performance loss in doing so...
>
>
>
> --
> View this message in context:
> http://old.nabble.com/pgsql2shp-error-tp29384180p29406665.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>



-- 
Jeff Adams

Azavea (formerly Avencia)  |  340 N 12th St, Ste 402, Philadelphia, PA
jad...@azavea.com  | T 215.701.7717  | F 215.925.2663
Web azavea.com  |  Blog azavea.com/blogs  |  Twitter
@azavea
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp error

[smas036]

Hi Jeff, 

Thanks for your reply.
The data was correct when I just used the table name.
I have tried working with just the integer but I was still getting similar
errors.
I decided to just use a temp table, update that with a normal query and
output the temp table as the shapefile.

Thanks for your help!

Sina

Jeff Adams-9 wrote:
> 
> Did you verify that the data in the shapefile is correct when you run the
> command with just the table name?
> 
> Hang on a second...  WHERE 'ID' < '20' is never true.  That's why you're
> getting an empty table.  'ID' is a string, as is '20'.  I think you mean
> to
> say where your ID column value is less than 20, correct?  Single quotes
> mean
> 'this is a string'.
> 
> Are your ID values strings (varchars) or integers?  If integers, it'll be
> faster to use 20 as an integer (without the single quotes) rather than as
> a
> string.
> 
> Maybe the problem is with 'id' by itself, so try fully qualifying it:
> 
> pgsql2shp -f [path] -h [myhost] -p [port] -u [user] -P [password]
> [database]
>  "SELECT * FROM table1 WHERE table1.id < 20"
> 
> On Wed, Aug 11, 2010 at 5:50 AM, smas036
> wrote:
> 
>>
>> Thanks for the replies, the most promising command so far is:
>>
>> pgsql2shp -f [path] -h [myhost] -p [port] -u [user] -P [password]
>> [database]
>>  "SELECT * FROM table1 WHERE 'ID' < '20'"
>>
>> Which has the output:
>>
>> Preparing table for user query... Done.
>> Initializing... WARNING: Cannot determine spatial reference (empty table
>> or
>> unknown spatial ref). No prj file will be generated.
>> ERROR: Cannot determine geometry type (empty table)
>>
>> I think the query is correct as other combinations if single and double
>> quotes around field values produce
>> "...column does not exist..." or "The system cannot find the file
>> specified."
>>
>> I believe the command parameters before the query are correct as the
>> command
>> without a query:
>>
>> pgsql2shp -f [path] -h [myhost] -p [port] -u [user] -P [password]
>> [database]
>> [table]
>>
>> works fine.
>>
>> It would be possible to produce a table from a query and use this command
>> but I wonder if there is a performance loss in doing so...
>>
>>
>>
>> --
>> View this message in context:
>> http://old.nabble.com/pgsql2shp-error-tp29384180p29406665.html
>> Sent from the PostGIS - User mailing list archive at Nabble.com.
>>
>> ___
>> postgis-users mailing list
>> postgis-users@postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
> 
> 
> 
> -- 
> Jeff Adams
> 
> Azavea (formerly Avencia)  |  340 N 12th St, Ste 402, Philadelphia, PA
> jad...@azavea.com  | T 215.701.7717  | F 215.925.2663
> Web azavea.com  |  Blog azavea.com/blogs  |  Twitter
> @azavea
> 
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/pgsql2shp-error-tp29384180p29520124.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp fails

[Paragon Corporation]

Denis,
 
I don't have the latest svn version handy, though I think the version I have
has the latest pgsql2shp in it and its about a week or 2 old.
 
I tested with dumping out a spatial table from my 2.0 database and it worked
fine.  It could be an issue with parameters. (though the error you describe
is a bit bizarre since gid is not needed to dump out a spatial table)
 
Can you give us the following bits of information
 
1) pgsql2shp -- with no args -- what does the version number say 
 
Mine reads -- the below
RCSID: $Id: pgsql2shp.c 5450 2010-03-22 19:38:14Z pramsey $ RELEASE: 2.0
USE_GEOS=1 USE_PROJ=1 USE_STATS=1
 
2) What command line switches are you using - an example would be nice
I tested with this
pgsql2shp -f C:\cities -h localhost -p 5439 -u postgres postgis_test20
cities
 
and worked fine for me
 
3) Version of PostgreSQL you are running
4) Output of the below
 
SELECT postgis_scripts_released()
 
mine reads
2.0 r5793
 
5) Your operating system
 
Thanks,
Regina

  _  

From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Denis
Rykov
Sent: Thursday, August 26, 2010 10:51 PM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] pgsql2shp fails


Hi, just updated to 2.0svn and it seems that new pgsql2shp fails with new
db, but works with 1.5 ones, pgsql2shp from 1.5 works fine with both
keeps saying "column "gid" does not exist" even on fresh tables where SELECT
AddGeometryColumn was done

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp fails

[Denis Rykov]

Hello, Regina. There is information about my system:

1) RCSID: $Id: pgsql2shp-core.h 5646 2010-05-27 13:19:12Z pramsey $
RELEASE: 2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
2) pgsql2shp -f $dir_output/bounds/bnd-a-osm-buf.shp -U pgsql -d osm
public.results
3) PostgreSQL 8.4
4) 2.0 r5830
5) FreeBSD 8.1

I get the following error: "Error executing main scan query: ERROR:
column "gid" does not exist
LINE 1: ... -1), 'NDR') AS _geoX FROM "public"."results" ORDER BY "gid""
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp fails

[Mark Cave-Ayland]

Denis Rykov wrote:


Hello, Regina. There is information about my system:

1) RCSID: $Id: pgsql2shp-core.h 5646 2010-05-27 13:19:12Z pramsey $
RELEASE: 2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
2) pgsql2shp -f $dir_output/bounds/bnd-a-osm-buf.shp -U pgsql -d osm
public.results
3) PostgreSQL 8.4
4) 2.0 r5830
5) FreeBSD 8.1

I get the following error: "Error executing main scan query: ERROR:
column "gid" does not exist
LINE 1: ... -1), 'NDR') AS _geoX FROM "public"."results" ORDER BY "gid""


That's probably my bug (although the other apparent bug is that I forgot 
to set the svn id property on pgsql2shp-core.h too).


What does the output of "\d public.results" look like in psql?


ATB,

Mark.

--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp fails

[Denis Rykov]

Result of \d public.results in psql:

       Table "public.results"
 Column  |     Type      | Modifiers
--+---+---
the_geom | geometry      |
reg      | character(16) |

2010/8/27 Mark Cave-Ayland :
> Denis Rykov wrote:
>
>> Hello, Regina. There is information about my system:
>>
>> 1) RCSID: $Id: pgsql2shp-core.h 5646 2010-05-27 13:19:12Z pramsey $
>> RELEASE: 2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
>> 2) pgsql2shp -f $dir_output/bounds/bnd-a-osm-buf.shp -U pgsql -d osm
>> public.results
>> 3) PostgreSQL 8.4
>> 4) 2.0 r5830
>> 5) FreeBSD 8.1
>>
>> I get the following error: "Error executing main scan query: ERROR:
>> column "gid" does not exist
>> LINE 1: ... -1), 'NDR') AS _geoX FROM "public"."results" ORDER BY "gid""
>
> That's probably my bug (although the other apparent bug is that I forgot to
> set the svn id property on pgsql2shp-core.h too).
>
> What does the output of "\d public.results" look like in psql?
>
>
> ATB,
>
> Mark.
>
> --
> Mark Cave-Ayland - Senior Technical Architect
> PostgreSQL - PostGIS
> Sirius Corporation plc - control through freedom
> http://www.siriusit.co.uk
> t: +44 870 608 0063
>
> Sirius Labs: http://www.siriusit.co.uk/labs
> ___
> 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] pgsql2shp fails

[Mark Cave-Ayland]

Denis Rykov wrote:


Result of \d public.results in psql:

   Table "public.results"
 Column  | Type  | Modifiers
--+---+---
the_geom | geometry  |
reg  | character(16) |


Thanks for the bug report - should be fixed in latest SVN. Can you do an 
"svn up" and confirm that it now works for you?



Many thanks,

Mark.

--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp fails

[Denis Rykov]

Thanks! It works fine now.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp revision

[Mark Cave-Ayland]

Denis Rykov wrote:


Is the pgsl2shp show correct SVN version of revision?
After updated pgsql2shp from svn and start pgsql2shp without options I get:

RCSID: $Id: pgsql2shp-core.h 5870 2010-08-28 09:16:32Z mcayland $
RELEASE: 2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1


But last SVN revision number is not 5870.


This appears to be a "feature" of SVN in that the revision number 
embedded in the file is the last revision that altered that particular 
file, and not the latest revision of the whole repository. Since we 
store the revision information in a separate file (because it is re-used 
in more than one place) then you don't see the "true" repository revision.


Perhaps we could "touch" the pgsql2shp-core.h file but that just seems 
messy :(



ATB,

Mark.

--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp revision

[strk]

On Fri, Sep 10, 2010 at 02:31:52PM +0100, Mark Cave-Ayland wrote:

> This appears to be a "feature" of SVN in that the revision number 
> embedded in the file is the last revision that altered that particular 
> file, and not the latest revision of the whole repository. Since we 
> store the revision information in a separate file (because it is re-used 
> in more than one place) then you don't see the "true" repository revision.
> 
> Perhaps we could "touch" the pgsql2shp-core.h file but that just seems 
> messy :(

Why drop the feature ?
If we want a "true" repository revision we should provide
that in an header file (which we'd touch) and use that when
we see fit. I wouldn't drop the file-specific one though,
you can always get a diff from current to the specific one
w/out querying the server (right? or time to move to git :).

--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] pgsql2shp revision

[Mark Cave-Ayland]

strk wrote:

Perhaps we could "touch" the pgsql2shp-core.h file but that just seems 
messy :(


Why drop the feature ?
If we want a "true" repository revision we should provide
that in an header file (which we'd touch) and use that when
we see fit. I wouldn't drop the file-specific one though,
you can always get a diff from current to the specific one
w/out querying the server (right? or time to move to git :).


Yeah; out of all the options I'd prefer to keep the standard 
svn:keywords tag and touch the file when appropriate (maybe as part of 
the release process?). It's not a great solution, but probably the least 
worse out of all the alternatives.



ATB,

Mark.

--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp error

[p valdes]

have you do this?

createlang plpgsql mydatabase
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp error

[Christian Jauvin]

Yes:

createlang: language "plpgsql" is already installed in database

I thought that this was required for a PostGIS-enabled database anyway..?

It is a very straightforward database: two tables imported from
shapefiles with shp2pgsql:

# \dt
 List of relations
 Schema | Name | Type  |   Owner
+--+---+---
 public | dmti_qc_streets_2008 | table | christian
 public | geometry_columns | table | christian
 public | sdr_2001 | table | christian
 public | spatial_ref_sys  | table | christian




On Sat, Oct 22, 2011 at 12:58 PM, p valdes  wrote:
> have you do this?
>
> createlang plpgsql mydatabase
> ___
> 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] pgsql2shp error

[Adam Eskreis]

Are you sure that the projection you are using is in your spatial_ref_sys
table?

On Sat, Oct 22, 2011 at 1:46 PM, Christian Jauvin  wrote:

> Yes:
>
> createlang: language "plpgsql" is already installed in database
>
> I thought that this was required for a PostGIS-enabled database anyway..?
>
> It is a very straightforward database: two tables imported from
> shapefiles with shp2pgsql:
>
> # \dt
> List of relations
>  Schema | Name | Type  |   Owner
> +--+---+---
>  public | dmti_qc_streets_2008 | table | christian
>  public | geometry_columns | table | christian
>  public | sdr_2001 | table | christian
>  public | spatial_ref_sys  | table | christian
>
>
>
>
> On Sat, Oct 22, 2011 at 12:58 PM, p valdes  wrote:
> > have you do this?
> >
> > createlang plpgsql mydatabase
> > ___
> > 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] pgsql2shp error

[Christian Jauvin]

> Are you sure that the projection you are using is in your spatial_ref_sys
> table?

Thanks, that solved it! I hadn't specified any projection with
shp2pgsql, so I updated my geometry_columns with:

select updategeometrysrid(, , 4326);

and it now works. The funny thing though is that there is still an
error/warning message
at the start:

Preparing table for user query... Done.
Initializing... Geometry type unknown (have you enabled postgis?)
Done (postgis major version: 1).
Output shape: PolyLine
Dumping: 

[8603 rows].

..but it doesn't seem to affect the results. Thanks again!
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] pgsql2shp encoding problem

[Ben Madin]

G'day all,

I'm having a problem exporting data from a utf8 database to shape files for a 
client. They are receiving the data and viewing it only to find that characters 
with diacritics are all messed up.

My understanding was that .dbf was originally ascii only (and the column 
headings still are) but now the .dbf file can contain data in any encoding. 

So with the suggestion of nicolas ribot, I am exporting using :

export PGCLIENTENCODING=LATIN9; pgsql2shp -f  etc etc

I also read in a number of sites that this can be specified by adding a file 
with a .cpg suffix, and including the name of the encoding for the .dbf in it, 
i.e. LATIN9

Presumably, one would end up with :

data.cpg
data.dbf
data.prj
data.qix
data.shp
data.shx

Can anyone advise me if this is true and reasonable? Or more importantly, will 
work with ArcGIS and / or MapServer?

cheers

Ben

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] pgsql2shp cuts varchar fields

[Gustavo Ces]

Hi all,

maybe this is an old issue... I´m trying to export to shapefile some 
geometries, but in the result shapefile varchar fields greater than 255 are 
cutted at this point. I´ve got alternatives but i´d like to know : is there any 
method to export those fields directly with pgsql2shp or is it a 
shapefile-format or pgsql2shp limit?
Thanks in advance,


Gus___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp : Encoding headache

[InterRob]

Does that last query (invoking the upper() function) actually run well when
executed in pgsql console?

Rob

2009/10/16 Arnaud Lesauvage 

> Hi all!
>
> I have an UTF8 encoded shapefile, and an UTF8 encoded postgis-enabled
> database. I want my shapefile to be encoded in WIN1252, and a particular
> field to be in uppercase.
>
> Since I am on windows, I don't have an iconv executable. Therefore, I am
> trying to :
> - dump the shapefile with shp2pgsql to an sql text file
> - load the resulting sql file into a postgresql table
> - dump this table into a shapefile with pgsql2shp (in WIN1252)
>
> To load the shapefile into postgresql, I had to dump it without specifying
> a '-W' argument, set my client_encoding to UTF8, then load the sql file into
> postgresql.
>
> If I look at the data with pgAdmin (with client_encoding set to UTF8), it
> looks good : accentuation is fine, special characters are fine.
>
> To dump the data in a WIN1252-encoded shapefile, pgsql2shp has no encoding
> argument, so I set my client encoding to WIN1252 thtough the environment
> variable PGCLIENTENCODING.
>
> If I just dump the file this way, it seems to be fine. So this command
> works fine :
> pgsql2shp -f myouput.shp -u postgres -g mygeom mydatabase "SELECT mytext,
> mygeom FROM mytemptable"
> ->  [621679 rows]
>
> But then, if I dump it through a query to have my field in uppercase, I get
> an error 'character 0xc29f of encoding UTF8 has no equivalent in WIN1252'
> (translated by myself, the message is in French)
> The command is simply :
> pgsql2shp -f myouput.shp -u postgres -g mygeom mydatabase "SELECT
> upper(mytext) as mytext, mygeom FROM mytemptable"
>
> So I guess there is a problem with my 'upper' conversion, but I have no
> idea what this 0xc29f character could be.
>
> Any help would be greatly appreciated.
> Thanks a lot !
>
> --
> Arnaud Lesauvage
> ___
> 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] pgsql2shp : Encoding headache

[Arnaud Lesauvage]

InterRob a écrit :

Does that last query (invoking the upper() function) actually run well when
executed in pgsql console?


Hi Rob.
No, if I issue a "SET client_encoding TO win1252;" before running 
"SELECT upper(myfield) FROM mytable", I get the same error.


Arnaud
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp: date field

[Paul Ramsey]

I've placed a ticket on this with a patch

http://trac.osgeo.org/postgis/ticket/321

If you would try and patch and see if it creates files that are more
broadly usable, that would be nice.

Thanks,

Paul

On Fri, Nov 27, 2009 at 5:28 AM, Peter Hopfgartner
 wrote:
> Hi,
>
> we currently have some interoperability problems with pgsql2shp generated
> shapes. Basically, if a date field is NULL, pgsql2shp writes all zeros to
> the file. This file can not be opened with gvSIG and OpenOffice, whereas it
> can be opened with ArcGIS and MS-Excel 2003 (showing the zeros).
>
> If I overwrite these zeros (from the hex editor) with spaces, all of the
> above programs do open and interpret the NULL value correctly.
>
> I've attached a dump of the database and the generated shape file and the
> version with the zeros replaced with spaces.
>
> I have not found any spec how dBase III should represent NULL data values
> and forward this problem to the ML, but the files written in ArcGIS use
> spaces, too.
>
> Another issue: pgsql2shp writes the integer field "ID" aligned to the left,
> when written from ArcGIS it is aligned to the right.  Indeed, some tools
> seem to have problems in interpreting the value written.
>
> Regards,
>
> Peter
>
> --
>
> Dott. Peter Hopfgartner
>
> R3 GIS Srl - GmbH
> Via Johann Kravogl-Str. 2
> I-39012 Meran/Merano (BZ)
> Email: peter.hopfgart...@r3-gis.com
> Tel. : +39 0473 494949
> Fax  : +39 0473 069902
> www  : http://www.r3-gis.com
>
> 
> Venite a trovarci all'ASITA, dal 1 al 4 dicembre a Bari www.asita.it
> Besuchen Sie uns bei der Messe ASITA vom 1. bis 4. Dezember in Bari
> www.asita.it
> Visit us at ASITA, from 1st to 4th December in Bari www.asita.it
> 
>
>
> ___
> 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] pgsql2shp: date field

[Peter Hopfgartner]

gvSIG opens the generated shape file without any complaint and Excel 
behaves as expected.


Thanks!

Peter

(tested with PostGIS 1.4/PostgreSQL 8.2/CentOS 5.4)



Paul Ramsey wrote:

I've placed a ticket on this with a patch

http://trac.osgeo.org/postgis/ticket/321

If you would try and patch and see if it creates files that are more
broadly usable, that would be nice.

Thanks,

Paul

On Fri, Nov 27, 2009 at 5:28 AM, Peter Hopfgartner
 wrote:
  

Hi,

we currently have some interoperability problems with pgsql2shp generated
shapes. Basically, if a date field is NULL, pgsql2shp writes all zeros to
the file. This file can not be opened with gvSIG and OpenOffice, whereas it
can be opened with ArcGIS and MS-Excel 2003 (showing the zeros).

If I overwrite these zeros (from the hex editor) with spaces, all of the
above programs do open and interpret the NULL value correctly.

I've attached a dump of the database and the generated shape file and the
version with the zeros replaced with spaces.

I have not found any spec how dBase III should represent NULL data values
and forward this problem to the ML, but the files written in ArcGIS use
spaces, too.

Another issue: pgsql2shp writes the integer field "ID" aligned to the left,
when written from ArcGIS it is aligned to the right.  Indeed, some tools
seem to have problems in interpreting the value written.

Regards,

Peter

--

Dott. Peter Hopfgartner

R3 GIS Srl - GmbH
Via Johann Kravogl-Str. 2
I-39012 Meran/Merano (BZ)
Email: peter.hopfgart...@r3-gis.com
Tel. : +39 0473 494949
Fax  : +39 0473 069902
www  : http://www.r3-gis.com


Venite a trovarci all'ASITA, dal 1 al 4 dicembre a Bari www.asita.it
Besuchen Sie uns bei der Messe ASITA vom 1. bis 4. Dezember in Bari
www.asita.it
Visit us at ASITA, from 1st to 4th December in Bari www.asita.it



___
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

  



--

Dott. Peter Hopfgartner

R3 GIS Srl - GmbH
Via Johann Kravogl-Str. 2
I-39012 Meran/Merano (BZ)
Email: peter.hopfgart...@r3-gis.com
Tel. : +39 0473 494949
Fax  : +39 0473 069902
www  : http://www.r3-gis.com


Venite a trovarci all'ASITA, dal 1 al 4 dicembre a Bari www.asita.it
Besuchen Sie uns bei der Messe ASITA vom 1. bis 4. Dezember in Bari www.asita.it
Visit us at ASITA, from 1st to 4th December in Bari www.asita.it


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp: date field

[Peter Hopfgartner]

I've tried to fix the problem with the left padding, too.

The resulting shape files open cleanly in ArcGIS and gvSIG. Also 
inspection with a tool from Borland, ("Database Desktop") has no 
complaints about those files.


This patch inclued your fix about the rapprsentation of tha NULL date.

Regards,

Peter

Paul Ramsey wrote:

I've placed a ticket on this with a patch

http://trac.osgeo.org/postgis/ticket/321

If you would try and patch and see if it creates files that are more
broadly usable, that would be nice.

Thanks,

Paul

On Fri, Nov 27, 2009 at 5:28 AM, Peter Hopfgartner
 wrote:
  

Hi,

we currently have some interoperability problems with pgsql2shp generated
shapes. Basically, if a date field is NULL, pgsql2shp writes all zeros to
the file. This file can not be opened with gvSIG and OpenOffice, whereas it
can be opened with ArcGIS and MS-Excel 2003 (showing the zeros).

If I overwrite these zeros (from the hex editor) with spaces, all of the
above programs do open and interpret the NULL value correctly.

I've attached a dump of the database and the generated shape file and the
version with the zeros replaced with spaces.

I have not found any spec how dBase III should represent NULL data values
and forward this problem to the ML, but the files written in ArcGIS use
spaces, too.

Another issue: pgsql2shp writes the integer field "ID" aligned to the left,
when written from ArcGIS it is aligned to the right.  Indeed, some tools
seem to have problems in interpreting the value written.

Regards,

Peter

--

Dott. Peter Hopfgartner

R3 GIS Srl - GmbH
Via Johann Kravogl-Str. 2
I-39012 Meran/Merano (BZ)
Email: peter.hopfgart...@r3-gis.com
Tel. : +39 0473 494949
Fax  : +39 0473 069902
www  : http://www.r3-gis.com


Venite a trovarci all'ASITA, dal 1 al 4 dicembre a Bari www.asita.it
Besuchen Sie uns bei der Messe ASITA vom 1. bis 4. Dezember in Bari
www.asita.it
Visit us at ASITA, from 1st to 4th December in Bari www.asita.it



___
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

  



--

Dott. Peter Hopfgartner

R3 GIS Srl - GmbH
Via Johann Kravogl-Str. 2
I-39012 Meran/Merano (BZ)
Email: peter.hopfgart...@r3-gis.com
Tel. : +39 0473 494949
Fax  : +39 0473 069902
www  : http://www.r3-gis.com


Venite a trovarci all'ASITA, dal 1 al 4 dicembre a Bari www.asita.it
Besuchen Sie uns bei der Messe ASITA vom 1. bis 4. Dezember in Bari www.asita.it
Visit us at ASITA, from 1st to 4th December in Bari www.asita.it


--- pgsql2shp.c.orig	2009-05-03 05:16:42.0 +0200
+++ pgsql2shp.c	2009-11-26 18:03:40.0 +0100
@@ -64,6 +64,7 @@
 char temptablename[256];
 char *geo_col_name, *table, *shp_file, *schema, *usrquery;
 int type_ary[256];
+int type_ary_len[256];
 char *main_scan_query;
 DBFHandle dbf;
 SHPHandle shp;
@@ -110,7 +111,7 @@
  * Might return untouched input or pointer to static private
  * buffer: use return value right away.
  */
-static const char * goodDBFValue(const char *in, char fieldType);
+static const char * goodDBFValue(const char *in, char fieldType, int filedLen);
 
 /** @brief Binary to hexewkb conversion function */
 char *convert_bytes_to_hex(uchar *ewkb, size_t size);
@@ -852,7 +853,7 @@
 			else
 			{
 val = PQgetvalue(res, residx, j);
-val = goodDBFValue(val, type_ary[j]);
+val = goodDBFValue(val, type_ary[j], type_ary_len[j]);
 			}
 
 			LWDEBUG(3, "s");
@@ -1514,6 +1515,7 @@
 return 0;
 			}
 			type_ary[mainscan_nflds]=FTInteger;
+			type_ary_len[mainscan_nflds]=6;
 			mainscan_flds[mainscan_nflds++] = fname;
 			continue;
 		}
@@ -1529,11 +1531,12 @@
 			if ( DBFAddField(dbf, field_name, FTInteger,
 11, 0) == -1 )
 			{
-printf( "Error - Ingeter field could not "
+printf( "Error - Integer field could not "
 	"be created.\n");
 return 0;
 			}
 			type_ary[mainscan_nflds]=FTInteger;
+			type_ary_len[mainscan_nflds]=11;
 			mainscan_flds[mainscan_nflds++] = fname;
 			continue;
 		}
@@ -1554,6 +1557,7 @@
 return 0;
 			}
 			type_ary[mainscan_nflds]=FTInteger;
+			type_ary_len[mainscan_nflds]=20;
 			mainscan_flds[mainscan_nflds++] = fname;
 			continue;
 		}
@@ -1576,6 +1580,7 @@
 return 0;
 			}
 			type_ary[mainscan_nflds]=FTDouble;
+			type_ary_len[mainscan_nflds]=32;
 			mainscan_flds[mainscan_nflds++] = fname;
 			continue;
 		}
@@ -1593,6 +1598,7 @@
 return 0;
 			}
 			type_ary[mainscan_nflds]=FTLogical

[postgis-users] pgsql2shp dbf file encoding

[Denis Rykov]

Try to export postgis data to shapefiles with pgsql2shp (pgsql2shp-core.h
5870 2010-08-28 09:16:32Z mcayland)
If open *.dbf file I see the value in my dbf files at byte 29 is 0x57h. Is
the 0x57h value is default? Why not 0x00h?
With 0x57h encoding my shapefiles looks not correct in any GIS software.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] pgsql2shp - No GID variable

[Andreas Forø Tollefsen]

Hi all,

When using the pgsql2shp the gid column is not included.
I even tried typing a "select gid AS g_id" to include it as a different
column name, but this did not work.

My syntax: pgsql2shp -f pgfinal2008.shp -h localhost -u user -P pass postgis
public.pgfinal2008 "SELECT *, gid as g_id FROM pgfinal2008

Any ideas how i manage to extract the gid column from my tables?

Andreas
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] pgsql2shp and prj support

[Dane Springmeyer]

It appears that the pgsql2shp tool does not write out .prj files  
alongside shp output?


After I dump out shapefiles I like to view them in Qgis or Udig and  
both like/require .prj files for projection of shapefiles. I know  
that you can assign them inside inside either of these apps but it  
would be nice to have them created automatically.


Since I am using php to batch dump a number of shapefiles, and I've  
been able to nest these two SQL statements within the script to be  
able to get the "SRTEXT" and write it to a .prj file that corresponds  
to each shapefile:


SELECT ST_SRID(the_geom) from MyTable limit 1;

SELECT srtext from spatial_ref_sys WHERE srid = 2964 // Alaska Albers...

It seems like this would be a useful thing to have as part of the  
pgsql2shp utility. Does it already do this and I a missing something,  
or is this a feature request?


I noted this discussion (http://postgis.refractions.net/pipermail/ 
postgis-users/2004-October/005958.html) from '04 that notes the  
ability to use the srtext entry in the spatial_ref_sys. Has this been  
discussed since?


Cheers,

Dane

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] pgsql2shp syntax - sentence construction

[SenTnel]

Hi! Im working with postgres and geoserver but im just not good at sql
statements, none the less in construction sentences in postgres, and I would
like to create a shapefile from a table in postgres and don't want to make a
costly mistake, so if you help me achieve this using pgsql2shp: 

database name: mydatabase
schema: myschema
table: mytable

shapefile to be created: updatedshp

database user: postgres
password: any

can you help me with the syntax to achieve the shapefile containing all of
the data (and geom) in such table?

Thanks !
-- 
View this message in context: 
http://www.nabble.com/pgsql2shp-syntax---sentence-construction-tp17613265p17613265.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp encoding problem

[Sandro Santilli]

On Thu, Jul 05, 2012 at 01:39:16PM +0800, Ben Madin wrote:
> G'day all,
> 
> I'm having a problem exporting data from a utf8 database to shape files for a 
> client. They are receiving the data and viewing it only to find that 
> characters with diacritics are all messed up.

Does their system support utf8 ?

> My understanding was that .dbf was originally ascii only (and the column 
> headings still are) but now the .dbf file can contain data in any encoding. 
> 
> So with the suggestion of nicolas ribot, I am exporting using :
> 
> export PGCLIENTENCODING=LATIN9; pgsql2shp -f  etc etc
> 
> I also read in a number of sites that this can be specified by adding a file 
> with a .cpg suffix, and including the name of the encoding for the .dbf in 
> it, i.e. LATIN9
> 
> Presumably, one would end up with :
> 
> data.cpg
> data.dbf
> data.prj
> data.qix
> data.shp
> data.shx
> 
> Can anyone advise me if this is true and reasonable? Or more importantly, 
> will work with ArcGIS and / or MapServer?

You tell us what works with what by testing it.
My impression is that the standard should be UTF8 for everything, specifically
to avoid the localization issue once and for all.

--strk; 

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.1
  |  / 2.1 |http://strk.keybit.net - http://vizzuality.com
  `-o--'

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp encoding problem

[Ben Madin]

Thanks strk,

On 05/07/2012, at 5:07 PM, Sandro Santilli wrote:

> On Thu, Jul 05, 2012 at 01:39:16PM +0800, Ben Madin wrote:
>> G'day all,
>> 
>> I'm having a problem exporting data from a utf8 database to shape files for 
>> a client. They are receiving the data and viewing it only to find that 
>> characters with diacritics are all messed up.
> 
> Does their system support utf8 ?

I don't know (but I assume so - Windows Server 2008 R2 SP1 64 bit) 


>> Can anyone advise me if this is true and reasonable? Or more importantly, 
>> will work with ArcGIS and / or MapServer?
> 
> You tell us what works with what by testing it.

I don't have (nor do I have much inclination to invest in…) the above system (I 
don't really have any Windows background, and I'm not involved in the software, 
only supply the spatial data in shapefile format - not my choice. They are 
using MapServer 5.2.1 (on IIS 7.5, which has only caused me grief in the past), 
hence my desire to see if anyone else had experience before I go backwards down 
that line.

> My impression is that the standard should be UTF8 for everything, 
> specifically to avoid the localisation issue once and for all.

'should be' … it's a world away from 'is' - there doesn't even seem to be a 
standard for referring to the standards! i.e. LATIN1 ≈ ISO-8859-1 ≈ 28591≈ 
ISO88591 

and UTF8 ≈ utf-8

cheers

Ben


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

RE: [postgis-users] pgsql2shp cuts varchar fields

[Obe, Regina]

Not absolutely sure, but I think its a limit in the spec.  I think fields > 254 
are considered to be memo fields in the dbf world and so would be put in a .dbt.
Its been a while since I've done any DBase or Foxpro programming so my memory 
could be wrong.
 
I've never actually seen .dbt files included with ESRI shape files, so I assume 
ESRI discarded that side of the spec.  If its allowed, then we probably should 
implement it.
 
Hope that helps,
Regina
 
 



From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Gustavo Ces
Sent: Thursday, December 18, 2008 10:53 AM
To: PostGIS Users Discussion
Subject: [postgis-users] pgsql2shp cuts varchar fields


Hi all,
 
maybe this is an old issue... I´m trying to export to shapefile some 
geometries, but in the result shapefile varchar fields greater than 255 are 
cutted at this point. I´ve got alternatives but i´d like to know : is there any 
method to export those fields directly with pgsql2shp or is it a 
shapefile-format or pgsql2shp limit?
Thanks in advance,
 
 
Gus


-
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp cuts varchar fields

[Gustavo Ces]

Hi Regina,

thanks for answering. You must be right. I´ve tried to export with Gvsig to 
shapefile and cuts the varchar fields... I´ve tried to join with arcgis and 
fields don´t be cutted but when i export joined data to a shapefile ( i need 
this to use Arcgis Mapbook and use those fields as tags), then i´ve got cutted 
fields... So it seems a shape/dbf problem... And i´ve got no option now! :(
 Maybe making two fields from all original fields it´s the answer ( i´ve 
got no text larger than 254*2). Any other suggestion will be welcomed...


Gus
  - Original Message - 
  From: Obe, Regina 
  To: PostGIS Users Discussion 
  Sent: Thursday, December 18, 2008 5:08 PM
  Subject: RE: [postgis-users] pgsql2shp cuts varchar fields


  Not absolutely sure, but I think its a limit in the spec.  I think fields > 
254 are considered to be memo fields in the dbf world and so would be put in a 
.dbt.
  Its been a while since I've done any DBase or Foxpro programming so my memory 
could be wrong.

  I've never actually seen .dbt files included with ESRI shape files, so I 
assume ESRI discarded that side of the spec.  If its allowed, then we probably 
should implement it.

  Hope that helps,
  Regina





--
  From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Gustavo Ces
  Sent: Thursday, December 18, 2008 10:53 AM
  To: PostGIS Users Discussion
  Subject: [postgis-users] pgsql2shp cuts varchar fields


  Hi all,

  maybe this is an old issue... I´m trying to export to shapefile some 
geometries, but in the result shapefile varchar fields greater than 255 are 
cutted at this point. I´ve got alternatives but i´d like to know : is there any 
method to export those fields directly with pgsql2shp or is it a 
shapefile-format or pgsql2shp limit?
  Thanks in advance,


  Gus


--


  The substance of this message, including any attachments, may be 
confidential, legally privileged and/or exempt from disclosure pursuant to 
Massachusetts law. It is intended solely for the addressee. If you received 
this in error, please contact the sender and delete the material from any 
computer. 



--


  Help make the earth a greener place. If at all possible resist printing this 
email and join us in saving paper. 




--


  ___
  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] pgsql2shp cuts varchar fields

[David Fawcett]

Other things that you may notice when exporting to shape is that field
names may be truncated, and you are limited to ~256 columns in your
table as well.  This goes back to the .dbf spec.  Some (column limit)
of the these limitations also show up when you export to the ESRI
Personal Geodatabase as well.

David.

On Thu, Dec 18, 2008 at 10:37 AM, Gustavo Ces  wrote:
> Hi Regina,
>
> thanks for answering. You must be right. I´ve tried to export with Gvsig
> to shapefile and cuts the varchar fields... I´ve tried to join with arcgis
> and fields don´t be cutted but when i export joined data to a shapefile ( i
> need this to use Arcgis Mapbook and use those fields as tags), then i´ve got
> cutted fields... So it seems a shape/dbf problem... And i´ve got no option
> now! :(
>  Maybe making two fields from all original fields it´s the answer ( i´ve
> got no text larger than 254*2). Any other suggestion will be welcomed...
>
>
> Gus
>
> - Original Message -
> From: Obe, Regina
> To: PostGIS Users Discussion
> Sent: Thursday, December 18, 2008 5:08 PM
> Subject: RE: [postgis-users] pgsql2shp cuts varchar fields
> Not absolutely sure, but I think its a limit in the spec.  I think fields >
> 254 are considered to be memo fields in the dbf world and so would be put in
> a .dbt.
> Its been a while since I've done any DBase or Foxpro programming so my
> memory could be wrong.
>
> I've never actually seen .dbt files included with ESRI shape files, so I
> assume ESRI discarded that side of the spec.  If its allowed, then we
> probably should implement it.
>
> Hope that helps,
> Regina
>
>
> 
> From: postgis-users-boun...@postgis.refractions.net
> [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Gustavo
> Ces
> Sent: Thursday, December 18, 2008 10:53 AM
> To: PostGIS Users Discussion
> Subject: [postgis-users] pgsql2shp cuts varchar fields
>
> Hi all,
>
> maybe this is an old issue... I´m trying to export to shapefile some
> geometries, but in the result shapefile varchar fields greater than 255 are
> cutted at this point. I´ve got alternatives but i´d like to know : is there
> any method to export those fields directly with pgsql2shp or is it a
> shapefile-format or pgsql2shp limit?
> Thanks in advance,
>
>
> Gus
>
> 
>
> The substance of this message, including any attachments, may be
> confidential, legally privileged and/or exempt from disclosure pursuant to
> Massachusetts law. It is intended solely for the addressee. If you received
> this in error, please contact the sender and delete the material from any
> computer.
>
> 
>
> Help make the earth a greener place. If at all possible resist printing this
> email and join us in saving paper.
>
> 
>
> ___
> 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] pgsql2shp cuts varchar fields

[Obe, Regina]

I suppose Map Info tab format or some other format is not an option for 
MapBook. 

Well I'm not sure about the memo field part, but I know around here MapInfo 
users get
frustrated when they export from MapInfo to ESRI Shape because of the .dbf 10 
character field name limitation.


-Original Message-
From: postgis-users-boun...@postgis.refractions.net on behalf of David Fawcett
Sent: Thu 12/18/2008 12:32 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] pgsql2shp cuts varchar fields
 
Other things that you may notice when exporting to shape is that field
names may be truncated, and you are limited to ~256 columns in your
table as well.  This goes back to the .dbf spec.  Some (column limit)
of the these limitations also show up when you export to the ESRI
Personal Geodatabase as well.

David.

On Thu, Dec 18, 2008 at 10:37 AM, Gustavo Ces  wrote:
> Hi Regina,
>
> thanks for answering. You must be right. I´ve tried to export with Gvsig
> to shapefile and cuts the varchar fields... I´ve tried to join with arcgis
> and fields don´t be cutted but when i export joined data to a shapefile ( i
> need this to use Arcgis Mapbook and use those fields as tags), then i´ve got
> cutted fields... So it seems a shape/dbf problem... And i´ve got no option
> now! :(
>  Maybe making two fields from all original fields it´s the answer ( i´ve
> got no text larger than 254*2). Any other suggestion will be welcomed...
>
>
> Gus
>
> - Original Message -
> From: Obe, Regina
> To: PostGIS Users Discussion
> Sent: Thursday, December 18, 2008 5:08 PM
> Subject: RE: [postgis-users] pgsql2shp cuts varchar fields
> Not absolutely sure, but I think its a limit in the spec.  I think fields >
> 254 are considered to be memo fields in the dbf world and so would be put in
> a .dbt.
> Its been a while since I've done any DBase or Foxpro programming so my
> memory could be wrong.
>
> I've never actually seen .dbt files included with ESRI shape files, so I
> assume ESRI discarded that side of the spec.  If its allowed, then we
> probably should implement it.
>
> Hope that helps,
> Regina
>
>
> 
> From: postgis-users-boun...@postgis.refractions.net
> [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Gustavo
> Ces
> Sent: Thursday, December 18, 2008 10:53 AM
> To: PostGIS Users Discussion
> Subject: [postgis-users] pgsql2shp cuts varchar fields
>
> Hi all,
>
> maybe this is an old issue... I´m trying to export to shapefile some
> geometries, but in the result shapefile varchar fields greater than 255 are
> cutted at this point. I´ve got alternatives but i´d like to know : is there
> any method to export those fields directly with pgsql2shp or is it a
> shapefile-format or pgsql2shp limit?
> Thanks in advance,
>
>
> Gus
>
> 
>
> The substance of this message, including any attachments, may be
> confidential, legally privileged and/or exempt from disclosure pursuant to
> Massachusetts law. It is intended solely for the addressee. If you received
> this in error, please contact the sender and delete the material from any
> computer.
>
> 
>
> Help make the earth a greener place. If at all possible resist printing this
> email and join us in saving paper.
>
> 
>
> ___
> 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




-
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

RE: [postgis-users] pgsql2shp cuts varchar fields

[Sufficool, Stanley]

> -Original Message-
> From: postgis-users-boun...@postgis.refractions.net 
> [mailto:postgis-users-boun...@postgis.refractions.net] On 
> Behalf Of David Fawcett
> Sent: Thursday, December 18, 2008 9:33 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] pgsql2shp cuts varchar fields
> 
> 
> Other things that you may notice when exporting to shape is 
> that field names may be truncated, and you are limited to 
> ~256 columns in your table as well.  This goes back to the 
> .dbf spec.  Some (column limit) of the these limitations also 
> show up when you export to the ESRI Personal Geodatabase as well.
> 
> David.
> 
> On Thu, Dec 18, 2008 at 10:37 AM, Gustavo Ces  wrote:
> > Hi Regina,
> >
> > thanks for answering. You must be right. I´ve tried to 
> export with 
> > Gvsig to shapefile and cuts the varchar fields... I´ve 
> tried to join 
> > with arcgis and fields don´t be cutted but when i export 
> joined data 
> > to a shapefile ( i need this to use Arcgis Mapbook and use those 
> > fields as tags), then i´ve got cutted fields... So it seems a 
> > shape/dbf problem... And i´ve got no option now! :(
> >  Maybe making two fields from all original fields it´s 
> the answer 
> > ( i´ve got no text larger than 254*2). Any other suggestion will be 
> > welcomed...
> >

Foxpro and Clipper allow for text field sizes up to 64K using the field size + 
(decimal count * 256).

Would it be a problem to include this support in the shape file importer? Does 
ogr2ogr support this?


> >
> > Gus
> >
> > - Original Message -
> > From: Obe, Regina
> > To: PostGIS Users Discussion
> > Sent: Thursday, December 18, 2008 5:08 PM
> > Subject: RE: [postgis-users] pgsql2shp cuts varchar fields Not 
> > absolutely sure, but I think its a limit in the spec.  I 
> think fields 
> > > 254 are considered to be memo fields in the dbf world and 
> so would 
> > be put in a .dbt. Its been a while since I've done any 
> DBase or Foxpro 
> > programming so my memory could be wrong.
> >
> > I've never actually seen .dbt files included with ESRI 
> shape files, so 
> > I assume ESRI discarded that side of the spec.  If its 
> allowed, then 
> > we probably should implement it.
> >
> > Hope that helps,
> > Regina
> >
> >
> > 
> > From: postgis-users-boun...@postgis.refractions.net
> > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of 
> > Gustavo Ces
> > Sent: Thursday, December 18, 2008 10:53 AM
> > To: PostGIS Users Discussion
> > Subject: [postgis-users] pgsql2shp cuts varchar fields
> >
> > Hi all,
> >
> > maybe this is an old issue... I´m trying to export to shapefile 
> > some geometries, but in the result shapefile varchar fields greater 
> > than 255 are cutted at this point. I´ve got alternatives 
> but i´d like 
> > to know : is there any method to export those fields directly with 
> > pgsql2shp or is it a shapefile-format or pgsql2shp limit?
> > Thanks in advance,
> >
> >
> > Gus
> >
> > 
> >
> > The substance of this message, including any attachments, may be 
> > confidential, legally privileged and/or exempt from disclosure 
> > pursuant to Massachusetts law. It is intended solely for the 
> > addressee. If you received this in error, please contact the sender 
> > and delete the material from any computer.
> >
> > 
> >
> > Help make the earth a greener place. If at all possible resist 
> > printing this email and join us in saving paper.
> >
> > 
> >
> > ___
> > 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] pgsql2shp cuts varchar fields

[Gustavo Ces]

RE: [postgis-users] pgsql2shp cuts varchar fieldsHi,

finally i´ve have done two varchar fields from original one and using both 
fields in mapbook and the result is not so strange ... thanks for your help and 
info!

Gus
  - Original Message - 
  From: Obe, Regina 
  To: PostGIS Users Discussion 
  Sent: Thursday, December 18, 2008 6:43 PM
  Subject: RE: [postgis-users] pgsql2shp cuts varchar fields


  I suppose Map Info tab format or some other format is not an option for 
MapBook.

  Well I'm not sure about the memo field part, but I know around here MapInfo 
users get
  frustrated when they export from MapInfo to ESRI Shape because of the .dbf 10 
character field name limitation.


  -Original Message-
  From: postgis-users-boun...@postgis.refractions.net on behalf of David Fawcett
  Sent: Thu 12/18/2008 12:32 PM
  To: PostGIS Users Discussion
  Subject: Re: [postgis-users] pgsql2shp cuts varchar fields

  Other things that you may notice when exporting to shape is that field
  names may be truncated, and you are limited to ~256 columns in your
  table as well.  This goes back to the .dbf spec.  Some (column limit)
  of the these limitations also show up when you export to the ESRI
  Personal Geodatabase as well.

  David.

  On Thu, Dec 18, 2008 at 10:37 AM, Gustavo Ces  wrote:
  > Hi Regina,
  >
  > thanks for answering. You must be right. I´ve tried to export with Gvsig
  > to shapefile and cuts the varchar fields... I´ve tried to join with arcgis
  > and fields don´t be cutted but when i export joined data to a shapefile ( i
  > need this to use Arcgis Mapbook and use those fields as tags), then i´ve got
  > cutted fields... So it seems a shape/dbf problem... And i´ve got no option
  > now! :(
  >  Maybe making two fields from all original fields it´s the answer ( i´ve
  > got no text larger than 254*2). Any other suggestion will be welcomed...
  >
  >
  > Gus
  >
  > - Original Message -
  > From: Obe, Regina
  > To: PostGIS Users Discussion
  > Sent: Thursday, December 18, 2008 5:08 PM
  > Subject: RE: [postgis-users] pgsql2shp cuts varchar fields
  > Not absolutely sure, but I think its a limit in the spec.  I think fields >
  > 254 are considered to be memo fields in the dbf world and so would be put in
  > a .dbt.
  > Its been a while since I've done any DBase or Foxpro programming so my
  > memory could be wrong.
  >
  > I've never actually seen .dbt files included with ESRI shape files, so I
  > assume ESRI discarded that side of the spec.  If its allowed, then we
  > probably should implement it.
  >
  > Hope that helps,
  > Regina
  >
  >
  > 
  > From: postgis-users-boun...@postgis.refractions.net
  > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Gustavo
  > Ces
  > Sent: Thursday, December 18, 2008 10:53 AM
  > To: PostGIS Users Discussion
  > Subject: [postgis-users] pgsql2shp cuts varchar fields
  >
  > Hi all,
  >
  > maybe this is an old issue... I´m trying to export to shapefile some
  > geometries, but in the result shapefile varchar fields greater than 255 are
  > cutted at this point. I´ve got alternatives but i´d like to know : is there
  > any method to export those fields directly with pgsql2shp or is it a
  > shapefile-format or pgsql2shp limit?
  > Thanks in advance,
  >
  >
  > Gus
  >
  > 
  >
  > The substance of this message, including any attachments, may be
  > confidential, legally privileged and/or exempt from disclosure pursuant to
  > Massachusetts law. It is intended solely for the addressee. If you received
  > this in error, please contact the sender and delete the material from any
  > computer.
  >
  > 
  >
  > Help make the earth a greener place. If at all possible resist printing this
  > email and join us in saving paper.
  >
  > 
  >
  > ___
  > 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






--


  The substance of this message, including any at

Re: [postgis-users] pgsql2shp dbf file encoding

[Mark Cave-Ayland]

Denis Rykov wrote:

Try to export postgis data to shapefiles with pgsql2shp 
(pgsql2shp-core.h 5870 2010-08-28 09:16:32Z mcayland)
If open *.dbf file I see the value in my dbf files at byte 29 is 0x57h. 
Is the 0x57h value is default? Why not 0x00h?

With 0x57h encoding my shapefiles looks not correct in any GIS software.


I don't think it's currently set to anything, so I guess this would be 
the default? Perhaps we should provide a mapping from PostgreSQL 
database encoding names to shapefile encoding values in a table somewhere?


Anyone know which encoding 0x57h represents?


ATB,

Mark.

--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp dbf file encoding

[Francis Markham]

0x57h is the dreaded Windows-1252 codepage.  I believe new versions of
shapelib allow this to be set when the shapefile is created.

Cheers,

Francis Markham


On 25 October 2010 20:05, Mark Cave-Ayland
 wrote:
>
> Denis Rykov wrote:
>
>> Try to export postgis data to shapefiles with pgsql2shp (pgsql2shp-core.h 
>> 5870 2010-08-28 09:16:32Z mcayland)
>> If open *.dbf file I see the value in my dbf files at byte 29 is 0x57h. Is 
>> the 0x57h value is default? Why not 0x00h?
>> With 0x57h encoding my shapefiles looks not correct in any GIS software.
>
> I don't think it's currently set to anything, so I guess this would be the 
> default? Perhaps we should provide a mapping from PostgreSQL database 
> encoding names to shapefile encoding values in a table somewhere?
>
> Anyone know which encoding 0x57h represents?
>
>
> ATB,
>
> Mark.
>
> --
> Mark Cave-Ayland - Senior Technical Architect
> PostgreSQL - PostGIS
> Sirius Corporation plc - control through freedom
> http://www.siriusit.co.uk
> t: +44 870 608 0063
>
> Sirius Labs: http://www.siriusit.co.uk/labs
> ___
> 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] pgsql2shp dbf file encoding

[Denis Rykov]

I don't quite understand why pgsql2shp is writing this encoding to our
shapes, our database is in UTF-8 and we never use win1252

On Mon, Oct 25, 2010 at 5:42 PM, Francis Markham  wrote:

> 0x57h is the dreaded Windows-1252 codepage.  I believe new versions of
> shapelib allow this to be set when the shapefile is created.
>
> Cheers,
>
> Francis Markham
>
>
> On 25 October 2010 20:05, Mark Cave-Ayland
>  wrote:
> >
> > Denis Rykov wrote:
> >
> >> Try to export postgis data to shapefiles with pgsql2shp
> (pgsql2shp-core.h 5870 2010-08-28 09:16:32Z mcayland)
> >> If open *.dbf file I see the value in my dbf files at byte 29 is 0x57h.
> Is the 0x57h value is default? Why not 0x00h?
> >> With 0x57h encoding my shapefiles looks not correct in any GIS software.
> >
> > I don't think it's currently set to anything, so I guess this would be
> the default? Perhaps we should provide a mapping from PostgreSQL database
> encoding names to shapefile encoding values in a table somewhere?
> >
> > Anyone know which encoding 0x57h represents?
> >
> >
> > ATB,
> >
> > Mark.
> >
> > --
> > Mark Cave-Ayland - Senior Technical Architect
> > PostgreSQL - PostGIS
> > Sirius Corporation plc - control through freedom
> > http://www.siriusit.co.uk
> > t: +44 870 608 0063
> >
> > Sirius Labs: http://www.siriusit.co.uk/labs
> > ___
> > 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] pgsql2shp dbf file encoding

[Mark Cave-Ayland]

Denis Rykov wrote:

I don't quite understand why pgsql2shp is writing this encoding to our 
shapes, our database is in UTF-8 and we never use win1252


Well pgsql2shp has never contained any code to set the encoding field 
(mainly because until recently the version of shapelib included with 
PostGIS didn't support the encoding field), so I guess WIN1252 must be 
the shapelib default.



ATB,

Mark.

--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp dbf file encoding

[Paul Ramsey]

And that DBF field dates from the Time Before UTF-8, so there won't be
a "UTF8" number to put in it, in any event.  DBF files with UTF in
them (OSM!) are scary scary scary (for example, should your code for
reading a CHAR(8) field in DBF expect 8 bytes, or 8 characters? yay!)
It would be nice to support transcoding down to the code pages that
*are* supported in that field, I suppose. I wonder how much software
actually supports it.

P.

On Mon, Oct 25, 2010 at 9:57 AM, Mark Cave-Ayland
 wrote:
> Denis Rykov wrote:
>
>> I don't quite understand why pgsql2shp is writing this encoding to our
>> shapes, our database is in UTF-8 and we never use win1252
>
> Well pgsql2shp has never contained any code to set the encoding field
> (mainly because until recently the version of shapelib included with PostGIS
> didn't support the encoding field), so I guess WIN1252 must be the shapelib
> default.
>
>
> ATB,
>
> Mark.
>
> --
> Mark Cave-Ayland - Senior Technical Architect
> PostgreSQL - PostGIS
> Sirius Corporation plc - control through freedom
> http://www.siriusit.co.uk
> t: +44 870 608 0063
>
> Sirius Labs: http://www.siriusit.co.uk/labs
> ___
> 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] pgsql2shp dbf file encoding

[Denis Rykov]

Will it make sense to set 0 as a default? In the current case some software
(ArcGIS) does not override correct CPG setting with obviously incorrect 1252
from the header.

On Tue, Oct 26, 2010 at 12:03 AM, Paul Ramsey  wrote:

> And that DBF field dates from the Time Before UTF-8, so there won't be
> a "UTF8" number to put in it, in any event.  DBF files with UTF in
> them (OSM!) are scary scary scary (for example, should your code for
> reading a CHAR(8) field in DBF expect 8 bytes, or 8 characters? yay!)
> It would be nice to support transcoding down to the code pages that
> *are* supported in that field, I suppose. I wonder how much software
> actually supports it.
>
> P.
>
> On Mon, Oct 25, 2010 at 9:57 AM, Mark Cave-Ayland
>  wrote:
> > Denis Rykov wrote:
> >
> >> I don't quite understand why pgsql2shp is writing this encoding to our
> >> shapes, our database is in UTF-8 and we never use win1252
> >
> > Well pgsql2shp has never contained any code to set the encoding field
> > (mainly because until recently the version of shapelib included with
> PostGIS
> > didn't support the encoding field), so I guess WIN1252 must be the
> shapelib
> > default.
> >
> >
> > ATB,
> >
> > Mark.
> >
> > --
> > Mark Cave-Ayland - Senior Technical Architect
> > PostgreSQL - PostGIS
> > Sirius Corporation plc - control through freedom
> > http://www.siriusit.co.uk
> > t: +44 870 608 0063
> >
> > Sirius Labs: http://www.siriusit.co.uk/labs
> > ___
> > 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] pgsql2shp dbf file encoding

[Paul Ramsey]

Can you hexedit it and see if it works better?

On Mon, Oct 25, 2010 at 10:25 AM, Denis Rykov  wrote:
> Will it make sense to set 0 as a default? In the current case some software
> (ArcGIS) does not override correct CPG setting with obviously incorrect 1252
> from the header.
>
> On Tue, Oct 26, 2010 at 12:03 AM, Paul Ramsey  wrote:
>>
>> And that DBF field dates from the Time Before UTF-8, so there won't be
>> a "UTF8" number to put in it, in any event.  DBF files with UTF in
>> them (OSM!) are scary scary scary (for example, should your code for
>> reading a CHAR(8) field in DBF expect 8 bytes, or 8 characters? yay!)
>> It would be nice to support transcoding down to the code pages that
>> *are* supported in that field, I suppose. I wonder how much software
>> actually supports it.
>>
>> P.
>>
>> On Mon, Oct 25, 2010 at 9:57 AM, Mark Cave-Ayland
>>  wrote:
>> > Denis Rykov wrote:
>> >
>> >> I don't quite understand why pgsql2shp is writing this encoding to our
>> >> shapes, our database is in UTF-8 and we never use win1252
>> >
>> > Well pgsql2shp has never contained any code to set the encoding field
>> > (mainly because until recently the version of shapelib included with
>> > PostGIS
>> > didn't support the encoding field), so I guess WIN1252 must be the
>> > shapelib
>> > default.
>> >
>> >
>> > ATB,
>> >
>> > Mark.
>> >
>> > --
>> > Mark Cave-Ayland - Senior Technical Architect
>> > PostgreSQL - PostGIS
>> > Sirius Corporation plc - control through freedom
>> > http://www.siriusit.co.uk
>> > t: +44 870 608 0063
>> >
>> > Sirius Labs: http://www.siriusit.co.uk/labs
>> > ___
>> > 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] pgsql2shp dbf file encoding

[Denis Rykov]

After editing dbf file in hex editor and set value at byte 29 to 00h
shapefile opens in ArcGIS without
encoding troubles (get codepage value from *.cpg file).

On Tue, Oct 26, 2010 at 12:30 AM, Paul Ramsey  wrote:

> Can you hexedit it and see if it works better?
>
> On Mon, Oct 25, 2010 at 10:25 AM, Denis Rykov  wrote:
> > Will it make sense to set 0 as a default? In the current case some
> software
> > (ArcGIS) does not override correct CPG setting with obviously incorrect
> 1252
> > from the header.
> >
> > On Tue, Oct 26, 2010 at 12:03 AM, Paul Ramsey 
> wrote:
> >>
> >> And that DBF field dates from the Time Before UTF-8, so there won't be
> >> a "UTF8" number to put in it, in any event.  DBF files with UTF in
> >> them (OSM!) are scary scary scary (for example, should your code for
> >> reading a CHAR(8) field in DBF expect 8 bytes, or 8 characters? yay!)
> >> It would be nice to support transcoding down to the code pages that
> >> *are* supported in that field, I suppose. I wonder how much software
> >> actually supports it.
> >>
> >> P.
> >>
> >> On Mon, Oct 25, 2010 at 9:57 AM, Mark Cave-Ayland
> >>  wrote:
> >> > Denis Rykov wrote:
> >> >
> >> >> I don't quite understand why pgsql2shp is writing this encoding to
> our
> >> >> shapes, our database is in UTF-8 and we never use win1252
> >> >
> >> > Well pgsql2shp has never contained any code to set the encoding field
> >> > (mainly because until recently the version of shapelib included with
> >> > PostGIS
> >> > didn't support the encoding field), so I guess WIN1252 must be the
> >> > shapelib
> >> > default.
> >> >
> >> >
> >> > ATB,
> >> >
> >> > Mark.
> >> >
> >> > --
> >> > Mark Cave-Ayland - Senior Technical Architect
> >> > PostgreSQL - PostGIS
> >> > Sirius Corporation plc - control through freedom
> >> > http://www.siriusit.co.uk
> >> > t: +44 870 608 0063
> >> >
> >> > Sirius Labs: http://www.siriusit.co.uk/labs
> >> > ___
> >> > 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] pgsql2shp dbf file encoding

[Mark Cave-Ayland]

Denis Rykov wrote:

After editing dbf file in hex editor and set value at byte 29 to 00h 
shapefile opens in ArcGIS without

encoding troubles (get codepage value from *.cpg file).


That's strange. Does anyone know what the behaviour of the 
psDBF->iLanguageDriver field should be in terms of how it reacts with a 
.cpg file? Frank?



ATB,

Mark.

--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp - No GID variable

[Mark Cave-Ayland]

Andreas Forø Tollefsen wrote:


Hi all,

When using the pgsql2shp the gid column is not included.
I even tried typing a "select gid AS g_id" to include it as a different 
column name, but this did not work.


My syntax: pgsql2shp -f pgfinal2008.shp -h localhost -u user -P pass 
postgis public.pgfinal2008 "SELECT *, gid as g_id FROM pgfinal2008


Any ideas how i manage to extract the gid column from my tables?


Hi Andreas,

Have you tried looking at the -r option for pgsql2shp?


ATB,

Mark.

--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp - No GID variable

[Andreas Forø Tollefsen]

Hi,

I tried that, but at first it did not seem to work when loading the
shapefile into ArcGIS.
However, it seems that i had to restart ArcGIS to make it update the
attribute table for the file.
Now -r works.

Thanks.
Andreas

2010/10/26 Mark Cave-Ayland 

> Andreas Forø Tollefsen wrote:
>
>  Hi all,
>>
>> When using the pgsql2shp the gid column is not included.
>> I even tried typing a "select gid AS g_id" to include it as a different
>> column name, but this did not work.
>>
>> My syntax: pgsql2shp -f pgfinal2008.shp -h localhost -u user -P pass
>> postgis public.pgfinal2008 "SELECT *, gid as g_id FROM pgfinal2008
>>
>> Any ideas how i manage to extract the gid column from my tables?
>>
>
> Hi Andreas,
>
> Have you tried looking at the -r option for pgsql2shp?
>
>
> ATB,
>
> Mark.
>
> --
> Mark Cave-Ayland - Senior Technical Architect
> PostgreSQL - PostGIS
> Sirius Corporation plc - control through freedom
> http://www.siriusit.co.uk
> t: +44 870 608 0063
>
> Sirius Labs: http://www.siriusit.co.uk/labs
> ___
> 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] pgsql2shp syntax - sentence construction

[Stephen Woodbridge]

SenTnel wrote:

Hi! Im working with postgres and geoserver but im just not good at sql
statements, none the less in construction sentences in postgres, and I would
like to create a shapefile from a table in postgres and don't want to make a
costly mistake, so if you help me achieve this using pgsql2shp: 


database name: mydatabase
schema: myschema
table: mytable

shapefile to be created: updatedshp

database user: postgres
password: any

can you help me with the syntax to achieve the shapefile containing all of
the data (and geom) in such table?

Thanks !


SenTel,

pgsql2shp is not a SQL statement it is a command line tool:

[EMAIL PROTECTED]:~$ pgsql2shp
RCSID: $Id: pgsql2shp.c,v 1.82 2006/01/16 10:42:57 strk Exp $
USAGE: pgsql2shp []  [.]
   pgsql2shp []  

OPTIONS:
  -f   Use this option to specify the name of the file
 to create.
  -h   Allows you to specify connection to a database on a
 machine other than the default.
  -p   Allows you to specify a database port other than the default.
  -P   Connect to the database with the specified password.
  -u   Connect to the database as the specified user.
  -g  Specify the geometry column to be exported.
  -b Use a binary cursor.
  -r Raw mode. Do not assume table has been created by
 the loader. This would not unescape attribute names
 and will not skip the 'gid' attribute.
  -k Keep postgresql identifiers case.


So you might use it like:


pgsql2shp -f updatedshp.shp -h localhost -u postgres mydatabase myschema.mytable


Have fun,
  -Steve W

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp syntax - sentence construction

[SenTnel]

Thanks Stephen! 

I must admit that Im not good at this, but Im determined to mantain my
city's database correcting names and roads classifications, etc., that's why
I need to take the postgres data and convert it to shapefile, then correct
things with arcmap, then drop the old postgres data and then re-convert the
updated shapefile to postgres, is this the way you would do it? 

I have one more question about this updating, and this is a "select"
question, (I'll remind you that Im an ignorant in this matters) would you
please construct an sql sentence to achive this:

In mydatabase, myschema, mytable there's a "street" column, where the street
names are, let's say I want to correct a misspeled name, for example, "Geoge
Washington", and want to correct it to "George Washington", how do you write
the select?

Thanks again!

 


Stephen Woodbridge wrote:
> 
> SenTnel wrote:
>> Hi! Im working with postgres and geoserver but im just not good at sql
>> statements, none the less in construction sentences in postgres, and I
>> would
>> like to create a shapefile from a table in postgres and don't want to
>> make a
>> costly mistake, so if you help me achieve this using pgsql2shp: 
>> 
>> database name: mydatabase
>> schema: myschema
>> table: mytable
>> 
>> shapefile to be created: updatedshp
>> 
>> database user: postgres
>> password: any
>> 
>> can you help me with the syntax to achieve the shapefile containing all
>> of
>> the data (and geom) in such table?
>> 
>> Thanks !
> 
> SenTel,
> 
> pgsql2shp is not a SQL statement it is a command line tool:
> 
> [EMAIL PROTECTED]:~$ pgsql2shp
> RCSID: $Id: pgsql2shp.c,v 1.82 2006/01/16 10:42:57 strk Exp $
> USAGE: pgsql2shp []  [.]
> pgsql2shp []  
> 
> OPTIONS:
>-f   Use this option to specify the name of the file
>   to create.
>-h   Allows you to specify connection to a database on a
>   machine other than the default.
>-p   Allows you to specify a database port other than the
> default.
>-P   Connect to the database with the specified password.
>-u   Connect to the database as the specified user.
>-g  Specify the geometry column to be exported.
>-b Use a binary cursor.
>-r Raw mode. Do not assume table has been created by
>   the loader. This would not unescape attribute names
>   and will not skip the 'gid' attribute.
>-k Keep postgresql identifiers case.
> 
> 
> So you might use it like:
> 
>> pgsql2shp -f updatedshp.shp -h localhost -u postgres mydatabase
>> myschema.mytable
> 
> Have fun,
>-Steve W
> 
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/pgsql2shp-syntax---sentence-construction-tp17613265p17613906.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp syntax - sentence construction

[P Kishor]

On 6/2/08, SenTnel <[EMAIL PROTECTED]> wrote:
>
>  Thanks Stephen!
>
>  I must admit that Im not good at this, but Im determined to mantain my
>  city's database correcting names and roads classifications, etc., that's why
>  I need to take the postgres data and convert it to shapefile, then correct
>  things with arcmap, then drop the old postgres data and then re-convert the
>  updated shapefile to postgres, is this the way you would do it?

If you are not changing the geometry, you can do all your attribute
updates as simple SQL updates (see below).

>
>  I have one more question about this updating, and this is a "select"
>  question, (I'll remind you that Im an ignorant in this matters) would you
>  please construct an sql sentence to achive this:
>
>  In mydatabase, myschema, mytable there's a "street" column, where the street
>  names are, let's say I want to correct a misspeled name, for example, "Geoge
>  Washington", and want to correct it to "George Washington", how do you write
>  the select?
>

UPDATE mytable
SET street = 'George Washington'
WHERE street = 'Geoge Washington'

Note the single quotes to delimit the text strings.

You might do well do spend some time looking a few basic SQL tutorials
before launching ahead. You will get more mileage from the tutorials
than from asking these simple questions on list.

http://www.google.com/search?q=sql+tutorial


>  Thanks again!
>
>
>
>
>
>  Stephen Woodbridge wrote:
>  >
>  > SenTnel wrote:
>  >> Hi! Im working with postgres and geoserver but im just not good at sql
>  >> statements, none the less in construction sentences in postgres, and I
>  >> would
>  >> like to create a shapefile from a table in postgres and don't want to
>  >> make a
>  >> costly mistake, so if you help me achieve this using pgsql2shp:
>  >>
>  >> database name: mydatabase
>  >> schema: myschema
>  >> table: mytable
>  >>
>  >> shapefile to be created: updatedshp
>  >>
>  >> database user: postgres
>  >> password: any
>  >>
>  >> can you help me with the syntax to achieve the shapefile containing all
>  >> of
>  >> the data (and geom) in such table?
>  >>
>  >> Thanks !
>  >
>  > SenTel,
>  >
>  > pgsql2shp is not a SQL statement it is a command line tool:
>  >
>  > [EMAIL PROTECTED]:~$ pgsql2shp
>  > RCSID: $Id: pgsql2shp.c,v 1.82 2006/01/16 10:42:57 strk Exp $
>  > USAGE: pgsql2shp []  [.]
>  > pgsql2shp []  
>  >
>  > OPTIONS:
>  >-f   Use this option to specify the name of the file
>  >   to create.
>  >-h   Allows you to specify connection to a database on a
>  >   machine other than the default.
>  >-p   Allows you to specify a database port other than the
>  > default.
>  >-P   Connect to the database with the specified password.
>  >-u   Connect to the database as the specified user.
>  >-g  Specify the geometry column to be exported.
>  >-b Use a binary cursor.
>  >-r Raw mode. Do not assume table has been created by
>  >   the loader. This would not unescape attribute names
>  >   and will not skip the 'gid' attribute.
>  >-k Keep postgresql identifiers case.
>  >
>  >
>  > So you might use it like:
>  >
>  >> pgsql2shp -f updatedshp.shp -h localhost -u postgres mydatabase
>  >> myschema.mytable
>  >
>  > Have fun,
>  >-Steve W
>  >
>  > ___
>  > postgis-users mailing list
>  > postgis-users@postgis.refractions.net
>  > http://postgis.refractions.net/mailman/listinfo/postgis-users
>  >
>  >
>
>
> --
>  View this message in context: 
> http://www.nabble.com/pgsql2shp-syntax---sentence-construction-tp17613265p17613906.html
>  Sent from the PostGIS - User mailing list archive at Nabble.com.
>
>
>  ___
>  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] pgsql2shp syntax - sentence construction

[SenTnel]

Thanks Kishor!

>If you are not changing the geometry, you can do all your attribute
>updates as simple SQL updates

Some changes are just name corrections, but I need also update the maps with
new streets and roads, and that is the most important updates that needs to
be done, since our maps are couple of years old and there are many new
streets to add and that's why Im asking if the best way is to dump postgres
to shape, update and then convert it again. What do you think is the best
way to achive this updating process?

Thanks again!



P Kishor-3 wrote:
> 
> On 6/2/08, SenTnel <[EMAIL PROTECTED]> wrote:
>>
>>  Thanks Stephen!
>>
>>  I must admit that Im not good at this, but Im determined to mantain my
>>  city's database correcting names and roads classifications, etc., that's
>> why
>>  I need to take the postgres data and convert it to shapefile, then
>> correct
>>  things with arcmap, then drop the old postgres data and then re-convert
>> the
>>  updated shapefile to postgres, is this the way you would do it?
> 
> If you are not changing the geometry, you can do all your attribute
> updates as simple SQL updates (see below).
> 
>>
>>  I have one more question about this updating, and this is a "select"
>>  question, (I'll remind you that Im an ignorant in this matters) would
>> you
>>  please construct an sql sentence to achive this:
>>
>>  In mydatabase, myschema, mytable there's a "street" column, where the
>> street
>>  names are, let's say I want to correct a misspeled name, for example,
>> "Geoge
>>  Washington", and want to correct it to "George Washington", how do you
>> write
>>  the select?
>>
> 
> UPDATE mytable
> SET street = 'George Washington'
> WHERE street = 'Geoge Washington'
> 
> Note the single quotes to delimit the text strings.
> 
> You might do well do spend some time looking a few basic SQL tutorials
> before launching ahead. You will get more mileage from the tutorials
> than from asking these simple questions on list.
> 
> http://www.google.com/search?q=sql+tutorial
> 
> 
>>  Thanks again!
>>
>>
>>
>>
>>
>>  Stephen Woodbridge wrote:
>>  >
>>  > SenTnel wrote:
>>  >> Hi! Im working with postgres and geoserver but im just not good at
>> sql
>>  >> statements, none the less in construction sentences in postgres, and
>> I
>>  >> would
>>  >> like to create a shapefile from a table in postgres and don't want to
>>  >> make a
>>  >> costly mistake, so if you help me achieve this using pgsql2shp:
>>  >>
>>  >> database name: mydatabase
>>  >> schema: myschema
>>  >> table: mytable
>>  >>
>>  >> shapefile to be created: updatedshp
>>  >>
>>  >> database user: postgres
>>  >> password: any
>>  >>
>>  >> can you help me with the syntax to achieve the shapefile containing
>> all
>>  >> of
>>  >> the data (and geom) in such table?
>>  >>
>>  >> Thanks !
>>  >
>>  > SenTel,
>>  >
>>  > pgsql2shp is not a SQL statement it is a command line tool:
>>  >
>>  > [EMAIL PROTECTED]:~$ pgsql2shp
>>  > RCSID: $Id: pgsql2shp.c,v 1.82 2006/01/16 10:42:57 strk Exp $
>>  > USAGE: pgsql2shp []  [.]
>>  > pgsql2shp []  
>>  >
>>  > OPTIONS:
>>  >-f   Use this option to specify the name of the file
>>  >   to create.
>>  >-h   Allows you to specify connection to a database on a
>>  >   machine other than the default.
>>  >-p   Allows you to specify a database port other than the
>>  > default.
>>  >-P   Connect to the database with the specified password.
>>  >-u   Connect to the database as the specified user.
>>  >-g  Specify the geometry column to be exported.
>>  >-b Use a binary cursor.
>>  >-r Raw mode. Do not assume table has been created by
>>  >   the loader. This would not unescape attribute names
>>  >   and will not skip the 'gid' attribute.
>>  >-k Keep postgresql identifiers case.
>>  >
>>  >
>>  > So you might use it like:
>>  >
>>  >> pgsql2shp -f updatedshp.shp -h localhost -u postgres mydatabase
>>  >> myschema.mytable
>>  >
>>  > Have fun,
>>  >-Steve W
>>  >
>>  > ___
>>  > postgis-users mailing list
>>  > postgis-users@postgis.refractions.net
>>  > http://postgis.refractions.net/mailman/listinfo/postgis-users
>>  >
>>  >
>>
>>
>> --
>>  View this message in context:
>> http://www.nabble.com/pgsql2shp-syntax---sentence-construction-tp17613265p17613906.html
>>  Sent from the PostGIS - User mailing list archive at Nabble.com.
>>
>>
>>  ___
>>  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
> 
> 

-- 
View this message in context: 
http://www.nabble.com/pgsql2shp-syntax---sentence-construction-tp17613265p17614606.html
Sent from the PostGIS - User mailing list archive 

Re: [postgis-users] pgsql2shp syntax - sentence construction

[P Kishor]

On 6/2/08, SenTnel <[EMAIL PROTECTED]> wrote:
>
>  Thanks Kishor!
>
>
>  >If you are not changing the geometry, you can do all your attribute
>  >updates as simple SQL updates
>
>
> Some changes are just name corrections, but I need also update the maps with
>  new streets and roads, and that is the most important updates that needs to
>  be done, since our maps are couple of years old and there are many new
>  streets to add and that's why Im asking if the best way is to dump postgres
>  to shape, update and then convert it again. What do you think is the best
>  way to achive this updating process?

If you have update a lot of geometry manually, and if you have ArcMap,
then yes, this is probably the easiest route. That is what I would do.

Why do you even have your 2 years old data in PostGIS? Mind you, I am
*for* PostGIS. But, I am wondering -- unless there is a good reason to
take on the complexity of Postgres/PostGIS, why not just keep your
data in Shapefile format?


>
>  Thanks again!
>
>
>
>
>  P Kishor-3 wrote:
>  >
>  > On 6/2/08, SenTnel <[EMAIL PROTECTED]> wrote:
>  >>
>  >>  Thanks Stephen!
>  >>
>  >>  I must admit that Im not good at this, but Im determined to mantain my
>  >>  city's database correcting names and roads classifications, etc., that's
>  >> why
>  >>  I need to take the postgres data and convert it to shapefile, then
>  >> correct
>  >>  things with arcmap, then drop the old postgres data and then re-convert
>  >> the
>  >>  updated shapefile to postgres, is this the way you would do it?
>  >
>  > If you are not changing the geometry, you can do all your attribute
>  > updates as simple SQL updates (see below).
>  >
>  >>
>  >>  I have one more question about this updating, and this is a "select"
>  >>  question, (I'll remind you that Im an ignorant in this matters) would
>  >> you
>  >>  please construct an sql sentence to achive this:
>  >>
>  >>  In mydatabase, myschema, mytable there's a "street" column, where the
>  >> street
>  >>  names are, let's say I want to correct a misspeled name, for example,
>  >> "Geoge
>  >>  Washington", and want to correct it to "George Washington", how do you
>  >> write
>  >>  the select?
>  >>
>  >
>  > UPDATE mytable
>  > SET street = 'George Washington'
>  > WHERE street = 'Geoge Washington'
>  >
>  > Note the single quotes to delimit the text strings.
>  >
>  > You might do well do spend some time looking a few basic SQL tutorials
>  > before launching ahead. You will get more mileage from the tutorials
>  > than from asking these simple questions on list.
>  >
>  > http://www.google.com/search?q=sql+tutorial
>  >
>  >
>  >>  Thanks again!
>  >>
>  >>
>  >>
>  >>
>  >>
>  >>  Stephen Woodbridge wrote:
>  >>  >
>  >>  > SenTnel wrote:
>  >>  >> Hi! Im working with postgres and geoserver but im just not good at
>  >> sql
>  >>  >> statements, none the less in construction sentences in postgres, and
>  >> I
>  >>  >> would
>  >>  >> like to create a shapefile from a table in postgres and don't want to
>  >>  >> make a
>  >>  >> costly mistake, so if you help me achieve this using pgsql2shp:
>  >>  >>
>  >>  >> database name: mydatabase
>  >>  >> schema: myschema
>  >>  >> table: mytable
>  >>  >>
>  >>  >> shapefile to be created: updatedshp
>  >>  >>
>  >>  >> database user: postgres
>  >>  >> password: any
>  >>  >>
>  >>  >> can you help me with the syntax to achieve the shapefile containing
>  >> all
>  >>  >> of
>  >>  >> the data (and geom) in such table?
>  >>  >>
>  >>  >> Thanks !
>  >>  >
>  >>  > SenTel,
>  >>  >
>  >>  > pgsql2shp is not a SQL statement it is a command line tool:
>  >>  >
>  >>  > [EMAIL PROTECTED]:~$ pgsql2shp
>  >>  > RCSID: $Id: pgsql2shp.c,v 1.82 2006/01/16 10:42:57 strk Exp $
>  >>  > USAGE: pgsql2shp []  [.]
>  >>  > pgsql2shp []  
>  >>  >
>  >>  > OPTIONS:
>  >>  >-f   Use this option to specify the name of the file
>  >>  >   to create.
>  >>  >-h   Allows you to specify connection to a database on a
>  >>  >   machine other than the default.
>  >>  >-p   Allows you to specify a database port other than the
>  >>  > default.
>  >>  >-P   Connect to the database with the specified password.
>  >>  >-u   Connect to the database as the specified user.
>  >>  >-g  Specify the geometry column to be exported.
>  >>  >-b Use a binary cursor.
>  >>  >-r Raw mode. Do not assume table has been created by
>  >>  >   the loader. This would not unescape attribute names
>  >>  >   and will not skip the 'gid' attribute.
>  >>  >-k Keep postgresql identifiers case.
>  >>  >
>  >>  >
>  >>  > So you might use it like:
>  >>  >
>  >>  >> pgsql2shp -f updatedshp.shp -h localhost -u postgres mydatabase
>  >>  >> myschema.mytable
>  >>  >
>  >>  > Have fun,
>  >>  >-Steve W
>  >>  >
>  >>  > ___
>  >>  > postgis-users mailing list
>  >>  > postgis-users@postgis.refractions.net
>  >>  > http://postgis.refractions.net

Re: [postgis-users] pgsql2shp syntax - sentence construction

[SenTnel]

Thanks again Kishor! 

The situation is that I work at a Truck Company and we keep a gps vehicle
location service using geoserver and openlayers, our shapefiles were
converted to postgis to be able to display the maps with the vehicle's
positions.

=)



P Kishor-3 wrote:
> 
> On 6/2/08, SenTnel <[EMAIL PROTECTED]> wrote:
>>
>>  Thanks Kishor!
>>
>>
>>  >If you are not changing the geometry, you can do all your attribute
>>  >updates as simple SQL updates
>>
>>
>> Some changes are just name corrections, but I need also update the maps
>> with
>>  new streets and roads, and that is the most important updates that needs
>> to
>>  be done, since our maps are couple of years old and there are many new
>>  streets to add and that's why Im asking if the best way is to dump
>> postgres
>>  to shape, update and then convert it again. What do you think is the
>> best
>>  way to achive this updating process?
> 
> If you have update a lot of geometry manually, and if you have ArcMap,
> then yes, this is probably the easiest route. That is what I would do.
> 
> Why do you even have your 2 years old data in PostGIS? Mind you, I am
> *for* PostGIS. But, I am wondering -- unless there is a good reason to
> take on the complexity of Postgres/PostGIS, why not just keep your
> data in Shapefile format?
> 
> 
>>
>>  Thanks again!
>>
>>
>>
>>
>>  P Kishor-3 wrote:
>>  >
>>  > On 6/2/08, SenTnel <[EMAIL PROTECTED]> wrote:
>>  >>
>>  >>  Thanks Stephen!
>>  >>
>>  >>  I must admit that Im not good at this, but Im determined to mantain
>> my
>>  >>  city's database correcting names and roads classifications, etc.,
>> that's
>>  >> why
>>  >>  I need to take the postgres data and convert it to shapefile, then
>>  >> correct
>>  >>  things with arcmap, then drop the old postgres data and then
>> re-convert
>>  >> the
>>  >>  updated shapefile to postgres, is this the way you would do it?
>>  >
>>  > If you are not changing the geometry, you can do all your attribute
>>  > updates as simple SQL updates (see below).
>>  >
>>  >>
>>  >>  I have one more question about this updating, and this is a "select"
>>  >>  question, (I'll remind you that Im an ignorant in this matters)
>> would
>>  >> you
>>  >>  please construct an sql sentence to achive this:
>>  >>
>>  >>  In mydatabase, myschema, mytable there's a "street" column, where
>> the
>>  >> street
>>  >>  names are, let's say I want to correct a misspeled name, for
>> example,
>>  >> "Geoge
>>  >>  Washington", and want to correct it to "George Washington", how do
>> you
>>  >> write
>>  >>  the select?
>>  >>
>>  >
>>  > UPDATE mytable
>>  > SET street = 'George Washington'
>>  > WHERE street = 'Geoge Washington'
>>  >
>>  > Note the single quotes to delimit the text strings.
>>  >
>>  > You might do well do spend some time looking a few basic SQL tutorials
>>  > before launching ahead. You will get more mileage from the tutorials
>>  > than from asking these simple questions on list.
>>  >
>>  > http://www.google.com/search?q=sql+tutorial
>>  >
>>  >
>>  >>  Thanks again!
>>  >>
>>  >>
>>  >>
>>  >>
>>  >>
>>  >>  Stephen Woodbridge wrote:
>>  >>  >
>>  >>  > SenTnel wrote:
>>  >>  >> Hi! Im working with postgres and geoserver but im just not good
>> at
>>  >> sql
>>  >>  >> statements, none the less in construction sentences in postgres,
>> and
>>  >> I
>>  >>  >> would
>>  >>  >> like to create a shapefile from a table in postgres and don't
>> want to
>>  >>  >> make a
>>  >>  >> costly mistake, so if you help me achieve this using pgsql2shp:
>>  >>  >>
>>  >>  >> database name: mydatabase
>>  >>  >> schema: myschema
>>  >>  >> table: mytable
>>  >>  >>
>>  >>  >> shapefile to be created: updatedshp
>>  >>  >>
>>  >>  >> database user: postgres
>>  >>  >> password: any
>>  >>  >>
>>  >>  >> can you help me with the syntax to achieve the shapefile
>> containing
>>  >> all
>>  >>  >> of
>>  >>  >> the data (and geom) in such table?
>>  >>  >>
>>  >>  >> Thanks !
>>  >>  >
>>  >>  > SenTel,
>>  >>  >
>>  >>  > pgsql2shp is not a SQL statement it is a command line tool:
>>  >>  >
>>  >>  > [EMAIL PROTECTED]:~$ pgsql2shp
>>  >>  > RCSID: $Id: pgsql2shp.c,v 1.82 2006/01/16 10:42:57 strk Exp $
>>  >>  > USAGE: pgsql2shp []  [.]
>>  >>  > pgsql2shp []  
>>  >>  >
>>  >>  > OPTIONS:
>>  >>  >-f   Use this option to specify the name of the file
>>  >>  >   to create.
>>  >>  >-h   Allows you to specify connection to a database on a
>>  >>  >   machine other than the default.
>>  >>  >-p   Allows you to specify a database port other than the
>>  >>  > default.
>>  >>  >-P   Connect to the database with the specified
>> password.
>>  >>  >-u   Connect to the database as the specified user.
>>  >>  >-g  Specify the geometry column to be
>> exported.
>>  >>  >-b Use a binary cursor.
>>  >>  >-r Raw mode. Do not assume table has been created by
>>  >>  >   the loader. This would not unescape attribute names
>>  >>  >   and will not sk

Re: [postgis-users] pgsql2shp syntax - sentence construction

[SenTnel]

Thanks guys! nicely done! Worked fine!!
:jumping::clap:


SenTnel wrote:
> 
> Thanks again Kishor! 
> 
> The situation is that I work at a Truck Company and we keep a gps vehicle
> location service using geoserver and openlayers, our shapefiles were
> converted to postgis to be able to display the maps with the vehicle's
> positions.
> 
> =)
> 
> 
> 
> P Kishor-3 wrote:
>> 
>> On 6/2/08, SenTnel <[EMAIL PROTECTED]> wrote:
>>>
>>>  Thanks Kishor!
>>>
>>>
>>>  >If you are not changing the geometry, you can do all your attribute
>>>  >updates as simple SQL updates
>>>
>>>
>>> Some changes are just name corrections, but I need also update the maps
>>> with
>>>  new streets and roads, and that is the most important updates that
>>> needs to
>>>  be done, since our maps are couple of years old and there are many new
>>>  streets to add and that's why Im asking if the best way is to dump
>>> postgres
>>>  to shape, update and then convert it again. What do you think is the
>>> best
>>>  way to achive this updating process?
>> 
>> If you have update a lot of geometry manually, and if you have ArcMap,
>> then yes, this is probably the easiest route. That is what I would do.
>> 
>> Why do you even have your 2 years old data in PostGIS? Mind you, I am
>> *for* PostGIS. But, I am wondering -- unless there is a good reason to
>> take on the complexity of Postgres/PostGIS, why not just keep your
>> data in Shapefile format?
>> 
>> 
>>>
>>>  Thanks again!
>>>
>>>
>>>
>>>
>>>  P Kishor-3 wrote:
>>>  >
>>>  > On 6/2/08, SenTnel <[EMAIL PROTECTED]> wrote:
>>>  >>
>>>  >>  Thanks Stephen!
>>>  >>
>>>  >>  I must admit that Im not good at this, but Im determined to mantain
>>> my
>>>  >>  city's database correcting names and roads classifications, etc.,
>>> that's
>>>  >> why
>>>  >>  I need to take the postgres data and convert it to shapefile, then
>>>  >> correct
>>>  >>  things with arcmap, then drop the old postgres data and then
>>> re-convert
>>>  >> the
>>>  >>  updated shapefile to postgres, is this the way you would do it?
>>>  >
>>>  > If you are not changing the geometry, you can do all your attribute
>>>  > updates as simple SQL updates (see below).
>>>  >
>>>  >>
>>>  >>  I have one more question about this updating, and this is a
>>> "select"
>>>  >>  question, (I'll remind you that Im an ignorant in this matters)
>>> would
>>>  >> you
>>>  >>  please construct an sql sentence to achive this:
>>>  >>
>>>  >>  In mydatabase, myschema, mytable there's a "street" column, where
>>> the
>>>  >> street
>>>  >>  names are, let's say I want to correct a misspeled name, for
>>> example,
>>>  >> "Geoge
>>>  >>  Washington", and want to correct it to "George Washington", how do
>>> you
>>>  >> write
>>>  >>  the select?
>>>  >>
>>>  >
>>>  > UPDATE mytable
>>>  > SET street = 'George Washington'
>>>  > WHERE street = 'Geoge Washington'
>>>  >
>>>  > Note the single quotes to delimit the text strings.
>>>  >
>>>  > You might do well do spend some time looking a few basic SQL
>>> tutorials
>>>  > before launching ahead. You will get more mileage from the tutorials
>>>  > than from asking these simple questions on list.
>>>  >
>>>  > http://www.google.com/search?q=sql+tutorial
>>>  >
>>>  >
>>>  >>  Thanks again!
>>>  >>
>>>  >>
>>>  >>
>>>  >>
>>>  >>
>>>  >>  Stephen Woodbridge wrote:
>>>  >>  >
>>>  >>  > SenTnel wrote:
>>>  >>  >> Hi! Im working with postgres and geoserver but im just not good
>>> at
>>>  >> sql
>>>  >>  >> statements, none the less in construction sentences in postgres,
>>> and
>>>  >> I
>>>  >>  >> would
>>>  >>  >> like to create a shapefile from a table in postgres and don't
>>> want to
>>>  >>  >> make a
>>>  >>  >> costly mistake, so if you help me achieve this using pgsql2shp:
>>>  >>  >>
>>>  >>  >> database name: mydatabase
>>>  >>  >> schema: myschema
>>>  >>  >> table: mytable
>>>  >>  >>
>>>  >>  >> shapefile to be created: updatedshp
>>>  >>  >>
>>>  >>  >> database user: postgres
>>>  >>  >> password: any
>>>  >>  >>
>>>  >>  >> can you help me with the syntax to achieve the shapefile
>>> containing
>>>  >> all
>>>  >>  >> of
>>>  >>  >> the data (and geom) in such table?
>>>  >>  >>
>>>  >>  >> Thanks !
>>>  >>  >
>>>  >>  > SenTel,
>>>  >>  >
>>>  >>  > pgsql2shp is not a SQL statement it is a command line tool:
>>>  >>  >
>>>  >>  > [EMAIL PROTECTED]:~$ pgsql2shp
>>>  >>  > RCSID: $Id: pgsql2shp.c,v 1.82 2006/01/16 10:42:57 strk Exp $
>>>  >>  > USAGE: pgsql2shp []  [.]
>>>  >>  > pgsql2shp []  
>>>  >>  >
>>>  >>  > OPTIONS:
>>>  >>  >-f   Use this option to specify the name of the file
>>>  >>  >   to create.
>>>  >>  >-h   Allows you to specify connection to a database on a
>>>  >>  >   machine other than the default.
>>>  >>  >-p   Allows you to specify a database port other than
>>> the
>>>  >>  > default.
>>>  >>  >-P   Connect to the database with the specified
>>> password.
>>>  >>  >-u   Connect to the database as the specified user.
>>>  >> 

[postgis-users] pgsql2shp can not connect to server

[Erwin Poeze]

Hello,

I'm a newbie to postGIS and worked my way through a tutorial.

In the tutorial a spatial database is created and filled with the
contents of a shapefile (using shp2pgsql).
One of the last steps is to create a new shapefile from the database
using pgsql2shp. The exact command
is "pgsql2shp g4wd us_50", where g4wd is the database name and us_50
the table containing the spatial information.
The response to this command is:

# pgsql2shp g4wd us_50
could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I tried all the options like setting the port, hostname and the
geometry table, but nothing helps. The database itself is working
fine.

Does someone have an idea of the error I'm making?

Erwin Poeze
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] pgsql2shp ERROR:mixed geometry types in table

[Denis Rykov]

Hi, I have a PostGIS table with Polygonal and Multi Polygonal  types
of geometry.
I used the pgsql2shp last year and had no problems. But some days ago
I've update pgsql2shp from svn.
And now I can't convert my PostGIS data into shapefile because I get
an error : "ERROR: Mixed geometry types in table".
I think that it is not necessary to consider this case as erroneous.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] pgsql2shp does not have an encoding option!

[Stephen Woodbridge]

Hi all,

I tend to keep all my databases in UTF8, and I just had an occasion 
where I needed to dump a postGIS table as LATIN1. Much to my surprise I 
found that pgsql2shp does not appear to have an option to set the client 
encoding.


This might already have a ticket, haven't looked yet.

Any thoughts on this?

-Steve
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp ERROR:mixed geometry types in table

[Denis Rykov]

I've tried to convert all my geometries to MULTI* with ST_Multi()
function but it works very slowly.

2010/9/5 Denis Rykov :
> Hi, I have a PostGIS table with Polygonal and Multi Polygonal  types
> of geometry.
> I used the pgsql2shp last year and had no problems. But some days ago
> I've update pgsql2shp from svn.
> And now I can't convert my PostGIS data into shapefile because I get
> an error : "ERROR: Mixed geometry types in table".
> I think that it is not necessary to consider this case as erroneous.
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp ERROR:mixed geometry types in table

[Stephen Woodbridge]

On 9/5/2010 10:16 PM, Denis Rykov wrote:

I've tried to convert all my geometries to MULTI* with ST_Multi()
function but it works very slowly.

2010/9/5 Denis Rykov:

Hi, I have a PostGIS table with Polygonal and Multi Polygonal  types
of geometry.
I used the pgsql2shp last year and had no problems. But some days ago
I've update pgsql2shp from svn.
And now I can't convert my PostGIS data into shapefile because I get
an error : "ERROR: Mixed geometry types in table".
I think that it is not necessary to consider this case as erroneous.


Denis,

How you converting them? Using and UPDATE?
This would be very slow because every (most?) rows in the table would 
need to be updated.


There are a couple of faster alternatives:

1. create table newtable select ..., st_multi(the_geom) form ...;
   then dump that table

2. pgsql2shp []  "select ..., st_multi(the_geom) as 
the_geom from "


-Steve W
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] pgsql2shp ERROR:mixed geometry types in table

[Denis Rykov]

Yes, I convert my data using the following command "UPDATE osm_line SET way
= ST_Multi(way) where geometrytype(way) = 'LINESTRING'".

> time psql -U pgsql -d osm -c "UPDATE osm_line SET way = ST_Multi(way) where
> geometrytype(way) = 'LINESTRING';"
> UPDATE 914007
>
> real 70m12.384s
> user 0m0.009s
> sys 0m0.011s
>
 Many thanks for response. I will try them.
But actually I wouldn't want to convert my data to MULTI*. It is look like
hack to work with the new version of pgsql2shp.
I think that adding any option to pgsql2shp for turn of checking of geometry
types or downgrading to previous version of pgsql2shp can solve my problem.


2010/9/6 Stephen Woodbridge :
> On 9/5/2010 10:16 PM, Denis Rykov wrote:
>>
>> I've tried to convert all my geometries to MULTI* with ST_Multi()
>> function but it works very slowly.
>>
>> 2010/9/5 Denis Rykov:
>>>
>>> Hi, I have a PostGIS table with Polygonal and Multi Polygonal  types
>>> of geometry.
>>> I used the pgsql2shp last year and had no problems. But some days ago
>>> I've update pgsql2shp from svn.
>>> And now I can't convert my PostGIS data into shapefile because I get
>>> an error : "ERROR: Mixed geometry types in table".
>>> I think that it is not necessary to consider this case as erroneous.
>
> Denis,
>
> How you converting them? Using and UPDATE?
> This would be very slow because every (most?) rows in the table would need
> to be updated.
>
> There are a couple of faster alternatives:
>
> 1. create table newtable select ..., st_multi(the_geom) form ...;
>   then dump that table
>
> 2. pgsql2shp []  "select ..., st_multi(the_geom) as
> the_geom from "
>
> -Steve W
> ___
> 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] pgsql2shp does not have an encoding option!

[Paul Ramsey]

Setting an environment variable should do the trick, but a commandline  
option would be an improvement


Sent from my iPod

On 25-Jan-09, at 10:33 AM, Stephen Woodbridge  
 wrote:



Hi all,

I tend to keep all my databases in UTF8, and I just had an occasion  
where I needed to dump a postGIS table as LATIN1. Much to my  
surprise I found that pgsql2shp does not appear to have an option to  
set the client encoding.


This might already have a ticket, haven't looked yet.

Any thoughts on this?

-Steve
___
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