[postgis-users] unique id in QGIS/PostGIS

[Stephen Mather]

I would add to this that you can put different shapefiles into different
tables:

for /r %%z in (*.dbf) do (%SHP2PGSQL%   -s 4326 -g geom -W "latin1" %%z
%%~nz | %PSQL% )

where *%~nz* becomes the output tablename based on the name without
extension of the input shapefile.

Also, if you don't want to run this as a batch file, but just run it on the
command line, use a single percent:

for /r %z in (*.dbf) do (%SHP2PGSQL%   -s 4326 -g geom -W "latin1" %z %~nz
| %PSQL% )


If you are interested in using PowerShell for this, it looks something like
this:

$shapefile = dir *.shp

$x = 0

$shapefile |foreach {

write-host ($x++) "of $($shapefile.count) is started. $($_.Name) is
processing"

write-host "shp2pgsql -s 4326 -g geom -W latin1 $($_.name) $($_.basename) |
psql"

}

I often write out all the flags I need for psql, so it might look more like
this:

$shapefile = dir *.shp

$x = 0

$shapefile |foreach {

write-host ($x++) "of $($shapefile.count) is started. $($_.Name) is
processing"

write-host "shp2pgsql -s 4326 -g geom -W latin1 $($_.name) $($_.basename) |
psql -U postgres -d databasename -h localhost -p 5432"

}



Best,
Steve







On Sun, Apr 7, 2013 at 6:41 PM, Paragon Corporation <lr at pcorp.us> wrote:

> **
> Slight correction forgot the )
>
> for /r %%z in (*.dbf) do (%SHP2PGSQL%   -s 4326 -g geom -W "latin1" %%z
> sometable | %PSQL% )
>
> You can also chain commands by using &
>
> for /r %%z in (*.dbf) do (%SHP2PGSQL%   -s 4326 -g geom -W "latin1" %%z
> sometable | %PSQL% & %PSQL% -c "SELECT postgis_full_version()" )
>  ------------------------------
> *From:* postgis-users-bounces at lists.osgeo.org [mailto:
> postgis-users-bounces at lists.osgeo.org] *On Behalf Of *Paragon Corporation
> *Sent:* Sunday, April 07, 2013 6:38 PM
> *To:* 'PostGIS Users Discussion'
>
> *Subject:* Re: [postgis-users] unique id in QGIS/PostGIS
>
>  You can also use msys mingw. We use the mingw64 chain which allows you
> to run linux shell scripts. That's what we use to compile PostGIS for
> windows.
>
> That aside, probably for what you want to do for starters as Brent
> suggested, batch scripts are sufficient.
>
> Our general batch script for loading up shape files looks something like
> this:
>
> set PGBIN=C:\Program Files\PostgreSQL\9.2\bin\
> set PGPORT=5432
> set PGHOST=localhost
> set PGUSER=postgres
> set PGPASSWORD=something
> set PGDATABASE=mygisdb
> set PSQL="%PGBIN%psql"
> set SHP2PGSQL="%PGBIN%shp2pgsql"
>
> %SHP2PGSQL% -c -s 4326  -g geom   -W "latin1" somefile.shp sometable |
> %PSQL%
>
> If you need to load up a whole bunch of files in a table you can use the
> for do batch command
>
> for /r %%z in (*.dbf) do (%SHP2PGSQL%   -s 4326 -g geom -W "latin1" %%z
> sometable | %PSQL%
>
>
> Hope that helps,
> Leo and Regina
> http://www.postgis.us
> http://postgis.net
>
>
>
>
>  ------------------------------
> *From:* postgis-users-bounces at lists.osgeo.org [mailto:
> postgis-users-bounces at lists.osgeo.org] *On Behalf Of *Marjorie Roswell
> *Sent:* Sunday, April 07, 2013 2:37 PM
> *To:* pcreso at pcreso.com
> *Cc:* postgis-users at lists.osgeo.org
> *Subject:* Re: [postgis-users] unique id in QGIS/PostGIS
>
>  Thanks immensely. Some years ago on a different computer, I installed
> cygwin.  Great reminder, and I appreciate it.  I'll be back at my computer
> this evening.
>
> Sent from my iPhone
>
> On Apr 7, 2013, at 2:11 PM, pcreso at pcreso.com wrote:
>
>    HI Margie,
>
> I gave up on Windows as a productive computing environment last century, &
> have zero regrets about that these days, so I'm not the best person to
> offer Windows oriented help... however...
>
> There are three ways to run scripts under Windows - Y
>
> 1. You can install Cygwin - which gives you a Linux-like environment
> within Windows, but is somewhat complicated & arcane. This does allow you
> to run the exact same bash scripts under Windows & Linux.
>
> 2. You can install Windows powershell & write your scripts using that
> "language":
> <http://technet.microsoft.com/en-us/scriptcenter/dd742419.aspx>
> http://technet.microsoft.com/en-us/scriptcenter/dd742419.aspx
>
> 3. You can write your scripts as batch files. Batch files are pretty
> limited, without text processing & other capabilities, but are probably
> able to do what you are looking to do right now. All Windows systems can
> run batch files with no additional software installed.
>
> So, I'd suggest you look at batch files initially.
>
> Google "batch file tutorial" & there are plenty to choose from, like
> <http://www.chebucto.ns.ca/~ak621/DOS/BatBasic.html>
> http://www.chebucto.ns.ca/~ak621/DOS/BatBasic.html
>
>  If you open a terminal window & type "psql -l" then press enter, you'll
> know if your path is already set up as they need to be. This is probably
> the only tricky thing you might need to configure. If it works, great, if
> not you'll need to add the folder containing the psql command to your path.
> If you do need to do this, see:
> <http://www.computerhope.com/issues/ch000549.htm>
> http://www.computerhope.com/issues/ch000549.htm
> but it should be unecessary.
>
> The simple shell script as a batch file - if I remember correctly :-)
> __________________________________________________________
> REM script to load Legislative_shape.zip & create unique index
>
> SHP=Legislative_final_shapefile\Legislative_Final.shp
>
> REM create new, empty Postgres database
> dropdb test
> createdb test
>
> REM install postgis
> psql -d test -c "create extension postgis;"
>
> REM determine projection
> REM assume from these links that the SRID is 3559 -
> REM   I couldn't find one to match the prj file directly
> REM <http://planning.maryland.gov/msdc/S5_Map_GIS.shtml>
> http://planning.maryland.gov/msdc/S5_Map_GIS.shtml
> REM <http://www.spatialreference.org/ref/sr-org/3559/>
> http://www.spatialreference.org/ref/sr-org/3559/
>
> REM load the shapefile the easy way...
> shp2pgsql -s 3559 -I %SHP% legislative_final | psql -d test
> ___________________________________________________________
>
>
> Cheers,
>
>   Brent
> --- On *Mon, 4/8/13, Margie Roswell <mroswell at gmail.com>* wrote:
>
>
> From: Margie Roswell <mroswell at gmail.com>
> Subject: Re: [postgis-users] unique id in QGIS/PostGIS
> To: <pcreso at pcreso.com>pcreso at pcreso.com
> Cc: "PostGIS Users Discussion" <postgis-users at lists.osgeo.org>
> Date: Monday, April 8, 2013, 2:45 AM
>
> One step at a time. I figured out how to set the path in windows, to
> access psql
>
> So, next, to try to figure out how to script... (on the windows side)
>
> inching forward,
>
> Margie
>
>  --
>  <http://FarmBillPrimer.org>http://FarmBillPrimer.org
> <http://www.BaltimoreUrbanAg.org>http://www.BaltimoreUrbanAg.org (Please
> send events; This site is hungry.)
> <http://www.ExcellentNutrition.org>http://www.ExcellentNutrition.org
> <http://www.packtpub.com/drupal-5-views-recipes/book>
> http://www.packtpub.com/drupal-5-views-recipes/book
>
>
> On Sun, Apr 7, 2013 at 10:35 AM, Margie Roswell <<http://mc/compose?to=mroswell@gmail.com>
> mroswell at gmail.com> wrote:
>
> Thanks very much. I like the idea of scripting these routine behaviors.
>
> 1. I figured out that the correct CRS is
> <http://spatialreference.org/ref/epsg/4019/>
> http://spatialreference.org/ref/epsg/4019/
>
> It basically says (in QGIS)
> Unknown datum based upon the GRS 1980 ellipsoid EPSG:4019
> but it does exactly match the PRJ file for legislative_final shapefile.
>
> A friend suggests using Maryland State Plan, so, I'll likely switch that
> command to
>
> shp2pgsql -s 3559:26985 -I $SHP legislative_final | psql -d test
>
> 2. I do have access to a PostGIS install on a mac or an EC2, but right now
> my preferred environment is windows, with my large screen, and special
> wrist-friendly keyboard.* So, I'm going to have to figure out how to run
> this script.* (or even how run anything from the command line on the
> windows side.)
>
> If anyone has some insight into how to run or replicate a bash script on a
> windows installation, let me know. The* psql tool that comes with windows
> starts right inside a database*, so I haven't actually yet figured out
> how to even run a simple postgres command on the windows side. No success
> from either the SQL shell or the regular windows command prompt.
>
> The scripting approach:
> - offers helpful comments to remind us of the data source.
> - offers a record of exactly what we did. (Part of the reason I've been
> using PgAdmin III is that I have appreciated the history file there.)
>
> Since I like this scripting approach, I'm off to try to figure out how to
> do it on the windows side.... Is anyone here running postgres on windows,
> with command line success?
>
> Margie
>
>
>  --
>  <http://FarmBillPrimer.org>http://FarmBillPrimer.org
> <http://www.BaltimoreUrbanAg.org>http://www.BaltimoreUrbanAg.org (Please
> send events; This site is hungry.)
> <http://www.ExcellentNutrition.org>http://www.ExcellentNutrition.org
> <http://www.packtpub.com/drupal-5-views-recipes/book>
> http://www.packtpub.com/drupal-5-views-recipes/book
>
>
>  On Sat, Apr 6, 2013 at 6:37 PM, <<http://mc/compose?to=pcreso@pcreso.com>
> pcreso at pcreso.com> wrote:
>
>   Hi again,
>
> In your screencap your SRID seems to be set to zero. Even if the data
> displays in QGIS, it will not be in the correct place, unless you have this
> set correctly. So with the unique index/prikary key - you still probably
> won't see the data unless you zoom the that layer extent.
>
> I have scribbled out a quick Linux script which uses the shp2pgsql command
> to load the shapefile, & which creates the unique index automatically...
> I'm not certain I got the SRID correct - so it will be worth checking.
>
> HTH,
>
>   Brent Wood
>
>
> #! /bin/bash
> # script to load Legislative_shape.zip & create unique index
>
> SHP=Legislative_final_shapefile/Legislative_Final.shp
>
> # create new, empty Postgres database
> dropdb test
> createdb test
>
> # install postgis
> psql -d test -c "create extension postgis;"
>
> # determine projection
> # assume from these links that the SRID is 3559 -
> #   I couldn't find one to match the prj file directly
> # <http://planning.maryland.gov/msdc/S5_Map_GIS.shtml>
> http://planning.maryland.gov/msdc/S5_Map_GIS.shtml
> # <http://www.spatialreference.org/ref/sr-org/3559/>
> http://www.spatialreference.org/ref/sr-org/3559/
>
> # load the shapefile the easy way...
> # use -I to create the primary key
> shp2pgsql -s 3559 -I $SHP legislative_final | psql -d test
>
>
> --- On *Sat, 4/6/13, Margie Roswell <<http://mc/compose?to=mroswell@gmail.com>
> mroswell at gmail.com>* wrote:
>
>
> From: Margie Roswell < <http://mc/compose?to=mroswell@gmail.com>
> mroswell at gmail.com>
> Subject: [postgis-users] unique id in QGIS/PostGIS
> To: "PostGIS Users Discussion" <<http://mc/compose?to=postgis-users@lists.osgeo.org>
> postgis-users at lists.osgeo.org>
> Date: Saturday, April 6, 2013, 9:18 PM
>
>  I've been googling up a storm, now that I've got a local postgis
> database with QGIS. But still haven't figured out how to display a newly
> created table.
>
> The query I used to create the table:
> CREATE TABLE smaller_leg_final AS
> SELECT ST_Buffer(geom,-500) AS geom, legislative_final.district
> FROM legislative_final
> WHERE area > 30;
>
> I then discovered that I needed a unique ID, and did the following:
> ALTER TABLE smaller_leg_final ADD COLUMN id serial not null;
>
> This successfully populated the ID field with unique integers.
>
> However when I go into QGIS, the Primary Key column offers no options.
> when I click into it, it just asks me to choose an SRID, which I believe
> I've already done. The table loads, but nothing displays.
>
> I honestly feel a bit like a toddler given ice skates and placed on an icy
> rink. I'm toddling my way around, falling, getting back up, and then
> looking up and asking: why did I fall?
>
> Once upon a time I was at the true cutting edge of GIS... I purchased
> MapInfo versions 1.1, and upgraded from there for many years. Moving into
> QGIS/Posgres/postGIS now, and well on the path to success, but .... not...
> quite... there.
>
> Are there any QGIS users here who can help me to display my new table?
>
> Do I need a different postgres statement? Is there a different field name
> expected? Maybe my query generated invalid shapes... But still, the geom
> field is displaying in the "Add PostGIS layers "Geometry column", but the
> Primary key column option doesn't allow me to select my 'id' field.
>
> Insight welcome.
>
>
>
>
>  --
>  <http://FarmBillPrimer.org>http://FarmBillPrimer.org
> <http://www.BaltimoreUrbanAg.org>http://www.BaltimoreUrbanAg.org (Please
> send events; This site is hungry.)
> <http://www.ExcellentNutrition.org>http://www.ExcellentNutrition.org
> <http://www.packtpub.com/drupal-5-views-recipes/book>
> http://www.packtpub.com/drupal-5-views-recipes/book
>
>
> On Thu, Apr 4, 2013 at 11:21 AM, Lelo - Luiz Rogério De Pieri <<http://mc/compose?to=lelo.pieri@gmail.com>
> lelo.pieri at gmail.com> wrote:
>
>   Hi all,
>
> I'm having a problem to execute a pgsql2shp command using java.
>
> My OS is Ubuntu and it's frequently I use to generate shp files using
> pgsql2shp, but now I want to do it through java.
>
> The follow command works fine on terminal
> pgsql2shp -f pontos_05600202_teste -h myhost -u myuser -P mypasswd -g
> coordinates my_database "SELECT * from points where id_uploaded_file =
> 37995";
>
> Now I'm trying to execute the follow code:
>
> ProcessBuilder pb = new ProcessBuilder("pgsql2shp", "-f
> pontos_05600202_teste", "-h myhost", "-u myuser", "-P mypasswd", "-g
> coordinates", "my_database", "SELECT coordinates, date from points where
> id_uploaded_file = 37995");
>
> Process proc;
> proc = pb.start();
> InputStream stdin = proc.getInputStream();
> InputStreamReader isr = new InputStreamReader(stdin);
> BufferedReader br = new BufferedReader(isr);
>
> String line = null;
> System.out.println("<OUTPUT>");
>
> while ((line = br.readLine()) != null)
>     System.out.println(line);
>
> System.out.println("</OUTPUT>");
> int exitVal = proc.waitFor();
> System.out.println("Process exitValue: " + exitVal);
>
>
> And the result is:
>
> <OUTPUT>
> Preparing table for user query... Done.
> Initializing...  coordinates: no such attribute in table
> __pgsql2shp14493_tmp_table
> </OUTPUT>
>
> Does anybody as any idea about it?
>
> Thank you for all
>
> Best regards
> --
> Rogério De Pieri  (Lelo)
> SCJP 5
>  Buscando melhorar a cada dia
> Áudio, Hardware & Software
>  <http://www.twitter.com/lelopieri>www.twitter.com/lelopieri
>  <http://blogdolelo.wordpress.com>blogdolelo.wordpress.com
>
> _______________________________________________
> postgis-users mailing list
> <http://mc/compose?to=postgis-users@lists.osgeo.org>
> postgis-users at lists.osgeo.org
> <http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users>
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
> -----Inline Attachment Follows-----
>
>
> _______________________________________________
> postgis-users mailing list
> <http://mc/compose?to=postgis-users@lists.osgeo.org>
> postgis-users at lists.osgeo.org
> <http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users>
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130407/f51ffb3a/attachment.html>