<table cellspacing="0" cellpadding="0" border="0" ><tr><td valign="top" style="font: inherit;">Margie,<br><br>QGIS needs to "know" that the id is guaranteed unique.<br><br>If you (but don't try it :-) ran the SQL:<br>UPDATE smaller_leg_final SET id = 0;<br><br>then all your id values would be set to 0, so it is not a safe assumption that the values in the id column will always be unique.<br><br>This can be fixed either by creating a unique index on the column, or by setting it as the table's primary key. In either case, the database will not allw duplicate values, so QGIS can safely use the column as the unique id.<br><br><br>Your SQL to create the column as a primary key would be:<br>ALTER TABLE smaller_leg_final ADD COLUMN id serial primary key;<br><br><br>The SQL to set it as a primary key, now that you have already created it is:<br>ALTER TABLE smaller_leg_final<br>
ADD CONSTRAINT smaller_leg_final_pk PRIMARY KEY(id); <br><br>The SQL to add the unique index is:<br>CREATE UNIQUE INDEX smaller_leg_final_pk ON smaller_leg_final(id);<br><br>Otherwise I think you are on the right track.<br><br>Cheers<br><br>Brent Wood<br><br><br><br>--- On <b>Sat, 4/6/13, Margie Roswell <i><mroswell@gmail.com></i></b> wrote:<br><blockquote style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br>From: Margie Roswell <mroswell@gmail.com><br>Subject: [postgis-users] unique id in QGIS/PostGIS<br>To: "PostGIS Users Discussion" <postgis-users@lists.osgeo.org><br>Date: Saturday, April 6, 2013, 9:18 PM<br><br><div id="yiv164055138">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. <br><br>The query I used to create the table:<br><div style="margin-left:40px;">
CREATE TABLE smaller_leg_final AS<br>SELECT ST_Buffer(geom,-500) AS geom, legislative_final.district<br>FROM legislative_final<br>WHERE area > 30;<br></div><br>I then discovered that I needed a unique ID, and did the following:<br>
<div style="margin-left:40px;">ALTER TABLE smaller_leg_final ADD COLUMN id serial not null;<br></div><br>This successfully populated the ID field with unique integers.<br><br>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. <br>
<br>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?<br><br>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.<br>
<br>Are there any QGIS users here who can help me to display my new table?<br><br>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.<br>
<br>Insight welcome.<br><br><br><br><br clear="all"><div><div>--</div><div><a rel="nofollow" target="_blank" href="http://FarmBillPrimer.org">http://FarmBillPrimer.org</a></div><a rel="nofollow" target="_blank" href="http://www.BaltimoreUrbanAg.org">http://www.BaltimoreUrbanAg.org</a> (Please send events; This site is hungry.)<br>
<a rel="nofollow" target="_blank" href="http://www.ExcellentNutrition.org">http://www.ExcellentNutrition.org</a><br><a rel="nofollow" target="_blank" href="http://www.packtpub.com/drupal-5-views-recipes/book">http://www.packtpub.com/drupal-5-views-recipes/book</a><br>
</div>
<br><br><div class="yiv164055138gmail_quote">On Thu, Apr 4, 2013 at 11:21 AM, Lelo - Luiz Rogério De Pieri <span dir="ltr"><<a rel="nofollow" ymailto="mailto:lelo.pieri@gmail.com" target="_blank" href="/mc/compose?to=lelo.pieri@gmail.com">lelo.pieri@gmail.com</a>></span> wrote:<br><blockquote class="yiv164055138gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div dir="ltr"><div><div><div>Hi all,<br><br></div>I'm having a problem to execute a pgsql2shp command using java.<br><br></div>My OS is Ubuntu and it's frequently I use to generate shp files using pgsql2shp, but now I want to do it <span lang="en"><span>through java.<br>
<br></span></span></div><div><span lang="en"><span>The follow command works fine on terminal<br>pgsql2shp -f pontos_05600202_teste -h myhost -u myuser -P mypasswd -g coordinates my_database "SELECT * from points where id_uploaded_file = 37995";<br>
<br></span></span></div><div><span lang="en"><span>Now I'm trying to execute the follow code:<br><br><span style="font-family:courier new, monospace;">ProcessBuilder pb = new ProcessBuilder("pgsql2shp", "-f pontos_05600202_teste", "-h myhost", "-u myuser", "-P mypasswd", "-g </span></span></span><span lang="en"><span><span style="font-family:courier new, monospace;"><span lang="en"><span><span style="font-family:courier new, monospace;">coordinates</span></span></span>", "my_database", "SELECT coordinates, date from points where id_uploaded_file = 37995");<br>
<br>Process proc;<br>proc = pb.start();<br>InputStream stdin = proc.getInputStream();<br>InputStreamReader isr = new InputStreamReader(stdin);<br>BufferedReader br = new BufferedReader(isr);<br><br>String line = null;<br>
System.out.println("<OUTPUT>");<br><br>while ((line = br.readLine()) != null)<br> System.out.println(line);<br><br>System.out.println("</OUTPUT>");<br>int exitVal = proc.waitFor();<br>System.out.println("Process exitValue: " + exitVal);</span><br>
</span></span></div><div><br clear="all"><div><div><div><div><div><div><div><div><br></div><div>And the result is:<br><br><span style="font-family:courier new, monospace;"><OUTPUT><br>Preparing table for user query... Done.<br>
Initializing... </span><span style="font-family:courier new, monospace;"><span lang="en"><span><span style="font-family:courier new, monospace;">coordinates</span></span></span>: no such attribute in table __pgsql2shp14493_tmp_table<br>
</OUTPUT></span><br><br></div><div>Does anybody as any idea about it?<br><br></div><div>Thank you for all<br><br>Best regards<span class="yiv164055138HOEnZb"><font color="#888888"><br></font></span></div><span class="yiv164055138HOEnZb"><font color="#888888"><div>
-- <br>Rogério De Pieri (Lelo)<div>SCJP 5<br><div><div>Buscando melhorar a cada dia<br>
Áudio, Hardware & Software</div></div><div><a rel="nofollow" target="_blank" href="http://www.twitter.com/lelopieri">www.twitter.com/lelopieri</a></div><div><a rel="nofollow" target="_blank" href="http://blogdolelo.wordpress.com">blogdolelo.wordpress.com</a></div>
</div>
</div></font></span></div></div></div></div></div></div></div></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a rel="nofollow" ymailto="mailto:postgis-users@lists.osgeo.org" target="_blank" href="/mc/compose?to=postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a rel="nofollow" target="_blank" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br>
</div><br>-----Inline Attachment Follows-----<br><br><div class="plainMail">_______________________________________________<br>postgis-users mailing list<br><a ymailto="mailto:postgis-users@lists.osgeo.org" href="/mc/compose?to=postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></div></blockquote></td></tr></table>