From 68af595796879bbf060b5aa349f87e3af2135d11 Mon Sep 17 00:00:00 2001 From: Philipp Borgers Date: Fri, 14 Jan 2011 16:48:57 +0100 Subject: added database setup howto to wREADME --- README | 58 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++- 1 file changed, 57 insertions(+), 1 deletion(-) diff --git a/README b/README index 8d10021..a11a09b 100644 --- a/README +++ b/README @@ -26,7 +26,63 @@ a more formal description We serve the node endpoint, but no others. We implment the tag based filtering, for only one tag and a bounding box. -database shema +Dabase setup +============= + +We use postgres 9.* with postgis 1.5. Postgres 9.0 is used becaus we need some +functions on hstores and postgis 1.5 is used for fancy geometry things. + +To setup a database you first need a runing postgres 9.0 instance. I hope you know +how to do this. + +Use + + createdb osm + +to create a database with name osm. Use + + createlang plpgsql osm + +to add plpgsql to your database. If you want to create a database user do so: + + createuser + +Add hstore support for your database: + + psql -d osm < /share/contrib/hstore.sql + +Add postgis support for your database: + + psql -d osm < /share/contrib/postgis-1.5/postgis.sql + +Just do: + + psql -d osm < /share/contrib/postgis-1.5/spatial_ref_sys.sql + +Create the actual schema for osm data. For this step you need osmosis. If your +osmosis build is correct there should be a directory named pacakge/scripts where +you run: + + psql -d osm < pgsql_simple_schema_0.6.sql + psql -d osm < pgsql_simple_schema_0.6_linestring.sql +Optional/stil to test: + + psql -d osm < pgsql_simple_schema_0.6_action.sql + psql -d osm < pgsql_simple_schema_0.6_bbox.sql + +For documentation on the schema read pgsql_simple.txt. + +Import data from xml with: + + osmosis --read-xml file="planet.osm.bz2" --wp host="localhost" password="TopSecretPassword" user="insertUserNameHere" database="osm" + +Before or after import you may want to create a index or two. Examples below: + CREATE INDEX idx_nodes_tags ON nodes USING GIN(tags); + CREATE INDEX idx_nodes_tags ON nodes USING GIST(tags); + CREATE INDEX idx_ways_tags ON ways USING GIN(tags); + CREATE INDEX idx_ways_tags ON ways USING GIST(tags); + +database shema (out dated) ================ We keep data in a highly specioaliced shema, that allows us to prevent tables -- cgit v1.2.3 From 0857764cec6cc564a6ba24bc81a358429c447fcb Mon Sep 17 00:00:00 2001 From: Philipp Borgers Date: Fri, 14 Jan 2011 21:09:32 +0100 Subject: added pg request for ways with key, value and bbox, some async improvements, write something like xml to the response, still to fix content encoding (utf8) and timestamp encoding --- src/nodejs/no1.js | 170 ++++++++++++++++++++++++++++++++++++------------------ 1 file changed, 113 insertions(+), 57 deletions(-) diff --git a/src/nodejs/no1.js b/src/nodejs/no1.js index 8eb86d7..ab903fe 100644 --- a/src/nodejs/no1.js +++ b/src/nodejs/no1.js @@ -5,18 +5,21 @@ var builder = require('xmlbuilder') var connectionString = "pg://user:password@host/database"; +function createDateString(date) { + + //2006-09-11T16:28:25+01:00 time format + date = Date(date); + return date.getUTCFullYear() + "-" + date.getUTCDate() + "-" + date.getUTCDate() + "T"; +} + +function createWayBboxQuery(key, value, left, bottom, right, top) { + return "SELECT id,tstamp,version,changeset_id, nodes, user_id, hstore_to_array(tags) as tags FROM ways WHERE (tags @> hstore('" + key + "','" + value + "') AND linestring && st_setsrid(st_makebox2d(st_setsrid(st_makepoint(" + + left + "," + bottom + "),4326), st_setsrid(st_makepoint(" + right + "," + top + "),4326)),4326));"; +} function createNodeBboxQuery(key, value, left, bottom, right, top) { - /* - * return "SELECT * from nodes WHERE (tags @> '\"" + key - + "\"=>\"" + value + "\"'" + - " AND POINT(geom) @ polygon(box('(" + left - + "," + bottom +")'::point,'(" + - + right + "," + top + ")'::point)));"; - */ - - return "SELECT id,tstamp,version,changeset_id, X(geom) as lat, Y(geom) as lon FROM nodes WHERE (tags @> hstore('" + key + "','" + value + "') AND geom && st_setsrid(st_makebox2d(st_setsrid(st_makepoint(" + - left + "," + bottom + "),4326), st_setsrid(st_makepoint(" + right + "," + top + "),4326)),4326));"; + return "SELECT id, user_id,tstamp,version,changeset_id, hstore_to_array(tags) as tags, X(geom) as lat, Y(geom) as lon FROM nodes WHERE (tags @> hstore('" + key + "','" + value + "') AND geom && st_setsrid(st_makebox2d(st_setsrid(st_makepoint(" + + left + "," + bottom + "),4326), st_setsrid(st_makepoint(" + right + "," + top + "),4326)),4326));"; } @@ -35,57 +38,61 @@ function nodeBboxHandler(req, res, key, value, left, bottom, right, top) { res.end('\n'); } else { - console.log(createNodeBboxQuery(key, value, left, bottom, right, top)); - client.query(createNodeBboxQuery(key, value, left, bottom, right, top), function(err,result) { + //console.log(createNodeBboxQuery(key, value, left, bottom, right, top)); + var success = false; + var query = client.query(createNodeBboxQuery(key, value, left, bottom, right, top)); + + query.on('error', function(err) { - if (err) { - console.log(err); - res.writeHead(404,{}); - res.end('\n'); - } - else { - console.log(result.rows); - - res.writeHead(200, {'Content-Type': 'text/plain'}); - //res.write("lala"); - res.write(""); - for(var i=0; i"); - res.write(""); - //console.log(result.rows[i].id); - */ - var node = builder.begin('node') - .att('id', result.rows[i].id) - .att('timetamp', result.rows[i].tstamp) - .att('version', result.rows[i].version) - .att('changeset', result.rows[i].changeset_id) - .att('lat', result.rows[i].lat) - .att('lon', result.rows[i].lon); - - - - res.write(builder.toString()); - - } + console.log(err); + res.writeHead(404,{}); + res.end('\n'); + }); + + query.on('end', function() { + if(success) { res.write(""); res.end(); } + else { + //perhaps write 404? is error also raised? + } }); - } - - + query.on('row', function(row) { + + if(!success) { + success = true; + res.writeHead(200, {'Content-Type': 'text/plain'}); + res.write(""); + } + + console.log(row); + + var node = builder.begin('node') + .att('id', row.id) + .att('timetamp', row.tstamp) + .att('version', row.version) + .att('changeset', row.changeset_id) + .att('lat', row.lat) + .att('lon', row.lon); + var temp = row.tags.replace("{","").replace("}","").split(","); + for(var x=0;x"); + res.end(); + } + else { + res.end(); + //perhaps write 404? + } + }); + + query.on('row', function(row) { + if(!success) { + success = true; + res.writeHead(200, {'Content-Type': 'text/plain'}); + res.write(""); + } + console.log(row); + + builder.begin('way') + .att('id', row.id) + .att('timetamp', row.tstamp) + .att('version', row.version) + .att('changeset', row.changeset_id); + + res.write(builder.toString()); + }); + + } + + }); + } function relationWorldHandler(req, res, key, value) { @@ -112,9 +168,9 @@ myRoutes = clutch.route404([ ['GET /api/node\\[(\\w+)=(\\w+)\\]\\[bbox=(\\d+(?:\\.\\d+)?),(\\d+(?:\\.\\d+)?),(\\d+(?:\\.\\d+)?),(\\d+(?:\\.\\d+)?)\\]$',nodeBboxHandler], //['GET /api/node\\[(\\w+)=(\\w+)\\]\\[bbox=(\\d+\\.\\d+),(\\d+),(\\d+),(\\d+)\\]$',nodeBboxHandler], ['GET /api/way\\[(\\w+)=(\\w+)\\]$',wayWorldHandler], - ['GET /api/way\\[(\\w+)=(\\w+)\\]\\[bbox=(\\d),(\\d),(\\d),(\\d)\\]$',wayBboxHandler], + ['GET /api/way\\[(\\w+)=(\\w+)\\]\\[bbox=(\\d+(?:\\.\\d+)?),(\\d+(?:\\.\\d+)?),(\\d+(?:\\.\\d+)?),(\\d+(?:\\.\\d+)?)\\]$',wayBboxHandler], ['GET /api/relation\\[(\\w+)=(\\w+)\\]$',relationWorldHandler], - ['GET /api/relation\\[(\\w+)=(\\w+)\\](\\[bbox=(\\d),(\\d),(\\d),(\\d)\\])$',relationBboxHandler], + //['GET /api/relation\\[(\\w+)=(\\w+)\\](\\[bbox=(\\d),(\\d),(\\d),(\\d)\\])$',relationBboxHandler], ]); -- cgit v1.2.3 From 647032e7aa10ce75483bfc80b0857fcafe7b7da8 Mon Sep 17 00:00:00 2001 From: Philipp Borgers Date: Sat, 15 Jan 2011 18:38:02 +0100 Subject: broken code for way requests, subquery problem... --- src/nodejs/no1.js | 47 ++++++++++++++++++++++++++++++++++++++++++----- 1 file changed, 42 insertions(+), 5 deletions(-) diff --git a/src/nodejs/no1.js b/src/nodejs/no1.js index ab903fe..a5a33be 100644 --- a/src/nodejs/no1.js +++ b/src/nodejs/no1.js @@ -22,6 +22,10 @@ function createNodeBboxQuery(key, value, left, bottom, right, top) { left + "," + bottom + "),4326), st_setsrid(st_makepoint(" + right + "," + top + "),4326)),4326));"; } +function createNodesForWayQuery(nodes) { + return "SELECT id, tstamp, version, changeset_id, hstore_to_array(tags) as tags, X(geom) as lat, Y(geom) as lon FROM nodes WHERE id = ANY('" + nodes + "');"; +} + function nodeWorldHandler(req, res, key, value) { @@ -109,7 +113,7 @@ function wayBboxHandler(req, res, key, value, left, bottom, right, top) { } else { var success = false; - console.log(createWayBboxQuery(key, value, left, bottom, right, top)); + //console.log(createWayBboxQuery(key, value, left, bottom, right, top)); var query = client.query(createWayBboxQuery(key, value, left, bottom, right, top)); query.on('error', function(err) { @@ -121,7 +125,7 @@ function wayBboxHandler(req, res, key, value, left, bottom, right, top) { query.on('end', function() { if(success) { res.write(""); - res.end(); + res.end(); //problem!!! } else { res.end(); @@ -135,15 +139,48 @@ function wayBboxHandler(req, res, key, value, left, bottom, right, top) { res.writeHead(200, {'Content-Type': 'text/plain'}); res.write(""); } - console.log(row); + //console.log(row); + if(row.nodes != '{}') { + + var subquery = client.query(createNodesForWayQuery(row.nodes)); + subquery.on('error',function(err) {}); + subquery.on('row', function(row) { + console.log(row); + var node = builder.begin('node') + .att('id', row.id) + .att('timetamp', row.tstamp) + .att('version', row.version) + .att('changeset', row.changeset_id) + .att('lat', row.lat) + .att('lon', row.lon); + var temp = row.tags.replace("{","").replace("}","").split(","); + for(var x=0;x Date: Sun, 16 Jan 2011 02:33:19 +0100 Subject: "fixed" timestamp format; added strange flowcontrol for subqueries; introduced heavy code duplication :) => reorganize code --- src/nodejs/no1.js | 97 ++++++++++++++++++++++++++++++++++++++----------------- 1 file changed, 67 insertions(+), 30 deletions(-) diff --git a/src/nodejs/no1.js b/src/nodejs/no1.js index a5a33be..6c6809e 100644 --- a/src/nodejs/no1.js +++ b/src/nodejs/no1.js @@ -5,13 +5,29 @@ var builder = require('xmlbuilder') var connectionString = "pg://user:password@host/database"; -function createDateString(date) { - - //2006-09-11T16:28:25+01:00 time format - date = Date(date); - return date.getUTCFullYear() + "-" + date.getUTCDate() + "-" + date.getUTCDate() + "T"; +function toISO8601(date) { + //2007-03-31T00:09:22+01:00 + var pad_two = function(n) { + return (n < 10 ? '0' : '') + n; + }; + var pad_three = function(n) { + return (n < 100 ? '0' : '') + (n < 10 ? '0' : '') + n; + }; + return [ + date.getUTCFullYear(), + '-', + pad_two(date.getUTCMonth() + 1), + '-', + pad_two(date.getUTCDate()), + 'T', + pad_two(date.getUTCHours()), + ':', + pad_two(date.getUTCMinutes()), + ':', + pad_two(date.getUTCSeconds()), + '+01:00' //FIX ME + ].join(''); } - function createWayBboxQuery(key, value, left, bottom, right, top) { return "SELECT id,tstamp,version,changeset_id, nodes, user_id, hstore_to_array(tags) as tags FROM ways WHERE (tags @> hstore('" + key + "','" + value + "') AND linestring && st_setsrid(st_makebox2d(st_setsrid(st_makepoint(" + left + "," + bottom + "),4326), st_setsrid(st_makepoint(" + right + "," + top + "),4326)),4326));"; @@ -54,11 +70,15 @@ function nodeBboxHandler(req, res, key, value, left, bottom, right, top) { }); query.on('end', function() { + //console.log("end event\n"); if(success) { res.write(""); res.end(); } else { + //empty response + res.writeHead(404,{}); + res.end(); //perhaps write 404? is error also raised? } }); @@ -75,17 +95,18 @@ function nodeBboxHandler(req, res, key, value, left, bottom, right, top) { var node = builder.begin('node') .att('id', row.id) - .att('timetamp', row.tstamp) + .att('timetamp', toISO8601(row.tstamp)) .att('version', row.version) .att('changeset', row.changeset_id) .att('lat', row.lat) .att('lon', row.lon); - var temp = row.tags.replace("{","").replace("}","").split(","); - for(var x=0;x"); - res.end(); //problem!!! + if(count == 0) { + res.write(""); + res.end(); + } + //res.write(""); + //res.end(); //problem!!! } else { + res.writeHead(404,{}); res.end(); //perhaps write 404? } @@ -141,40 +168,50 @@ function wayBboxHandler(req, res, key, value, left, bottom, right, top) { } //console.log(row); if(row.nodes != '{}') { - + count++; var subquery = client.query(createNodesForWayQuery(row.nodes)); subquery.on('error',function(err) {}); + subquery.on('end', function() { + count--; + if(count==0) + res.write(""); + res.end(); + }); subquery.on('row', function(row) { console.log(row); var node = builder.begin('node') .att('id', row.id) - .att('timetamp', row.tstamp) + .att('timetamp', toISO8601(row.tstamp)) .att('version', row.version) .att('changeset', row.changeset_id) .att('lat', row.lat) .att('lon', row.lon); - var temp = row.tags.replace("{","").replace("}","").split(","); - for(var x=0;x Date: Wed, 19 Jan 2011 00:10:43 +0100 Subject: first version of bootstrap skript --- bootstrap.sh | 29 +++++++++++++++++++++++++++++ 1 file changed, 29 insertions(+) create mode 100755 bootstrap.sh diff --git a/bootstrap.sh b/bootstrap.sh new file mode 100755 index 0000000..593e3d8 --- /dev/null +++ b/bootstrap.sh @@ -0,0 +1,29 @@ +#! /bin/sh + +NODE_VERSION=0.3.5 + +## Download Helper +# $1 - url +# $2 - tarball name +download() { + echo $1 + mkdir -p usr/src + + if [ ! -f ${2} ]; then + curl ${1} > ${2} + fi + + tar -xf ${2} -C usr/src +} + +# fetch and compile nodejs +download http://nodejs.org/dist/node-v${NODE_VERSION}.tar.gz node-${NODE_VERSION}.tar.gz +rm -f node-${NODE_VERSION}.tar.gz + +cd usr/src/node-v${NODE_VERSION} +./configure --prefix=../.. +make install +cd ../../.. + +# fetch and compile npm +curl http://npmjs.org/install.sh | PATH=`pwd`/usr/bin:${PATH} sh -- cgit v1.2.3 From 7a79aa613513a53dce807c881c2bca38a7baec31 Mon Sep 17 00:00:00 2001 From: slomo Date: Wed, 19 Jan 2011 00:25:10 +0100 Subject: added load for our package --- bootstrap.sh | 3 +++ 1 file changed, 3 insertions(+) diff --git a/bootstrap.sh b/bootstrap.sh index 593e3d8..02d71df 100755 --- a/bootstrap.sh +++ b/bootstrap.sh @@ -27,3 +27,6 @@ cd ../../.. # fetch and compile npm curl http://npmjs.org/install.sh | PATH=`pwd`/usr/bin:${PATH} sh + +# load development code and compile dependencies +usr/bin/npm link src/nodejs/ -- cgit v1.2.3 From 9088690fef029279190166bdd14abd1a2d0d3b84 Mon Sep 17 00:00:00 2001 From: slomo Date: Wed, 19 Jan 2011 00:26:54 +0100 Subject: added gitignore --- .gitignore | 1 + 1 file changed, 1 insertion(+) create mode 100644 .gitignore diff --git a/.gitignore b/.gitignore new file mode 100644 index 0000000..1b91047 --- /dev/null +++ b/.gitignore @@ -0,0 +1 @@ +usr/ -- cgit v1.2.3 From 048f77c545ea1c604a5da812fb1cfee6851054f5 Mon Sep 17 00:00:00 2001 From: slomo Date: Wed, 19 Jan 2011 00:27:15 +0100 Subject: added npm config --- src/nodejs/package.json | 11 +++++++++++ 1 file changed, 11 insertions(+) create mode 100644 src/nodejs/package.json diff --git a/src/nodejs/package.json b/src/nodejs/package.json new file mode 100644 index 0000000..88fc755 --- /dev/null +++ b/src/nodejs/package.json @@ -0,0 +1,11 @@ +{ "name" : "xapi-osm-spline" +, "version" : "0.0.1" +, "description" : "An osm xapi implementation" +, "autor" : "osm@spline (http://osm.spline.de)" +, "files" : ["."] +, "dependencies" : + { "postgres" : "*" + , "clutch" : "*" + , "xmlbuilder" : "*" + } +} -- cgit v1.2.3 From 7da9576c8b801e7a1b0d854d576069181f76e34f Mon Sep 17 00:00:00 2001 From: "steve.harrison@gmx.net" Date: Wed, 19 Jan 2011 16:04:59 +0100 Subject: fixed dependencies --- src/nodejs/package.json | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/nodejs/package.json b/src/nodejs/package.json index 88fc755..cbe341f 100644 --- a/src/nodejs/package.json +++ b/src/nodejs/package.json @@ -4,7 +4,7 @@ , "autor" : "osm@spline (http://osm.spline.de)" , "files" : ["."] , "dependencies" : - { "postgres" : "*" + { "pg" : "*" , "clutch" : "*" , "xmlbuilder" : "*" } -- cgit v1.2.3 From de138849df98ebc6260c6bab665707cc817248e9 Mon Sep 17 00:00:00 2001 From: yves Date: Wed, 19 Jan 2011 16:07:42 +0100 Subject: updated bootstrap.sh --- bootstrap.sh | 48 +++++++++++++++++++++++++++++++----------------- 1 file changed, 31 insertions(+), 17 deletions(-) diff --git a/bootstrap.sh b/bootstrap.sh index 02d71df..73fcf3b 100755 --- a/bootstrap.sh +++ b/bootstrap.sh @@ -1,32 +1,46 @@ #! /bin/sh +########################### +# start config + +PREFIX=`pwd`/usr/ NODE_VERSION=0.3.5 +# end config +########################### + +export PATH=${PREFIX}/bin:${PATH} + ## Download Helper -# $1 - url -# $2 - tarball name +# $1 - url +# $2 - destdir download() { - echo $1 - mkdir -p usr/src + mkdir -p ${PREFIX}/src if [ ! -f ${2} ]; then - curl ${1} > ${2} + echo "!! saving source to $1" + curl ${1} | tar -x -z -C ${PREFIX}/src fi - - tar -xf ${2} -C usr/src } -# fetch and compile nodejs -download http://nodejs.org/dist/node-v${NODE_VERSION}.tar.gz node-${NODE_VERSION}.tar.gz -rm -f node-${NODE_VERSION}.tar.gz +if [ ! -f ${PREFIX}/bin/node ]; then + # fetch and compile nodejs + download http://nodejs.org/dist/node-v${NODE_VERSION}.tar.gz ${PREFIX}/src/node-v${NODE_VERSION} + + cd ${PREFIX}/src/node-v${NODE_VERSION} + ./configure --prefix=${PREFIX} + make install +else + echo "!! node already installed" +fi -cd usr/src/node-v${NODE_VERSION} -./configure --prefix=../.. -make install -cd ../../.. +if [ ! -f ${PREFIX}/bin/npm ];then + # fetch and compile npm + curl http://npmjs.org/install.sh | sh +else + echo "!! npm already installed" -# fetch and compile npm -curl http://npmjs.org/install.sh | PATH=`pwd`/usr/bin:${PATH} sh +fi # load development code and compile dependencies -usr/bin/npm link src/nodejs/ +npm link src/nodejs/ -- cgit v1.2.3 From 5d7f1d4e1feda2fa4e3f7655404737d85276e8a9 Mon Sep 17 00:00:00 2001 From: slomo Date: Wed, 19 Jan 2011 20:40:30 +0100 Subject: fixed path issus with bootstrap --- bootstrap.sh | 1 + 1 file changed, 1 insertion(+) diff --git a/bootstrap.sh b/bootstrap.sh index 73fcf3b..802b7f1 100755 --- a/bootstrap.sh +++ b/bootstrap.sh @@ -30,6 +30,7 @@ if [ ! -f ${PREFIX}/bin/node ]; then cd ${PREFIX}/src/node-v${NODE_VERSION} ./configure --prefix=${PREFIX} make install + cd ${PREFIX}/.. else echo "!! node already installed" fi -- cgit v1.2.3 From 688fc4727124a3f4e8ac33ed73a8df05f681283c Mon Sep 17 00:00:00 2001 From: Mark Engel Date: Fri, 21 Jan 2011 14:22:19 +0100 Subject: externalized config (connectionString) --- src/nodejs/config.json | 1 + src/nodejs/no1.js | 10 +++++++--- 2 files changed, 8 insertions(+), 3 deletions(-) create mode 100644 src/nodejs/config.json diff --git a/src/nodejs/config.json b/src/nodejs/config.json new file mode 100644 index 0000000..1025c5b --- /dev/null +++ b/src/nodejs/config.json @@ -0,0 +1 @@ +exports.connectionString = "pg://user:password@host/database"; diff --git a/src/nodejs/no1.js b/src/nodejs/no1.js index 6c6809e..1132a11 100644 --- a/src/nodejs/no1.js +++ b/src/nodejs/no1.js @@ -1,9 +1,13 @@ var clutch = require('clutch'); -var pg = require('pg') -var builder = require('xmlbuilder') +var pg = require('pg'); +var builder = require('xmlbuilder'); +var config = require('./config.json'); -var connectionString = "pg://user:password@host/database"; + +var connectionString = config['connectionString']; +console.log("server starting..."); +console.log("Connection String: " + connectionString); function toISO8601(date) { //2007-03-31T00:09:22+01:00 -- cgit v1.2.3 From 570d3aa1c9bdebfdbf172f0873d7cda94b89fcaf Mon Sep 17 00:00:00 2001 From: Mark Engel Date: Fri, 21 Jan 2011 14:24:56 +0100 Subject: enhanced gitignore --- .gitignore | 13 +++++++++++++ 1 file changed, 13 insertions(+) diff --git a/.gitignore b/.gitignore index 1b91047..1608301 100644 --- a/.gitignore +++ b/.gitignore @@ -1 +1,14 @@ usr/ + +# Temp Files # +############## +*.swp + + +# OS generated files # +###################### +*.DS_Store +ehthumbs.db +Icon? +Thumbs.db + -- cgit v1.2.3