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
[email protected]
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
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
