Ticket #208: Export2DB.cpp

File Export2DB.cpp, 7.2 KB (added by calinm, 5 months ago)

Fixed file

Line 
1/***************************************************************************
2 *   Copyright (C) 2008 by Daniel Wendt                                                                    *
3 *   gentoo.murray@gmail.com                                                                                       *
4 *                                                                         *
5 *   This program is free software; you can redistribute it and/or modify  *
6 *   it under the terms of the GNU General Public License as published by  *
7 *   the Free Software Foundation; either version 2 of the License, or     *
8 *   (at your option) any later version.                                   *
9 *                                                                         *
10 *   This program is distributed in the hope that it will be useful,       *
11 *   but WITHOUT ANY WARRANTY; without even the implied warranty of        *
12 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
13 *   GNU General Public License for more details.                          *
14 *                                                                         *
15 *   You should have received a copy of the GNU General Public License     *
16 *   along with this program; if not, write to the                         *
17 *   Free Software Foundation, Inc.,                                       *
18 *   59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.             *
19 ***************************************************************************/
20
21#include "stdafx.h"
22#include "Export2DB.h"
23
24using namespace std;
25
26Export2DB::Export2DB(std::string host, std::string user, std::string dbname, std::string port, std::string passwd)
27:mycon(0)
28{
29       
30this->conninf="host="+host+" user="+user+" dbname="+ dbname +" port="+port;
31if(!passwd.empty())
32        this->conninf+=" password="+passwd;
33       
34}
35
36Export2DB::~Export2DB()
37{
38        PQfinish(mycon);
39}
40
41int Export2DB::connect()
42{
43        cout << conninf<< endl;
44        //mycon =PQconnectdb("user=postgres dbname=template1 hostaddr=127.0.0.1 port=5432");
45        mycon =PQconnectdb(conninf.c_str());
46       
47        ConnStatusType type =PQstatus(mycon);
48                if(type==CONNECTION_BAD)
49                {
50                        cout << "connection failed"<< endl;
51                        return 1;
52                }
53                else
54                {
55                        cout << "connection success"<< endl;
56                        return 0;
57                }
58/***
59      CONNECTION_STARTED: Waiting for connection to be made.
60      CONNECTION_MADE: Connection OK; waiting to send.
61      CONNECTION_AWAITING_RESPONSE: Waiting for a response from the postmaster.
62      CONNECTION_AUTH_OK: Received authentication; waiting for backend start-up.
63          CONNECTION_SETENV: Negotiating environment.
64***/
65
66}
67
68
69void Export2DB::createTables()
70{
71        PGresult *result = PQexec(mycon, "CREATE TABLE nodes (ID integer PRIMARY KEY,  lon decimal(11,8), lat decimal(11,8), numOfUse smallint);");
72        if (PQresultStatus(result) != PGRES_COMMAND_OK)
73        {
74                std::cerr << "create Nodes failed: "
75                        << PQerrorMessage(mycon)
76                        << std::endl;
77                PQclear(result);
78        }
79
80        std::cout << "Nodes table created" << std::endl;
81        result = PQexec(mycon, "CREATE TABLE ways (gid integer, class_id integer not null, length double precision, name char(200), x1 double precision, y1 double precision, x2 double precision,y2 double precision, reverse_cost double precision,rule text, to_cost double precision, PRIMARY KEY(gid)); SELECT AddGeometryColumn('ways','the_geom',4326,'MULTILINESTRING',2);");
82        if (PQresultStatus(result) != PGRES_COMMAND_OK)
83        {
84                std::cerr << "create ways failed: "
85                        << PQerrorMessage(mycon)
86                        << std::endl;
87                PQclear(result);
88        } else {
89                std::cout << "Ways table created" << std::endl;
90        }
91        result = PQexec(mycon, "CREATE TABLE types (id integer, name char(200));");
92        if (PQresultStatus(result) != PGRES_COMMAND_OK)
93        {
94                std::cerr << "create types failed: "
95                        << PQerrorMessage(mycon)
96                        << std::endl;
97                PQclear(result);
98        } else {
99                std::cout << "Types table created" << std::endl;
100        }
101        result = PQexec(mycon, "CREATE TABLE classes (id integer, type_id integer, name char(200), cost double precision);");
102        if (PQresultStatus(result) != PGRES_COMMAND_OK)
103        {
104                std::cerr << "create classes failed: "
105                        << PQerrorMessage(mycon)
106                        << std::endl;
107                PQclear(result);
108        } else {
109                std::cout << "Classes table created" << std::endl;     
110        }
111}
112
113void Export2DB::dropTables()
114{
115        PGresult *result = PQexec(mycon, "DROP TABLE ways; DROP TABLE nodes; DROP TABLE types; DROP TABLE classes;");
116}
117
118void Export2DB::exportNode(long long id, double lon, double lat, ushort numOfUse )
119{
120        char tmp_id[20];
121        char tmp_lon[15];
122        char tmp_lat[15];
123       
124        sprintf(tmp_id,"%lld",id);
125        gcvt(lon,12,tmp_lon);
126        gcvt(lat,12,tmp_lat);
127       
128        std::string query = "INSERT into nodes(id,lon,lat) values(";
129                                query+= tmp_id;
130                                query+=",";
131                                query+= tmp_lon;
132                                query+=",";
133                                query+= tmp_lat;
134                                query+=");";
135       
136        PGresult *result = PQexec(mycon, query.c_str());
137}
138
139void Export2DB::exportWay(Way* way)
140{
141        std::string query = "INSERT into ways(gid, class_id, length, x1, y1, x2, y2, the_geom, reverse_cost";
142        if(!way->name.empty())
143                query+=", name";
144        query+=") values(";
145       
146        query+=boost::lexical_cast<std::string>(way->id) +
147                ", (SELECT id FROM classes WHERE name ='" + boost::lexical_cast<std::string>(way->clss) + "' and type_id = (select id from types where name='"+ boost::lexical_cast<std::string>(way->type) + "')),"
148                + boost::lexical_cast<std::string>(way->length) + ","
149                 + boost::lexical_cast<std::string>(way->m_NodeRefs.front()->lon) + ","+ boost::lexical_cast<std::string>(way->m_NodeRefs.front()->lat) + ","
150                 + boost::lexical_cast<std::string>(way->m_NodeRefs.back()->lon)  + ","+ boost::lexical_cast<std::string>(way->m_NodeRefs.back()->lat) + ",";
151        query+="GeometryFromText('" + way->geom +"', 4326)";
152
153        if(way->oneway)
154        {
155            query+=", "+ boost::lexical_cast<std::string>(way->length*1000000);
156        }
157        else
158        {
159            query+=", "+ boost::lexical_cast<std::string>(way->length);
160        }       
161
162        if(!way->name.empty())
163                query+=",$$"+ way->name +"$$";
164        query+=");";
165                //std::cout << query <<std::endl;
166        PGresult *result = PQexec(mycon, query.c_str());
167        if (PQresultStatus(result) != PGRES_COMMAND_OK)
168        {
169                std::cerr << "create Nodes failed: "
170                        << PQerrorMessage(mycon)
171                        << std::endl;
172                std::cerr << "SQL:" << std::endl << query << std::endl;
173                PQclear(result);
174        }
175}
176
177void Export2DB::exportType(Type* type)
178{
179        std::string query = "INSERT into types(id, name) values(";
180       
181        query+=boost::lexical_cast<std::string>(type->id) + ", '" + type->name +"');";
182        PGresult *result = PQexec(mycon, query.c_str());
183}
184
185void Export2DB::exportClass(Type* type, Class* clss)
186{
187        std::string query = "INSERT into classes(id, type_id, name) values(";
188       
189        query+=boost::lexical_cast<std::string>(clss->id) + ", " + boost::lexical_cast<std::string>(type->id) + ", '" + clss->name +"');";
190        PGresult *result = PQexec(mycon, query.c_str());
191}
192
193void Export2DB::createTopology()
194{
195        PGresult *result = PQexec(mycon,"ALTER TABLE ways ADD COLUMN source integer;");
196        result = PQexec(mycon,"ALTER TABLE ways ADD COLUMN target integer;");
197        result = PQexec(mycon,"CREATE INDEX source_idx ON ways(source);");
198        result = PQexec(mycon,"CREATE INDEX target_idx ON ways(target);");
199        result = PQexec(mycon,"CREATE INDEX geom_idx ON ways USING GIST(the_geom GIST_GEOMETRY_OPS);");
200        result = PQexec(mycon,"SELECT assign_vertex_id('ways', 0.00001, 'the_geom', 'gid');");
201       
202}