[postgis] How to use pgsql2shp

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

[postgis] How to use pgsql2shp

lr1234567
Is there a particular structure that pgsql2shp is expecting

When I try to use it on a table I created it generates the dbf fine,
but can't generate the shp file

It states as an error, can not have multiple geometry types in a
shape file.

It works fine on tables I generated using the shp2pgsql loader.

The way I created and populated the table I'm trying to dump out is
as follows

create table remsactive ( PARCELID varchar(10), ADDRESS varchar,
NEIGHBORHO varchar, USE varchar, TYPE varchar, STATUS varchar,
LOTAREA int);

select AddGeometryColumn
('dts','remsactive','the_geom','102686','MULTIPOLYGON',2);

INSERT INTO remsactive(gid, parcelid, address, neighborho,use, type,
status, lotarea, the_geom)
SELECT gid, parcelid, address,neighborhood, cuse, propertytype,
status, sqfeet,the_geom
FROM vwavailableparcels;

_______
the vwavailableparcels is a view I set up that joins a table of
multipolygon geometries I loaded from a shape file with a non-
geometry table.




------------------------ Yahoo! Groups Sponsor ---------------------~-->
Promise to Quit
Nicotrol will help
http://us.click.yahoo.com/5vN8tD/AqSDAA/ySSFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] How to use pgsql2shp

Jeff Lounsbury
This is simply a limitation of the .shp format. The format only allows a file
to consists of homogeneous shape types.
eg  all Points , all MultiPolygons etc...

What you can do if you really need the shapes in .shp format is keep the
different types in separate PostGIS tables and then dump each of the tables.

The other possibility is converting all the shapes to the most complex type.
eg. Just call points multipoints so you can have a single multipoint .shp
file. If the types are very different like points and mutipolygons, this will
probably not work though.
-Jeff

lr1234567 wrote:

> Is there a particular structure that pgsql2shp is expecting
>
> When I try to use it on a table I created it generates the dbf fine,
> but can't generate the shp file
>
> It states as an error, can not have multiple geometry types in a
> shape file.
>
> It works fine on tables I generated using the shp2pgsql loader.
>
> The way I created and populated the table I'm trying to dump out is
> as follows
>
> create table remsactive ( PARCELID varchar(10), ADDRESS varchar,
> NEIGHBORHO varchar, USE varchar, TYPE varchar, STATUS varchar,
> LOTAREA int);
>
> select AddGeometryColumn
> ('dts','remsactive','the_geom','102686','MULTIPOLYGON',2);
>
> INSERT INTO remsactive(gid, parcelid, address, neighborho,use, type,
> status, lotarea, the_geom)
> SELECT gid, parcelid, address,neighborhood, cuse, propertytype,
> status, sqfeet,the_geom
> FROM vwavailableparcels;
>
> _______
> the vwavailableparcels is a view I set up that joins a table of
> multipolygon geometries I loaded from a shape file with a non-
> geometry table.
>
>
> To unsubscribe from this group, send an email to:
> [hidden email]
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/


To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

[postgis] Re: How to use pgsql2shp

lr1234567
Jeff,

That part I understand about the limitation of shp, but the geometry
table I was joining with only has one type of geometry
(multipolygons) - and that is the geometry field I am selecting in my
view.  The other table I am joining with has no geometries.

My view query looks something like this

SELECT ap.*, l.the_geom
FROM ap INNER JOIN landparcels l ON ap.parcelid = l.parcelid


landparcels is the one with the geometry field of type multipolygon
and ap has no geometries - just a simple postgres table.

I assumed that if I defined my new table remsactive setting the
geometry type the same as my landparcels, I would only have one type
of geometry.

Note that if I try to dump out landparcels using pgsql2shp it works
fine.  Or is the fact that I am inserting from a view posing a
problem.





--- In postgis@y..., jeff lounsbury <jeffloun@r...> wrote:
> This is simply a limitation of the .shp format. The format only
allows a file
> to consists of homogeneous shape types.
> eg  all Points , all MultiPolygons etc...
>
> What you can do if you really need the shapes in .shp format is
keep the
> different types in separate PostGIS tables and then dump each of
the tables.
>
> The other possibility is converting all the shapes to the most
complex type.
> eg. Just call points multipoints so you can have a single
multipoint .shp
> file. If the types are very different like points and mutipolygons,
this will
> probably not work though.
> -Jeff
>
> lr1234567 wrote:
>
> > Is there a particular structure that pgsql2shp is expecting
> >
> > When I try to use it on a table I created it generates the dbf
fine,
> > but can't generate the shp file
> >
> > It states as an error, can not have multiple geometry types in a
> > shape file.
> >
> > It works fine on tables I generated using the shp2pgsql loader.
> >
> > The way I created and populated the table I'm trying to dump out
is

> > as follows
> >
> > create table remsactive ( PARCELID varchar(10), ADDRESS varchar,
> > NEIGHBORHO varchar, USE varchar, TYPE varchar, STATUS varchar,
> > LOTAREA int);
> >
> > select AddGeometryColumn
> > ('dts','remsactive','the_geom','102686','MULTIPOLYGON',2);
> >
> > INSERT INTO remsactive(gid, parcelid, address, neighborho,use,
type,

> > status, lotarea, the_geom)
> > SELECT gid, parcelid, address,neighborhood, cuse, propertytype,
> > status, sqfeet,the_geom
> > FROM vwavailableparcels;
> >
> > _______
> > the vwavailableparcels is a view I set up that joins a table of
> > multipolygon geometries I loaded from a shape file with a non-
> > geometry table.
> >
> >
> > To unsubscribe from this group, send an email to:
> > postgis-unsubscribe@y...
> >
> >
> >
> > Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/


------------------------ Yahoo! Groups Sponsor ---------------------~-->
Tiny Wireless Camera under $80!
Order Now! FREE VCR Commander!
Click Here - Only 1 Day Left!
http://us.click.yahoo.com/75YKVC/7.PDAA/ySSFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Re: How to use pgsql2shp

Jeff Lounsbury
Hmmm, that error gets printed whenever there is more than a single geometry
type in the result of the query so maybe you join is creating some rows that
have Null geometry types and the dumped doesn't handle that very nicely I
notice looking at it. Can you send me any more info you have, maybe a dump of
your table possibly and I can try to look at it, but I'm pretty busy these
days with other projests so I can't promise much timeliness. Feel free to
poke at the code yourself, i assuming you probably just have to check for a
unique geomtry OR (a unique one plus some NULL's ) bracketed is not happening
in current code. Or maybe I'm completly wrong and something is happening due
to the view instead of a basic table.
-Jeff
lr1234567 wrote:

> Jeff,
>
> That part I understand about the limitation of shp, but the geometry
> table I was joining with only has one type of geometry
> (multipolygons) - and that is the geometry field I am selecting in my
> view.  The other table I am joining with has no geometries.
>
> My view query looks something like this
>
> SELECT ap.*, l.the_geom
> FROM ap INNER JOIN landparcels l ON ap.parcelid = l.parcelid
>
> landparcels is the one with the geometry field of type multipolygon
> and ap has no geometries - just a simple postgres table.
>
> I assumed that if I defined my new table remsactive setting the
> geometry type the same as my landparcels, I would only have one type
> of geometry.
>
> Note that if I try to dump out landparcels using pgsql2shp it works
> fine.  Or is the fact that I am inserting from a view posing a
> problem.
>
> --- In postgis@y..., jeff lounsbury <jeffloun@r...> wrote:
> > This is simply a limitation of the .shp format. The format only
> allows a file
> > to consists of homogeneous shape types.
> > eg  all Points , all MultiPolygons etc...
> >
> > What you can do if you really need the shapes in .shp format is
> keep the
> > different types in separate PostGIS tables and then dump each of
> the tables.
> >
> > The other possibility is converting all the shapes to the most
> complex type.
> > eg. Just call points multipoints so you can have a single
> multipoint .shp
> > file. If the types are very different like points and mutipolygons,
> this will
> > probably not work though.
> > -Jeff
> >
> > lr1234567 wrote:
> >
> > > Is there a particular structure that pgsql2shp is expecting
> > >
> > > When I try to use it on a table I created it generates the dbf
> fine,
> > > but can't generate the shp file
> > >
> > > It states as an error, can not have multiple geometry types in a
> > > shape file.
> > >
> > > It works fine on tables I generated using the shp2pgsql loader.
> > >
> > > The way I created and populated the table I'm trying to dump out
> is
> > > as follows
> > >
> > > create table remsactive ( PARCELID varchar(10), ADDRESS varchar,
> > > NEIGHBORHO varchar, USE varchar, TYPE varchar, STATUS varchar,
> > > LOTAREA int);
> > >
> > > select AddGeometryColumn
> > > ('dts','remsactive','the_geom','102686','MULTIPOLYGON',2);
> > >
> > > INSERT INTO remsactive(gid, parcelid, address, neighborho,use,
> type,
> > > status, lotarea, the_geom)
> > > SELECT gid, parcelid, address,neighborhood, cuse, propertytype,
> > > status, sqfeet,the_geom
> > > FROM vwavailableparcels;
> > >
> > > _______
> > > the vwavailableparcels is a view I set up that joins a table of
> > > multipolygon geometries I loaded from a shape file with a non-
> > > geometry table.
> > >
> > >
> > > To unsubscribe from this group, send an email to:
> > > postgis-unsubscribe@y...
> > >
> > >
> > >
> > > Your use of Yahoo! Groups is subject to
> http://docs.yahoo.com/info/terms/
>
>
> To unsubscribe from this group, send an email to:
> [hidden email]
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/


To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

[postgis] Re: How to use pgsql2shp

lr1234567
Jeff,

Thanks a bunch.  Although I did not have any null geometries in my
land parcel table, I double checked my view.  I was wrong about the
definition of my view it was actually a left join rather than an
inner join because and as a result did have some null geometries.

I got rid of the nulls with a where clause and that resolved the
problem.


--- In postgis@y..., jeff lounsbury <jeffloun@r...> wrote:
> Hmmm, that error gets printed whenever there is more than a single
geometry
> type in the result of the query so maybe you join is creating some
rows that
> have Null geometry types and the dumped doesn't handle that very
nicely I
> notice looking at it. Can you send me any more info you have, maybe
a dump of
> your table possibly and I can try to look at it, but I'm pretty
busy these
> days with other projests so I can't promise much timeliness. Feel
free to
> poke at the code yourself, i assuming you probably just have to
check for a
> unique geomtry OR (a unique one plus some NULL's ) bracketed is not
happening
> in current code. Or maybe I'm completly wrong and something is
happening due
> to the view instead of a basic table.
> -Jeff
> lr1234567 wrote:
>
> > Jeff,
> >
> > That part I understand about the limitation of shp, but the
geometry
> > table I was joining with only has one type of geometry
> > (multipolygons) - and that is the geometry field I am selecting
in my
> > view.  The other table I am joining with has no geometries.
> >
> > My view query looks something like this
> >
> > SELECT ap.*, l.the_geom
> > FROM ap INNER JOIN landparcels l ON ap.parcelid = l.parcelid
> >
> > landparcels is the one with the geometry field of type
multipolygon
> > and ap has no geometries - just a simple postgres table.
> >
> > I assumed that if I defined my new table remsactive setting the
> > geometry type the same as my landparcels, I would only have one
type
> > of geometry.
> >
> > Note that if I try to dump out landparcels using pgsql2shp it
works

> > fine.  Or is the fact that I am inserting from a view posing a
> > problem.
> >
> > --- In postgis@y..., jeff lounsbury <jeffloun@r...> wrote:
> > > This is simply a limitation of the .shp format. The format only
> > allows a file
> > > to consists of homogeneous shape types.
> > > eg  all Points , all MultiPolygons etc...
> > >
> > > What you can do if you really need the shapes in .shp format is
> > keep the
> > > different types in separate PostGIS tables and then dump each of
> > the tables.
> > >
> > > The other possibility is converting all the shapes to the most
> > complex type.
> > > eg. Just call points multipoints so you can have a single
> > multipoint .shp
> > > file. If the types are very different like points and
mutipolygons,

> > this will
> > > probably not work though.
> > > -Jeff
> > >
> > > lr1234567 wrote:
> > >
> > > > Is there a particular structure that pgsql2shp is expecting
> > > >
> > > > When I try to use it on a table I created it generates the dbf
> > fine,
> > > > but can't generate the shp file
> > > >
> > > > It states as an error, can not have multiple geometry types
in a
> > > > shape file.
> > > >
> > > > It works fine on tables I generated using the shp2pgsql
loader.
> > > >
> > > > The way I created and populated the table I'm trying to dump
out
> > is
> > > > as follows
> > > >
> > > > create table remsactive ( PARCELID varchar(10), ADDRESS
varchar,

> > > > NEIGHBORHO varchar, USE varchar, TYPE varchar, STATUS varchar,
> > > > LOTAREA int);
> > > >
> > > > select AddGeometryColumn
> > > > ('dts','remsactive','the_geom','102686','MULTIPOLYGON',2);
> > > >
> > > > INSERT INTO remsactive(gid, parcelid, address, neighborho,use,
> > type,
> > > > status, lotarea, the_geom)
> > > > SELECT gid, parcelid, address,neighborhood, cuse,
propertytype,
> > > > status, sqfeet,the_geom
> > > > FROM vwavailableparcels;
> > > >
> > > > _______
> > > > the vwavailableparcels is a view I set up that joins a table
of

> > > > multipolygon geometries I loaded from a shape file with a non-
> > > > geometry table.
> > > >
> > > >
> > > > To unsubscribe from this group, send an email to:
> > > > postgis-unsubscribe@y...
> > > >
> > > >
> > > >
> > > > Your use of Yahoo! Groups is subject to
> > http://docs.yahoo.com/info/terms/
> >
> >
> > To unsubscribe from this group, send an email to:
> > postgis-unsubscribe@y...
> >
> >
> >
> > Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/


------------------------ Yahoo! Groups Sponsor ---------------------~-->
Stop Smoking Now
Nicotrol will help
http://us.click.yahoo.com/2vN8tD/_pSDAA/ySSFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/