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

mane moshref many_yammy at yahoo.com
Thu Jul 25 03:37:00 PDT 2013


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);
                }
            }
            
    }
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130725/486f5649/attachment.html>


More information about the postgis-users mailing list