[postgis-users] How can I add one column to the table in my existing query?!

[Wolf-E. Altmann]

Am 25.07.2013 12:37, schrieb mane moshref:
> Hi all,
> I have one question.
> I wrote this query in my java code:
> "CREATE table many_line AS SELECT ST_AsText(St_makeline(sp, ep)) as line, gmlid, parent_id, root_id from (select st_pointN(geom, generate_series(1, ST_NPoints(geom)-1)) as sp, ST_PointN(geom, generate_series(2, ST_NPoints(geom)  )) as ep, gmlid, parent_id, root_id FROM (SELECT (ST_Dump(ST_Boundary(geometry))).geom, g.gmlid, g.parent_id, g.root_id FROM surface_geometry g where (g.parent_id=15) ) AS linestrings ) AS segments"; It works perfectly but my problem is that I need also to have a unique id for each line in the created table. How can I include the column of line ids to this table?
>
> For more clarity I put my code here as well:
>
> public static void linstring(String url, String username, String password){
>           Connection con = null;
>              Statement st = null;
>              ResultSet rs = null;
>              
>              String Query ="CREATE table many_line AS SELECT ST_AsText(St_makeline(sp, ep)) as line, gmlid, parent_id, root_id from (select st_pointN(geom, generate_series(1, ST_NPoints(geom)-1)) as sp, ST_PointN(geom, generate_series(2, ST_NPoints(geom)  )) as ep, gmlid, parent_id, root_id FROM (SELECT (ST_Dump(ST_Boundary(geometry))).geom, g.gmlid, g.parent_id, g.root_id FROM surface_geometry g where (g.parent_id=15) ) AS linestrings ) AS segments";
>              
>              
>              try{
>                  
>                  Class.forName("org.postgresql.Driver");
>                  con = DriverManager.getConnection(url, username, password);
>                  st= con.createStatement();
>                  rs=st.executeQuery(Query);
>                  
>                  while(rs.next()){
>                      System.out.println(rs.getString(1)+"/");
>                      System.out.println(rs.getString(2)+"/");
>                      System.out.print(rs.getInt(3)+"/");
>                      System.out.println(rs.getInt(4)+"/");
>                  }
>                  
>              }catch (SQLException ex) {
>                  Logger lgr = Logger.getLogger(database.class.getName());
>                  lgr.log(Level.SEVERE, ex.getMessage(), ex);
>
>          } catch (ClassNotFoundException e) {
>                  e.printStackTrace();
>                  }
>          
>          finally {
>
>                  try {
>                      if (rs != null) {
>                          rs.close();
>                      }
>                      if (st != null) {
>                          st.close();
>                      }
>                      if (con != null) {
>                          con.close();
>                      }
>
>                  } catch (SQLException ex) {
>                      Logger lgr = Logger.getLogger(database.class.getName());
>                      lgr.log(Level.WARNING, ex.getMessage(), ex);
>                  }
>              }
>              
>      }
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Hi Mane,

maybe it works, if you add "row_number() over () as ids" to your query 
string, i.e.

String Query ="CREATE table many_line AS SELECT row_number() over () as ids, ST_AsText(St_makeline(sp, ep)) as line, gmlid, parent_id, root_id from (select st_pointN(geom, generate_series(1, ST_NPoints(geom)-1)) as sp, ST_PointN(geom, generate_series(2, ST_NPoints(geom)  )) as ep, gmlid, parent_id, root_id FROM (SELECT (ST_Dump(ST_Boundary(geometry))).geom, g.gmlid, g.parent_id, g.root_id FROM surface_geometry g where (g.parent_id=15) ) AS linestrings ) AS segments";

Wolf


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