[postgis-users] unique id in QGIS/PostGIS

Sun Apr 7 07:45:53 PDT 2013

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://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.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 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.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 Thu, Apr 4, 2013 at 11:21 AM, Lelo - Luiz Rogério De Pieri <
>> lelo.pieri at gmail.com <http://mc/compose?to=lelo.pieri@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://mc/compose?to=postgis-users@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://mc/compose?to=postgis-users@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/c4488d99/attachment.html>