Merge pull request #399 from navit-gps/psql

Psql

4 files changed, 108 insertions, 24 deletions

diff --git a/.circleci/config.yml b/.circleci/config.yml
index 493b9f930..3d79961be 100644
--- a/.circleci/config.yml
+++ b/.circleci/config.yml
@@ -90,6 +90,8 @@ jobs:
           command: |
             bash ci/setup_common_requirements.sh
             bash ci/build_win32.sh
+      - store_artifacts:
+          path: win32/navit.exe
   build_wince:
     docker:
       - image: navit/wince:8.04
diff --git a/config.h.cmake b/config.h.cmake
index dd27d1ee5..c759e69c1 100644
--- a/config.h.cmake
+++ b/config.h.cmake
@@ -93,3 +93,5 @@
 #cmakedefine HAVE_IMLIB2 1
 
 #cmakedefine HAS_IFADDRS 1
+
+#cmakedefine HAVE_POSTGRESQL 1
diff --git a/navit/maptool/CMakeLists.txt b/navit/maptool/CMakeLists.txt
index 0c006daee..e208eeea5 100644
--- a/navit/maptool/CMakeLists.txt
+++ b/navit/maptool/CMakeLists.txt
@@ -2,7 +2,7 @@
 if(BUILD_MAPTOOL)
    add_definitions( -DMODULE=maptool ${NAVIT_COMPILE_FLAGS})
    include_directories(${CMAKE_CURRENT_SOURCE_DIR})
-   SET(MAPTOOL_SOURCE boundaries.c buffer.c ch.c coastline.c itembin.c itembin_buffer.c misc.c osm.c osm_o5m.c osm_relations.c sourcesink.c tempfile.c tile.c zip.c osm_xml.c)
+   SET(MAPTOOL_SOURCE boundaries.c buffer.c ch.c coastline.c itembin.c itembin_buffer.c misc.c osm.c osm_o5m.c osm_psql.c osm_relations.c sourcesink.c tempfile.c tile.c zip.c osm_xml.c)
    if(NOT MSVC)
 	SET(MAPTOOL_SOURCE ${MAPTOOL_SOURCE} osm_protobuf.c osm_protobufdb.c generated-code/fileformat.pb-c.c generated-code/osmformat.pb-c.c google/protobuf-c/protobuf-c.c)
    endif(NOT MSVC)
diff --git a/navit/maptool/osm_psql.c b/navit/maptool/osm_psql.c
index 53ba780bc..8e8ded6c5 100644
--- a/navit/maptool/osm_psql.c
+++ b/navit/maptool/osm_psql.c
@@ -25,15 +25,13 @@
 #include "linguistics.h"
 #include "file.h"
 #ifdef HAVE_POSTGRESQL
-#include <libpq-fe.h>
+#include <postgresql/libpq-fe.h>
 
 int
 map_collect_data_osm_db(char *dbstr, struct maptool_osm *osm)
 {
 	PGconn *conn;
-	PGresult *res,*node,*way,*tag;
-	int count,tagged,i,j,k;
-	long min, max, id, tag_id, node_id;
+	PGresult *res;
 	char query[256];
 	
 	sig_alrm(0);
@@ -42,6 +40,7 @@ map_collect_data_osm_db(char *dbstr, struct maptool_osm *osm)
 		fprintf(stderr,"Failed to connect to database with '%s'\n",dbstr);
 		exit(1);
 	}
+	fprintf(stderr,"connected to database with '%s'\n",dbstr);
 	res=PQexec(conn, "begin");
 	if (! res) {
 		fprintf(stderr, "Cannot begin transaction: %s\n", PQerrorMessage(conn));
@@ -54,53 +53,56 @@ map_collect_data_osm_db(char *dbstr, struct maptool_osm *osm)
 		PQclear(res);
 		exit(1);
 	}
-	res=PQexec(conn, "declare node cursor for select id,x(coordinate),y(coordinate) from node order by id");
+	res=PQexec(conn, "declare nodes cursor for select id, ST_Y(geom), ST_X(geom) from nodes order by id");
 	if (! res) {
 		fprintf(stderr, "Cannot setup cursor for nodes: %s\n", PQerrorMessage(conn));
 		PQclear(res);
 		exit(1);
 	}
-	res=PQexec(conn, "declare way cursor for select id from way order by id");
+	res=PQexec(conn, "declare ways cursor for select id from ways order by id");
 	if (! res) {
-		fprintf(stderr, "Cannot setup cursor for nodes: %s\n", PQerrorMessage(conn));
+		fprintf(stderr, "Cannot setup cursor for ways: %s\n", PQerrorMessage(conn));
 		PQclear(res);
 		exit(1);
 	}
-	res=PQexec(conn, "declare relation cursor for select id from relation order by id");
+	res=PQexec(conn, "declare relations cursor for select id from relations order by id");
 	if (! res) {
-		fprintf(stderr, "Cannot setup cursor for nodes: %s\n", PQerrorMessage(conn));
+		fprintf(stderr, "Cannot setup cursor for relations: %s\n", PQerrorMessage(conn));
 		PQclear(res);
 		exit(1);
 	}
+	
 	for (;;) {
-		node=PQexec(conn, "fetch 100000 from node");
+		int j=0, count=0;
+		long min, max, id, tag_id;
+		PGresult *node, *tag;
+		node=PQexec(conn, "fetch 100000 from nodes");
 		if (! node) {
 			fprintf(stderr, "Cannot setup cursor for nodes: %s\n", PQerrorMessage(conn));
 			PQclear(node);
 			exit(1);
 		}
 		count=PQntuples(node);
+		fprintf(stderr, "fetch got %i nodes\n", count);
 		if (! count)
 			break;
 		min=atol(PQgetvalue(node, 0, 0));
 		max=atol(PQgetvalue(node, count-1, 0));
-		sprintf(query,"select node_id,name,value from node_tag where node_id >= %ld and node_id <= %ld order by node_id", min, max);
+		sprintf(query,"select node_id,k,v from node_tags where node_id >= %ld and node_id <= %ld order by node_id", min, max);
 		tag=PQexec(conn, query);
 		if (! tag) {
 			fprintf(stderr, "Cannot query node_tag: %s\n", PQerrorMessage(conn));
 			exit(1);
 		}
-		j=0;
-		for (i = 0 ; i < count ; i++) {
+		fprintf(stderr, "query node_tag got : %i tuples\n", PQntuples(tag));
+		for (int i = 0 ; i < count ; i++) {
 			id=atol(PQgetvalue(node, i, 0));
 			osm_add_node(id, atof(PQgetvalue(node, i, 1)), atof(PQgetvalue(node, i, 2)));
-			tagged=0;
 			processed_nodes++;
 			while (j < PQntuples(tag)) {
 				tag_id=atol(PQgetvalue(tag, j, 0));
 				if (tag_id == id) {
 					osm_add_tag(PQgetvalue(tag, j, 1), PQgetvalue(tag, j, 2));
-					tagged=1;
 					j++;
 				}
 				if (tag_id < id)
@@ -113,33 +115,37 @@ map_collect_data_osm_db(char *dbstr, struct maptool_osm *osm)
 		PQclear(tag);
 		PQclear(node);
 	}
+	
 	for (;;) {
-		way=PQexec(conn, "fetch 100000 from way");
+		int j=0, k=0, count=0, tagged=0;
+		long min, max, id, tag_id, node_id;
+		PGresult *node,*way,*tag;
+		way=PQexec(conn, "fetch 25000 from ways");
 		if (! way) {
 			fprintf(stderr, "Cannot setup cursor for ways: %s\n", PQerrorMessage(conn));
-			PQclear(node);
+			PQclear(way);
 			exit(1);
 		}
 		count=PQntuples(way);
+		fprintf(stderr, "fetch got %i ways\n", count);
 		if (! count)
 			break;
 		min=atol(PQgetvalue(way, 0, 0));
 		max=atol(PQgetvalue(way, count-1, 0));
-		sprintf(query,"select way_id,node_id from way_node where way_id >= %ld and way_id <= %ld order by way_id,sequence_id", min, max);
+		fprintf(stderr, "continue with %i ways\n", count);
+		sprintf(query,"select way_id,node_id from way_nodes where way_id >= %ld and way_id <= %ld order by way_id,sequence_id", min, max);
 		node=PQexec(conn, query);
 		if (! node) {
 			fprintf(stderr, "Cannot query way_node: %s\n", PQerrorMessage(conn));
 			exit(1);
 		}
-		sprintf(query,"select way_id,name,value from way_tag where way_id >= %ld and way_id <= %ld order by way_id", min, max);
+		sprintf(query,"select way_id,k,v from way_tags where way_id >= %ld and way_id <= %ld order by way_id", min, max);
 		tag=PQexec(conn, query);
 		if (! tag) {
 			fprintf(stderr, "Cannot query way_tag: %s\n", PQerrorMessage(conn));
 			exit(1);
 		}
-		j=0;
-		k=0;
-		for (i = 0 ; i < count ; i++) {
+		for (int i = 0 ; i < count ; i++) {
 			id=atol(PQgetvalue(way, i, 0));
 			osm_add_way(id);
 			tagged=0;
@@ -175,7 +181,81 @@ map_collect_data_osm_db(char *dbstr, struct maptool_osm *osm)
 		PQclear(node);
 		PQclear(way);
 	}
-
+	
+	for (;;) {
+		int j=0, k=0, count=0, tagged=0;
+		long min, max, id;
+		PGresult *tag, *relation, *member;
+		relation=PQexec(conn, "fetch 40000 from relations");
+		if (! relation) {
+			fprintf(stderr, "Cannot setup cursor for relations: %s\n", PQerrorMessage(conn));
+			PQclear(relation);
+			exit(1);
+		}
+		count=PQntuples(relation);
+		fprintf(stderr, "Got %i relations\n", count);
+		if (! count)
+			break;
+		min=atol(PQgetvalue(relation, 0, 0));
+		max=atol(PQgetvalue(relation, count-1, 0));
+		sprintf(query,"select relation_id,k,v from relation_tags where relation_id >= %ld and relation_id <= %ld order by relation_id", min, max);
+		tag=PQexec(conn, query);
+		if (! tag) {
+			fprintf(stderr, "Cannot query relation_tag: %s\n", PQerrorMessage(conn));
+			exit(1);
+		}
+		sprintf(query,"select relation_id, member_id, member_type, member_role from relation_members where relation_id >= %ld and relation_id <= %ld order by relation_id, sequence_id", min, max);
+		member=PQexec(conn, query);
+		if (! member) {
+			fprintf(stderr, "Cannot query relation_members: %s\n", PQerrorMessage(conn));
+			exit(1);
+		}
+		for (int i = 0 ; i < count ; i++) {
+			id=atol(PQgetvalue(relation, i, 0));
+			osm_add_relation(id);
+			tagged = 0;
+			while (j < PQntuples(tag)) {
+				long tag_relation_id=atol(PQgetvalue(tag, j, 0));
+				if (tag_relation_id == id) {
+					osm_add_tag(PQgetvalue(tag, j, 1), PQgetvalue(tag, j, 2));
+					tagged=1;
+					j++;
+				}
+				if (tag_relation_id < id)
+					j++;
+				if (tag_relation_id > id)
+					break;
+			}
+			while (k < PQntuples(member)) {
+				long member_relation_id=atol(PQgetvalue(member, k, 0));
+				if (member_relation_id == id) {
+					int relmember_type=0; //type unknown
+					if (!strcmp(PQgetvalue(member,k, 2),"W")){
+						relmember_type=2;
+						}else{
+								if (!strcmp(PQgetvalue(member,k, 2),"N")){
+								relmember_type=1;
+								}else{
+									if (!strcmp(PQgetvalue(member,k, 2),"R")){
+										relmember_type=3;
+										}
+									}
+							}
+					osm_add_member(relmember_type,atoll(PQgetvalue(member,k, 1)),PQgetvalue(member,k, 3));
+					k++;
+				}
+				if (member_relation_id < id)
+					k++;
+				if (member_relation_id > id)
+					break;
+			}
+			if (tagged)
+				osm_end_relation(osm);
+		}
+		PQclear(relation);
+		PQclear(member);
+		PQclear(tag);
+	}
 	res=PQexec(conn, "commit");
 	if (! res) {
 		fprintf(stderr, "Cannot commit transaction: %s\n", PQerrorMessage(conn));