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
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
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
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
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
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
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
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
-----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-----