Entries by Hartmut Holzgraefe

Slides for my two MySQL Conference talks are now available from the O'Reilly conference site:

• From Map to Web - OpenStreetMap -> (PostGIS|MySQL|SpatiaLite) -> Openlayers
• A code stub generator for MySQL and Drizzle plugins

Found this while waiting for the CSV export of a search result from (*undisclosed project*) ...

The export took almost half an hour even though it was just extracting the snail mail addresses of about 40K contacts, so what caused it to only spit out about 20 rows per second? Looking at the PROCESSLIST output a pattern was easy to detect. What greeted me was a single running query which had only been running for a few seconds even though i was already waiting for export results for about 20 minutes:

SELECT ... LIMIT 32900, 100;

And then a few seconds later:

SELECT ... LIMIT 33000, 100;

So instead of retrieving the search results for export by firing a single query and processing the results in one go the same pagination logic seemed to be used as for displaying search results. About 400 queries instead of a single one, and each iteration taking more query time, with finally the last one taking about the same time as the only query in the all-at-once alone.

Somewhere, somehow, something terribly went wrong it seems ....

With the changes i recently committed to the OSM SVN repository osm2pgsql can now read OSM files in the new binary PBF format directly (with parsing code based on that found in pbf2osm). Files in PBF format are available from the geofabrik download site along with their XML counterparts and are usually about 30% smaller than bzip2 compressed XML. osm2pgsql also parses PBF about twice as fast, so substantially reducing the time taken for the first processing step.

Also part of my committed changes are an improved autotools setup that checks for library and header file availability in the configure stage already, and that only includes PBF support if the needed GNU ProtoBuffer related tools and libraries are available.

Unfortunately the PBF code requires at least protobuf-c 0.14, which is not yet part of current distributions, so you'll have to install that one from source yourself. But with proper configure checks in place you'll be at least warned about version mismatches here and osm2pgsql will be built without PBF support then.

Thanks to TomH for adding the missing fields to the API response so quickly

No more need to manually download tracks you previously uploaded to openstreetmap.org first, then opening the downloaded file with JOSMs "Open file ..." dialog. You can now pick an uploaded track and have it loaded into a new GPX layer directly with the help of the DirectDownload plugin.

You can download the compiled plugin .jar or the source code, but be warned: current status is "works for me" aka. "proof of concept".
The desired functionality is there, but it still is a bit rough around the edges.

I confess that i've been hiding under a stone for quite some time when it comes to contemporary HTML stuff, for most of the things i'm doing on the presentation level even good old HTML 1.0 would be sufficient if it weren't for the occasinal tabular data ...

So while i know CSS in theory i've never really looked into the black magic of doing CSS layouts and stuff ... until now ...

What i'm up to right now was to produce a small series of step by step examples on how to use OpenLayers to present customized OpenStreetMap based maps, and for that i wanted to have a simple layout like this:

... oh why do java binaries (java, javac, jar ...) use long command line options starting with a single dash instead of going with the "one dash for one letter options, two dashes for long option names" convention?

I'll probably never learn to type things like java -version instead of java --version right away ...

After getting a Panasonic Lumix DMC-FS6 as christmas present for myself last year i today took my first try at photo mapping with Josm ... and failed.

Camera and GPS device clocks seemed to be almost perfectly in sync but i could not convince JOSM to load those issues and associate them with the loaded GPX track at all, no matter what i tried.

After double checking the photos EXIF tag entries and file system time stamps and the .gpx file entries for quite a while i finally found the reason:

The camera still believed to be living in 2009, it hat somehow forgotten to change over to 2010. The original test photos i did last year clearly show that i had set the date right, so i'm afraid i now need to remember to double check the cameras date every new year now ... :/

CodeGen_MySQL_Plugin had basic support for status variables (for SHOW STATUS) for quite a while, now it also has basic support for system variables (SHOW [GLOBAL|SESSION] VARIABLES, SET) in the 0.9.2 package version i just released as well

So i finally defeated my 'inner Schweinehund' and set up a working Windows development system again. Using the Visual Studio C++ 2008 Express edition i'm now able to compile UDF projects generated by CodeGen_MySQL_UDF aka. udf-gen, and the udf-gen script now works natively on Windows, too.

I'm talking at the MySQL User Conference once again this year presenting my Code Generators for MySQL Plugins and User Defined Functions in a formal session this time after my original "pizza & hacking" session on this topic on the 2006 UC (which ended up in hacking some other things on the way first ... photographs may follow ...).

The plugin and UDF code generators are based on the same foundation as my "pecl-gen" generator for PHP extensions so this might be of interest for hardcore PHP folks, too.

For more information see the Conference Schedule.

In the recent past the RSS feeds on http://forge.mysql.com/ used to loose the formatting on posts leading to most feed readers showing them as just one long wrapped line of text. This made forum posts hard to read and impossible to figure out any SQL or code snippets within.

This has now been fixed a few days ago, so providing a way better reading experience for all us RSS users

I just came back from this years FROSCON event and even though i don't think that i did discover any new big things there were two small tools that i wasn't aware of so far which i'll probably use on a rather regular basis in the future.

I finished implementing the missing optional REGEXP function parameters today, all regular expression functions should now work as similar to their Oracle counterparts as possible with the following restrictions:

* The MySQL UDF API predates the extended character set support added with MySQL 4.1 and so UDF functions have no idea about charsets and collations at all. As a consequence the functions for now are always case sensitve by default and are always assuming their input to be Latin1 encoded
* Only the 'c' and 'i' pattern modifiers for case sensitive and insensitive matching are implemented yet, the 'm' and 'n' modifiers for multi line input are not yet supported
* I haven't tested back references on REGEXP_REPLACE() yet. They may or may not work, as i borrowed the actual implementation from the PHP source i'm not really sure about whether it takes care of this or not

Anyway, the REGEXP UDFs are hosted on http://udf-regexp.php-baustelle.de/ and the new 1.0 release is available here

I released version 0.9.8 of my MySQL UDF code generator yesterday and i finally moved development over to my own php-baustelle.de family of sites.