[postgis-users] unique id in QGIS/PostGIS
Margie Roswell
mroswell at gmail.com
Mon Apr 8 19:27:58 PDT 2013
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>
>> 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
>>
>>
>
> _______________________________________________
> 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/20130408/cdb5d7fc/attachment.html>
More information about the postgis-users
mailing list