<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Am 25.07.2013 12:37, schrieb mane
moshref:<br>
</div>
<blockquote
cite="mid:1374748620.1716.YahooMailNeo@web125903.mail.ne1.yahoo.com"
type="cite">
<pre wrap="">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);
}
}
}
</pre>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
Hi Mane,<br>
<br>
maybe it works, if you add "row_number() over () as ids" to your
query string, i.e.<br>
<br>
<pre wrap="">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
</pre>
<br>
</body>
</html>