Maps-l May 2010

maps-l@lists.wikimedia.org

10 participants
12 discussions

Planet-Import with hstore
by Peter Körner 15 Jun '10

15 Jun '10

Hi Folks, i started a planet import with the additional hstore column. It allows us to retrive and search in all tags used in osm. I used a combined hstore/column scheme, as our tests on the german devserver showed that hstore-value-conditions are much slower than the column-conditions of the classic database layout. If the import is over, i'll come back with some sample queries. The import is running on ptolemy using the following command: $HOME/src/osm2pgsql/osm2pgsql \ --slim \ --cache 3000 \ --merc \ --prefix planet \ --style $HOME/src/osm2pgsql/default.style \ --host sql-mapnik \ --database osm_mapnik \ --hstore \ --extra-attributes \ $HOME/data/planet-100505.osm.bz2 2>&1 | \ tee $HOME/planet_osm_hstore_import.log I'm planning to switch the ddiff update process to this database after the import and to drop the old, classic database. Peter

3 14

introduction: the new osm database
by Peter Körner 01 Jun '10

01 Jun '10

Hi folks, I'm proud to announce the availability of a new osm database that holds all nodes, ways and polygons with all used tags used in osm. It uses the postgresql hstore extension to form a "tags" column which is an indexed hash array of all tags. despite that it has the "ways"-column known from regular osm2pgsql databases and the normal tag-columns that are listed in osm2pgsql's default.style. Because of this combined scheme it is adequate for a lot of queries. To take use of the indexes you'll need to follow some rules. I'll point out some of them and also give some examples on how to use the new scheme. I'll switch the diff-update process from the classic scheme to the new combined scheme in the next days and drop the old scheme (tables starting with planet_osm) in about two weeks if no one protests. I'll take some days to catch up. == a query in a (relative) small area == in this case the geometry-index is the most important one and you can use the tags-column as you wish: SELECT tags FROM planet_point WHERE (tags->'man_made' = 'surveillance') AND way && ST_Transform(ST_SetSRID(ST_MakeBox2D( ST_Point(8.17,50.04), ST_Point(8.36,49.95) ), 4326),900913); == a query on a rarely used tag in a large area == in such a case you want to use the index on the tags-column to identify the nodes. the index only knows "which node has which tag", means it doesn't know anything about the values. So to take use of the index you need two different conditions: one to filter for the tag and a second one for the tags value: SELECT tags FROM planet_point WHERE (tags ? 'club-mate') AND (tags->'club-mate' = 'yes') == a query on a heavy used tag == in the case of a heavy used tag (eg. amenity), the gin-index on the hstore column does not work very good, especially when the tag has a such huge number of different values, because (as we learned before) the index doesn't know anything about the value. In such a case its better to use the regular columns. anyway such a query is always a bad idea, if you don't specify a small enough bounding box, so don't run this query, please: SELECT tags FROM planet_point WHERE amenity = 'restaurant' == live statistics == a very cool feature of the hstore column is that you can get live tag usage statistics from it. please don't run this on the whole planet before talking about it on this list, as it will may drive the database-server down, but for small or medium sized regions, it's really cool: SELECT key, count(*) FROM ( SELECT (each(tags)).key FROM planet_point WHERE way && ST_Transform(ST_SetSRID(ST_MakeBox2D( ST_Point(8.17,50.04), ST_Point(8.36,49.95) ), 4326),900913) ) AS stat GROUP BY key ORDER BY count DESC, key LIMIT 20; == rendering == thanks to river we have a quite nice rendeing stack on willow. You can use the command line tool /home/project/o/s/m/osm/bin/render to test your own styles and if they're ready you can mail them to this list so we can configure renderd to render tiles for them. To show the capabilities of the new hstore scheme in conjunction with the rendering system, I created a style that renders surveillance cctvs on a transparent overlay. It's a quite simple style but it shows how to do things. It's located at /home/project/o/s/m/osm/data/styles/surveillance/surveillance.xml and should be redable for everyone. to render a map of germany in this style, just log on to willow and call render --bbox 5.56,55.04,15.35,47.15 --style /home/mazder/styles/surveillance/surveillance.xml --size 2000x2000 it will create a map.png if the current directory. Call /home/project/o/s/m/osm/bin/render --help to learn about the possible parameters. You'll want to add /home/project/o/s/m/osm/bin/ to your PATH in your .bash_profile so you can just all render without a path. I also added the style to the renderd config and you can take a look at it under http://toolserver.org/~osm/styles/?zoom=12&lat=52.50546&lon=13.42461&layers… The style is not perfect, the icons are too big but hey - its just a sample. Thank you for reading. I'm happily looking forward in seeing cool map styles and useful tools. If you got questions, just ask at <maps-l(a)lists.wikimedia.org>. Peter

4 19

Account-Extending June
by DaB. 01 Jun '10

01 Jun '10

Hello folks, at first of June, the toolserver-accounts will expire again. If you need your account further, please send an eMail to ts-accounts201001(a)dabpunkt.eu before this date. The eMail should contain your login-name in the subject and in the body. Please do NOT send the eMail to this mailing-list; write a NEW eMail, DON'T use the answer-function of your eMail-client (because it will send the eMail to this mailinglist). If you send the eMail to the mailinglist, you will get the "Can't use his/her eMail-client"-price (and some jokes from the regulars) and NO extending. This time, I will use our wiki to list account-names for which I got an email for (see [1]) — please be a little bit patient after you sent me the email. Maybe I will also send lists per mail too. Please notice that tools of expired users will be disabled, so if you plan to not extent your account, make sure that you transfer your tools to another ts- user before 1. June. We hope that this is the last time that we use email for account-extending and that we can use something new next time. If you have questions, please use the TS-Maillinglist. Sincerly, DaB. [1] https://wiki.toolserver.org/submit/Account-Extending_2010-01 -- Userpage: [[:w:de:User:DaB.]] — PGP: 2B255885

1 2

"Need" a new python module
by Kay Drangmeister 31 May '10

31 May '10

Hi, I have some python scripts that access the mapnik database (on my home server). I am to migrate these to toolserver/mapnik/hstore. The problem is that I import psycopg2 as a database connector: kayd@willow:~/parkingicons$ python /home/kayd/parkingicons/osm-parking-icons.py Traceback (most recent call last): File "/home/kayd/parkingicons/osm-parking-icons.py", line 9, in <module> import psycopg2 ImportError: No module named psycopg2 Question1: is it possible to install that module? (download from http://initd.org/psycopg/) ~/Install/psycopg2-2.0.13$ python setup.py build ~/Install/psycopg2-2.0.13$ sudo python setup.py install or Question2: what alternatives are available? Kind regards, Kay

1 0

Reboot of nightshade tonight
by DaB. 25 May '10

25 May '10

Hello all, I updated the kernel of nightshade a few minutes ago and now it needs a reboot. So I will reboot nightshade arround 22:15 UTC. The downtime should be quite low (few minutes) if no problems occure. You can follow the process at [1] if you like. Sincerly, DaB. [1] https://jira.toolserver.org/browse/MNT-593 -- Userpage: [[:w:de:User:DaB.]] — PGP: 2B255885

1 0

Announcement:Project Wikipedia-World moved to PostGIS-DB
by Tim Alder 20 May '10

20 May '10

Hello, I moved with my Wikipedia coordinate extraction project Wikipedia-World: http://de.wikipedia.org/wiki/Wikipedia:WikiProjekt_Georeferenzierung/Wikipe… from Mysql to PostGIS on ptolemy. The most advantage is that it is much faster and really support all 273 languages. I have 3 tables for different zoomlevels and use a geometry index (gist). I use coordinates-database from dispenser and merge the entries with the interwikilinks to one table. I support the parameter "title" and can so extract more than one coordinate per article. Everybody on ptolemy should have access to the database u_kolossos. I hope ptolemy will be stable over the next few days because I want to publish the tool to the german community. I would be happy if somebody else make a announcement to the other wikipedia communities. Greetings Kolossos

4 5

Tile purge strategy?
by Kay Drangmeister 19 May '10

19 May '10

Hi, for the tiles rendered on page http://toolserver.org/~osm/styles/?layers=00B0F0FF0000F what is the strategy to mark tiles as dirty? I.e. when are existing tiles re-rendered? Thanks, Kay

2 3

Re: [Maps-l] hstore on Query-to-map project
by Peter Körner 17 May '10

17 May '10

Am 16.05.2010 12:15, schrieb Tim Alder: > As far I know the hstore support no "like" command. Well, you can do WHERE (tags->'name' LIKE 'A%') but I'm not sure if this can take use of any index. > So a column index > would be nice to > get e.g. all streets beginning "A..." in an area: "where name like 'A%'" > or looking for something like > "Amselweg" or "Amselstraße"... > So perhaps line table would be better than the point table for testing. We are updated with the main db now, so I added the two indexes on the line table: CREATE INDEX planet_line_name ON planet_line (name); CREATE INDEX planet_line_ref ON planet_line (ref); and did some test-queries (see textfile). of course the BitmapAnd is much faster then the hstore like. As far as I understood we'll have to add those indexes to the other tables as well, right? Peter

2 1

hstore on Query-to-map project
by Tim Alder 16 May '10

16 May '10

I'm happy to can say: "The light is on.". I moved with my project Query-to-map[1] to hstore-DB which was really easy, so I can now also show lit=yes[2]. ;-) As far as I know is no project active on the old tables, so we could shut off them? "drink:club-mate=yes" (rare key with commons value) works fine, also in a huge area, so I'm very happy ;-) But "amenity=baby_hatch" (commons key with rare value) and others doesn't work fine in a huge area, that is frustrating. Peter explains the reasons in his mail. But we should really find an answer for this problem. Is there a way to indexing the hstore with alternating the keys and the values or indexing the values? I read in in the documentation[3] only that "%% hstore" convert hstore to array of alternating keys and values. Or could we use a second alternated hstore? This should work and there are a lot of use-cases. Independent from that I want to ask if I can get an index on "name" and "ref", because I use this often to link on objects from featurelist. The tools seems in the moment not very fast because we have high load on the database until we are up-to-date. So I will wait with further testing. Greetings Kolossos [1] http://wiki.openstreetmap.org/wiki/Query-to-map [2] http://toolserver.org/~kolossos/qtm2/queryinmap.php?BBOX=13.5333,50.95,13.9… [3] http://developer.postgresql.org/pgdocs/postgres/hstore.html

4 7

Changes to osm account
by River Tarnell 08 May '10

08 May '10

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, I've made a couple of changes to the "osm" MMT: * $HOME/renderd/bin is now $HOME/bin * $HOME/renderd/src is now $HOME/src/renderd * osm2pgsql is now in $HOME/src/osm2pgsql (source) and $HOME/bin/osm2pgsql (binary). The system /opt/ts/bin/osm2pgsql is still installed, but I'll remove it once the import scripts have been updated. This means any use can update osm2pgsql (but be careful, since it needs a few local changes; see 'svn diff'). - river. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.10 (HP-UX) iEYEARECAAYFAkvk7GMACgkQIXd7fCuc5vKElACfQZx0XNSO/cM4hvE3zx6soKLe 2zwAn1yVjiNY0uSR/1kLOJAWRug7ZbCY =gU6X -----END PGP SIGNATURE-----

2 1

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

Maps-l May 2010