[postgis-users] unique id in QGIS/PostGIS
pcreso at pcreso.com
pcreso at pcreso.com
Tue Apr 9 09:46:57 PDT 2013
Hi Margie,
Try:
export PATH=/usr/local/bin:/cygdrive/c/Program\ Files/PostgreSQL/9.2/bin
echo $PATH
then
which psql
and note - there is no command "postgres"
Brent
--- On Tue, 4/9/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: "Greg Williamson" <gwilliamson39 at yahoo.com>, "PostGIS Users Discussion" <postgis-users at lists.osgeo.org>
Date: Tuesday, April 9, 2013, 3:42 PM
Thanks so much for responding
At this point, I've fiddled so much with the PATH, I'm nowhere. Can't even run that log command you suggested.
So, I'm really curious: what environments are people working in? SSH to remote Ubuntu, like E2? macboook? Anyone having success scripting on the windows side?
I had hoped to have my desktop also serve as my local dev environment for postgres/postgis/qgis/tilemill... I'm pretty much there, except on the windows side I could only use the GUI, and so far no luck on the command line side of things.... and the latter was offered as a solution to some of the barriers I was experiencing.
The space in "Program Files" is an issue, and escaping it doesn't seem to work. Neither does quoting it. I've done a bunch of googling, and maybe I'm just too tired to get there tonight.
owner at owner-PC ~/src
$ PATH=/usr/local/bin:`/cygdrive/c/Program\ Files/PostgreSQL/9.2/bin`
-bash: /cygdrive/c/Program Files/PostgreSQL/9.2/bin: is a directory
owner at owner-PC ~/src
$ export PATH
owner at owner-PC ~/src
$ $PATH
-bash: /usr/local/bin:: No such file or directory
owner at owner-PC ~/src
$ postgres log
-bash: postgres: command not found
--
http://FarmBillPrimer.orghttp://www.BaltimoreUrbanAg.org (Please send events; This site is hungry.)
http://www.ExcellentNutrition.org
http://www.packtpub.com/drupal-5-views-recipes/book
On Mon, Apr 8, 2013 at 11:25 PM, Greg Williamson <gwilliamson39 at yahoo.com> wrote:
Margie --
What does the postgres log say b?
If it showsnothing the attempt to connect never made it to the postgres port (usually 5432 but I don't inow what Windows might be using for a local connection).
If it did make the basix handshake, the log message might show an authentication issue or other useful tidbit.
People with more windows experience might tell you where to look for the log and what to tweak on the config side to allow connections locally.
Greg Williamson
From: Margie Roswell <mroswell at gmail.com>
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Sent: Monday, April 8, 2013 7:27 PM
Subject: Re: [postgis-users] unique id in QGIS/PostGIS
This thread morphed from a QGIS issue to a configuration and scripting and environment issue. I'm fine with that... if I get
a comfortable working environment, I'll be ready to tackle other learning curves... so now I'll report my struggles on this "environment" front.
First, I might mention that I'm actually generally competent in a linux environment. Command line, no problem. And usually I can routinely edit files in vim, for instance, but I'll have to figure out a slew of key bindings to make vim remotely usable now that I'm in windows Not a problem, I've got Sublime Text 2. So, ignoring that issue the need-sensible vim-key-bindings issue for now.
I'm choosing windows because it's where my comfortable keyboard is, my large monitor, my desk. Little macbook laptop can't compete on this front. (I'll consider another system, if I totally strike out on the windows side... but hoping for one desk, one desktop.)
Biggest issue now is basically 'hello world'. My GUI interface was working... but now on the Cygwin side, I've got this. (I'm copying from the postgres documentation, because the error matches:
createdb: could not connect to database template1: could not connect to server:
No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
This means that the server was not started, or it was not started where createdb expected it. Again, check the installation instructions or consult the administrator.
----------------------------------So, I'm really at ground zero on the cygwin side of things. I've got ;C:\Program Files\PostgreSQL\9.2\bin in my path. So, I'm not sure how to start the server.
And I've also added to bash path (without success here) http://stackoverflow.com/questions/4918830/how-can-i-set-my-cygwin-path-to-find-javac
owner at owner-PC ~/src$ export PATH=$PATH:/cygdrive/c/Program\ Files/PostgreSQL/9.2/bin
owner at owner-PC ~/src$ $PATH-bash: /usr/local/bin:/cygdrive/c/Program: No such file or directory
and lots of variations.
Sorry, so long. This is just all an earnest way of asking: what do I do to get past the
createdb: could not connect to database template1: could not connect to server:
No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?error?
Margie
P.S.
My goal was to place random dots, in legislative districts, 1, 2, or 3, as appropriate. No problem, QGIS makes that easy. But basically I wanted an internal buffer for each district shape, because I didn't like how close to the edge those dots were appearing. and that's when my trouble began. despite an accurate buffer statement, the map refused to show. So, a few days ago, I was on a far more advanced part of the learning curve, and now, trying to get an environment that'll run a script... or even just run ANY postgres command.
http://FarmBillPrimer.org
http://www.BaltimoreUrbanAg.org (Please send events; This site is hungry.)
http://www.ExcellentNutrition.org
http://www.packtpub.com/drupal-5-views-recipes/book
On Sun, Apr 7, 2013 at 8:39 PM, Stephen Mather <mather.stephen at gmail.com> wrote:
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
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
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
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
REM
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
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.orghttp://www.BaltimoreUrbanAg.org
(Please send events; This site is hungry.)
http://www.ExcellentNutrition.org
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/
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.orghttp://www.BaltimoreUrbanAg.org
(Please send events; This site is hungry.)
http://www.ExcellentNutrition.org
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://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.orghttp://www.BaltimoreUrbanAg.org
(Please send events; This site is hungry.)
http://www.ExcellentNutrition.org
http://www.packtpub.com/drupal-5-views-recipes/book
On Thu, Apr 4, 2013 at 11:21 AM, Lelo - Luiz Rogério
De Pieri <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
www.twitter.com/lelopieri
blogdolelo.wordpress.com
_______________________________________________
postgis-users
mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
-----Inline
Attachment Follows-----
_______________________________________________
postgis-users
mailing list
postgis-users at lists.osgeo.org
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
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
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
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
-----Inline Attachment Follows-----
_______________________________________________
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/20130409/6e2321ff/attachment.html>
More information about the postgis-users
mailing list