Consulta em base de dados espacial postgis em java
Boa tarde!!
Estou tentando conectar Java com o Postgis onde já tenho algumas tabelas espaciais.
Inicialmente segui um exemplo da internet que funcionou corretamente consultando uma tabela que tem vetores de linhas e retorna a sequência de coordenadas que as formam.
Segue abaixo:
Ele me retorna a geometria em uma string com a sequencia de coordenadas que a formam assim:
Como disse o exemplo acima deu certo.
Agora estou criando o meu projeto mais "elegantemente", esta é minha classe connection
Aqui a minha classe ônibus, estou em dúvida de que tipo usar no para o campo geom da tabela espacial.
A minha finalidade é desenhar essas linhas usando a google maps api, pensando nisso qual é a melhor forma de recuperá-las do bd como no exemplo acima
que traz a sequência de coordenadas numa string? Ou existe um tipo de objeto linha e depois é só plotar no mapa?
Sei que existe o Hibernate Spacial para fazer esse mapeamento queria fazer na mão a título de conhecimento, alguém conhece algum artigo ou tuto usando hibernate spacial com postgis pra indicar?
Estou tentando conectar Java com o Postgis onde já tenho algumas tabelas espaciais.
Inicialmente segui um exemplo da internet que funcionou corretamente consultando uma tabela que tem vetores de linhas e retorna a sequência de coordenadas que as formam.
Segue abaixo:
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import org.postgis.PGgeometry;
public class JavaGIS {
public static void main(String[] args) {
java.sql.Connection conn;
try {
/*
* Load the JDBC driver and establish a connection.
*/
Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://localhost:5432/SpatialTeste";
conn = DriverManager.getConnection(url, "postgres", "130999");
/*
* Add the geometry types to the connection.
* Deve converter a conexão com a conexão específica pgsql
* implementation before calling the addDataType() method.
*/
((org.postgresql.PGConnection)conn).addDataType("geometry",Class.forName("org.postgis.PGgeometry"));
((org.postgresql.PGConnection)conn).addDataType("box3d",Class.forName("org.postgis.PGbox3d"));
/*
* Create a statement and execute a select query.
*/
Statement s = conn.createStatement();
ResultSet r = s.executeQuery("select geom,id,rota from onibus");
while( r.next() ) {
/*
* Retrieve the geometry as an object then cast it to the geometry type.
* Print things out.
*/
PGgeometry geom = (PGgeometry)r.getObject(1);
int id = r.getInt(2);
System.out.println("Row " + id + ":");
System.out.println(geom.toString());
}
s.close();
conn.close();
}
catch( Exception e ) {
e.printStackTrace();
}
}
}
Ele me retorna a geometria em uma string com a sequencia de coordenadas que a formam assim:
SRID=4326;MULTILINESTRING((-51.150568 -30.064866,-51.151272999999996 -30.064731,-51.151481999999994 -30.064642,-51.151807999999996 -30.064518999999997,-51.152041 -30.064453999999998,-51.152231 -30.064411,-51.152519999999996 -30.064383999999997,-51.152643 -30.064387,-51.153459999999995 -30.064404,-51.153704999999995 -30.064407,-51.154239 -30.064389,-51.154823 -30.064287,-51.155232 -30.064173,-51.155469 -30.06411,-51.156363999999996 -30.063872,-51.156859 -30.063730999999997,-51.157118 -30.063658999999998,-51.157855 -30.063416999999998,-51.158570999999995 -30.063143999999998,-51.159490999999996 -30.062706,-51.160345 -30.062255999999998,-51.161072 -30.061882999999998,-51.161445 -30.061698,-51.161674 -30.061594999999997,-51.161967 -30.061494999999997,-51.162515 -30.06137,-51.162639 -30.061356999999997,-51.163036999999996 -30.061332999999998,-51.164054 -30.061322999999998,-51.164528 -30.061283999999997,-51.166045 -30.061073,-51.166109999999996 -30.061064,-51.167606 -30.060881,-51.167727 -30.060868,-51.168726 -30.060768999999997,-51.169042999999995 -30.060738999999998,-51.169636 -30.060719,-51.169791 -30.060727999999997,-51.170393999999995 -30.060772,-51.171141 -30.060871,-51.171462 -30.0609,-51.171763 -30.060899,-51.172608 -30.060805,-51.172943 -30.060796,-51.173153 -30.060809,-51.173369 -30.06085,-51.174298 -30.061144,-51.174659 -30.061207999999997,-51.174870999999996 -30.061239999999998,-51.175968999999995 -30.061339999999998,-51.176536 -30.061405999999998,-51.177225 -30.06147,-51.177580999999996 -30.061594,-51.177813 -30.061728,-51.178036 -30.061927999999998,-51.178656 -30.062627,-51.178719 -30.062694999999998,-51.178774999999995 -30.062753999999998,-51.179044 -30.062969,-51.179336 -30.063153,-51.179669 -30.063311,-51.180068 -30.063443999999997,-51.181819 -30.063907999999998,-51.182064999999994 -30.063975,-51.182401999999996 -30.064041999999997,-51.182573999999995 -30.063996,-51.183581 -30.06356,-51.184193 -30.063253,-51.184711 -30.062979,-51.185451 -30.062538,-51.185770999999995 -30.062368,-51.18618 -30.062177,-51.186572 -30.061968999999998,-51.187087999999996 -30.061694,-51.187518999999995 -30.06149,-51.188457 -30.061044,-51.189288999999995 -30.060637999999997,-51.189775999999995 -30.060389999999998,-51.190146 -30.060157999999998,-51.191178 -30.059548,-51.191089 -30.059434,-51.190183 -30.057806,-51.189372 -30.056348999999997,-51.188646999999996 -30.055045999999997,-51.186606999999995 -30.056046,-51.186246 -30.056224,-51.184807 -30.056936999999998,-51.18459 -30.057040999999998,-51.184126 -30.057274999999997,-51.184034999999994 -30.057078999999998,-51.183921999999995 -30.056841,-51.183825 -30.056637,-51.183647 -30.056364,-51.183634999999995 -30.056345,-51.183467 -30.056112,-51.183341999999996 -30.055905,-51.183312 -30.055854,-51.183299 -30.055822,-51.183178999999996 -30.055529999999997,-51.183094999999994 -30.055329,-51.183071 -30.055239999999998,-51.183049999999994 -30.055166,-51.182887 -30.054903999999997,-51.182708 -30.054554,-51.182572 -30.054267999999997,-51.182410999999995 -30.053929999999998,-51.182147 -30.053382,-51.181905 -30.052875999999998,-51.18172 -30.052487,-51.181376 -30.051745,-51.181103 -30.051171,-51.181028999999995 -30.051057999999998,-51.180763999999996 -30.05076,-51.180527999999995 -30.050435,-51.180175999999996 -30.049902999999997,-51.179604 -30.049091999999998,-51.178971 -30.047883,-51.178698 -30.047354,-51.178408 -30.046837999999997,-51.178179 -30.046429999999997,-51.177358 -30.044904,-51.176946 -30.044183,-51.176823 -30.044017,-51.176708 -30.043862999999998,-51.176431 -30.043595999999997,-51.176139 -30.043349,-51.175940999999995 -30.043214,-51.175520999999996 -30.042966,-51.175194999999995 -30.042804999999998,-51.174672 -30.042578,-51.174478 -30.042468999999997,-51.173821999999994 -30.041940999999998,-51.173417 -30.041352999999997,-51.173246999999996 -30.040968999999997,-51.173224999999995 -30.040342,-51.173187999999996 -30.040070999999998,-51.17346 -30.038632999999997,-51.173749 -30.037727999999998,-51.173949 -30.037105999999998,-51.17409 -30.036768,-51.174226999999995 -30.036475,-51.174343 -30.036316,-51.174636 -30.036023,-51.174808999999996 -30.035878,-51.174986 -30.035753,-51.175404 -30.035522,-51.176455999999995 -30.034983999999998,-51.1766 -30.034892,-51.176784 -30.034784,-51.176944999999996 -30.03466,-51.177234999999996 -30.034395999999997,-51.177499999999995 -30.034119999999998,-51.177924 -30.033528999999998,-51.178323 -30.032961999999998,-51.178543 -30.032684,-51.179144 -30.031882,-51.179739999999995 -30.031045,-51.179880999999995 -30.030828,-51.180020999999996 -30.030517999999997,-51.180129 -30.030268,-51.180844 -30.028606999999997,-51.181242999999995 -30.027675,-51.181773 -30.026473,-51.18268 -30.024694999999998,-51.183564 -30.022903,-51.184464 -30.02112,-51.184568 -30.020964,-51.184739 -30.020751999999998,-51.185005 -30.020424,-51.185587 -30.019647,-51.186127 -30.018907,-51.186147 -30.018839999999997,-51.186167999999995 -30.01877,-51.18633 -30.017856,-51.186454999999995 -30.016523,-51.186572999999996 -30.015269,-51.186653 -30.014464,-51.186842 -30.014048,-51.187945 -30.011823999999997,-51.188947999999996 -30.009815,-51.189448999999996 -30.008775999999997,-51.189988 -30.007699,-51.190369999999994 -30.006894,-51.190374 -30.006783,-51.190306 -30.006459999999997,-51.19061 -30.006484999999998,-51.190369999999994 -30.006894,-51.189988 -30.007699,-51.189448999999996 -30.008775999999997,-51.188947999999996 -30.009815,-51.187945 -30.011823999999997,-51.186842 -30.014048,-51.186653 -30.014464,-51.186572999999996 -30.015269,-51.186454999999995 -30.016523,-51.18633 -30.017856,-51.186167999999995 -30.01877,-51.186147 -30.018839999999997,-51.186127 -30.018907,-51.185587 -30.019647,-51.185005 -30.020424,-51.184739 -30.020751999999998,-51.184568 -30.020964,-51.184464 -30.02112,-51.183564 -30.022903,-51.18268 -30.024694999999998,-51.181773 -30.026473,-51.181242999999995 -30.027675,-51.180844 -30.028606999999997,-51.180129 -30.030268,-51.180020999999996 -30.030517999999997,-51.179880999999995 -30.030828,-51.179739999999995 -30.031045,-51.179144 -30.031882,-51.178543 -30.032684,-51.178323 -30.032961999999998,-51.177924 -30.033528999999998,-51.177499999999995 -30.034119999999998,-51.177234999999996 -30.034395999999997,-51.176944999999996 -30.03466,-51.176784 -30.034784,-51.1766 -30.034892,-51.176455999999995 -30.034983999999998,-51.175404 -30.035522,-51.174986 -30.035753,-51.174808999999996 -30.035878,-51.174636 -30.036023,-51.174343 -30.036316,-51.174226999999995 -30.036475,-51.17409 -30.036768,-51.173949 -30.037105999999998,-51.173749 -30.037727999999998,-51.17346 -30.038632999999997,-51.173187999999996 -30.040070999999998,-51.173224999999995 -30.040342,-51.173246999999996 -30.040968999999997,-51.173417 -30.041352999999997,-51.173821999999994 -30.041940999999998,-51.174478 -30.042468999999997,-51.174672 -30.042578,-51.175194999999995 -30.042804999999998,-51.175520999999996 -30.042966,-51.175940999999995 -30.043214,-51.176139 -30.043349,-51.176431 -30.043595999999997,-51.176708 -30.043862999999998,-51.176823 -30.044017,-51.176946 -30.044183,-51.177358 -30.044904,-51.178179 -30.046429999999997,-51.178408 -30.046837999999997,-51.178698 -30.047354,-51.178971 -30.047883,-51.179604 -30.049091999999998,-51.180175999999996 -30.049902999999997,-51.180527999999995 -30.050435,-51.180763999999996 -30.05076,-51.181028999999995 -30.051057999999998,-51.181103 -30.051171,-51.181376 -30.051745,-51.18172 -30.052487,-51.181905 -30.052875999999998,-51.182147 -30.053382,-51.182410999999995 -30.053929999999998,-51.182572 -30.054267999999997,-51.182708 -30.054554,-51.182887 -30.054903999999997,-51.183049999999994 -30.055166,-51.183071 -30.055239999999998,-51.183094999999994 -30.055329,-51.183178999999996 -30.055529999999997,-51.183299 -30.055822,-51.183312 -30.055854,-51.183341999999996 -30.055905,-51.183467 -30.056112,-51.183634999999995 -30.056345,-51.183647 -30.056364,-51.183825 -30.056637,-51.183921999999995 -30.056841,-51.184034999999994 -30.057078999999998,-51.184126 -30.057274999999997,-51.184325 -30.057938999999998,-51.184394 -30.058086,-51.18452 -30.058470999999997,-51.184602 -30.05888,-51.184776 -30.059851,-51.185005 -30.06121,-51.185111 -30.061767,-51.185136 -30.061826,-51.185299 -30.062248999999998,-51.185451 -30.062538,-51.184711 -30.062979,-51.184193 -30.063253,-51.183581 -30.06356,-51.182573999999995 -30.063996,-51.182401999999996 -30.064041999999997,-51.182064999999994 -30.063975,-51.181819 -30.063907999999998,-51.180068 -30.063443999999997,-51.179669 -30.063311,-51.179336 -30.063153,-51.179044 -30.062969,-51.178774999999995 -30.062753999999998,-51.178719 -30.062694999999998,-51.178656 -30.062627,-51.178036 -30.061927999999998,-51.177813 -30.061728,-51.177580999999996 -30.061594,-51.177225 -30.06147,-51.176536 -30.061405999999998,-51.175968999999995 -30.061339999999998,-51.174870999999996 -30.061239999999998,-51.174659 -30.061207999999997,-51.174298 -30.061144,-51.173369 -30.06085,-51.173153 -30.060809,-51.172943 -30.060796,-51.172608 -30.060805,-51.171763 -30.060899,-51.171462 -30.0609,-51.171141 -30.060871,-51.170393999999995 -30.060772,-51.169791 -30.060727999999997,-51.169636 -30.060719,-51.169042999999995 -30.060738999999998,-51.168726 -30.060768999999997,-51.167727 -30.060868,-51.167606 -30.060881,-51.166109999999996 -30.061064,-51.166045 -30.061073,-51.164528 -30.061283999999997,-51.164054 -30.061322999999998,-51.163036999999996 -30.061332999999998,-51.162639 -30.061356999999997,-51.162515 -30.06137,-51.161967 -30.061494999999997,-51.161674 -30.061594999999997,-51.161445 -30.061698,-51.161072 -30.061882999999998,-51.160345 -30.062255999999998,-51.159490999999996 -30.062706,-51.158570999999995 -30.063143999999998,-51.157855 -30.063416999999998,-51.157118 -30.063658999999998,-51.156859 -30.063730999999997,-51.156363999999996 -30.063872,-51.155469 -30.06411,-51.155232 -30.064173,-51.154823 -30.064287,-51.154239 -30.064389,-51.153704999999995 -30.064407,-51.153459999999995 -30.064404,-51.152643 -30.064387,-51.152159999999995 -30.064549,-51.1521 -30.064577,-51.151629 -30.064798999999997,-51.151574 -30.064836999999997,-51.151441 -30.06493,-51.150572 -30.065697,-51.150020999999995 -30.066073,-51.149753 -30.066219999999998,-51.149442 -30.066357,-51.148779999999995 -30.066592,-51.148697 -30.066391999999997,-51.14937 -30.066080999999997,-51.150006999999995 -30.065756,-51.150403999999995 -30.065105,-51.150568 -30.064866))
Como disse o exemplo acima deu certo.
Agora estou criando o meu projeto mais "elegantemente", esta é minha classe connection
package br.com.spatialteste;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class ConnectionFactory {
private static final String url = "jdbc:postgresql://localhost:5432/SpatialTeste";
private static final String driver = "org.postgresql.Driver";
private static final String usuario = "postgres";
private static final String senha = "130999";
public static Connection getConnection(){
try {
Class.forName(driver);
return DriverManager.getConnection(url, usuario, senha);
}catch (ClassNotFoundException e){
return null;
}catch (SQLException e){
return null;
}
}
}
Aqui a minha classe ônibus, estou em dúvida de que tipo usar no para o campo geom da tabela espacial.
A minha finalidade é desenhar essas linhas usando a google maps api, pensando nisso qual é a melhor forma de recuperá-las do bd como no exemplo acima
que traz a sequência de coordenadas numa string? Ou existe um tipo de objeto linha e depois é só plotar no mapa?
Sei que existe o Hibernate Spacial para fazer esse mapeamento queria fazer na mão a título de conhecimento, alguém conhece algum artigo ou tuto usando hibernate spacial com postgis pra indicar?
package br.com.spatialteste.pojos;
import org.postgis.Geometry;
public class Onibus {
public Integer id;
public Geometry geom;
public Float lenght;
public String rota;
public String codLin;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Geometry getGeom() {
return geom;
}
public void setGeom(Object object) {
this.geom = (Geometry) object;
}
public Float getLenght() {
return lenght;
}
public void setLenght(Float lenght) {
this.lenght = lenght;
}
public String getRota() {
return rota;
}
public void setRota(String rota) {
this.rota = rota;
}
public String getCodLin() {
return codLin;
}
public void setCodLin(String codLin) {
this.codLin = codLin;
}
}package br.com.spatialteste;
import br.com.spatialteste.pojos.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.postgis.PGgeometry;
import br.com.spatialteste.pojos.Onibus;
public class BuscaOnibus {
private Connection con;
public BuscaOnibus(Connection con){
this.con = con;
String sqlOnibus = ("select geom,id,rota,codLin from onibus");
try{
PreparedStatement ps = this.con.prepareCall(sqlOnibus);
ResultSet rs = ps.executeQuery();
List<Onibus> onibusList = new ArrayList<>();
while (rs.next()){
Onibus onibus = new Onibus();
onibus.setCodLin(rs.getString("codLin"));
onibus.setId(rs.getInt("id"));
onibus.setLenght(rs.getFloat("length"));
onibus.setRota(rs.getString("rota"));
onibus.setGeom(rs.getString("geom"));
}
}catch (SQLException sqle){
System.out.println("Erro de SQL ao recuperar as linhas ");
sqle.printStackTrace();
}
finally {
try
{
con.close();
} catch(SQLException onConClose){
System.out.println("Houve erro no fechamento da conexão");
onConClose.printStackTrace();
}
}
}
}
Cássia Freitas
Curtidas 0