[postgis-users] pgsql2shp syntax - sentence construction

[P Kishor]

On 6/2/08, SenTnel <elvin.pimentel at gmail.com> 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 <elvin.pimentel at gmail.com> 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:
>  >>  >
>  >>  > woodbri at carto:~$ pgsql2shp
>  >>  > RCSID: $Id: pgsql2shp.c,v 1.82 2006/01/16 10:42:57 strk Exp $
>  >>  > USAGE: pgsql2shp [<options>] <database> [<schema>.]<table>
>  >>  >         pgsql2shp [<options>] <database> <query>
>  >>  >
>  >>  > OPTIONS:
>  >>  >    -f <filename>  Use this option to specify the name of the file
>  >>  >       to create.
>  >>  >    -h <host>  Allows you to specify connection to a database on a
>  >>  >       machine other than the default.
>  >>  >    -p <port>  Allows you to specify a database port other than the
>  >>  > default.
>  >>  >    -P <password>  Connect to the database with the specified password.
>  >>  >    -u <user>  Connect to the database as the specified user.
>  >>  >    -g <geometry_column> 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 at 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 at postgis.refractions.net
>  >>  http://postgis.refractions.net/mailman/listinfo/postgis-users
>  >>
>  > _______________________________________________
>  > postgis-users mailing list
>  > postgis-users at 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 at Nabble.com.
>
>  _______________________________________________
>  postgis-users mailing list
>  postgis-users at postgis.refractions.net
>  http://postgis.refractions.net/mailman/listinfo/postgis-users
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/