[postgis-users] unique id in QGIS/PostGIS

Sun Apr 7 15:38:23 PDT 2013

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:  <mailto: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 <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, <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 <mroswell at gmail.com> wrote:

From: Margie Roswell <mroswell at gmail.com>
Subject: [postgis-users] unique id in QGIS/PostGIS
To: "PostGIS Users Discussion" <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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130407/c74d5656/attachment.html>