http://mhms.org.uk/ are doing some interesting historical work by digitising the old Brighton Street Directories and the property registers. I took this photo in Kemp Street where a number of the houses are displaying lists of all the people who have owned the property since it's construction in the 1800's.

I particularly like the way that they have taken data and turned it into an artefact that can be used by people in the street, at the physical place where it happened. In http://openplaques.org we do it the other way round, recording digitally the physical artefact that has been placed on a building. There is also a difference as mhms isn't just about famous people.

I offered to help a while ago, but got no reply. Maybe we can crossover when they feature somewhere with blue plaques, such as Regency Square.

Had a good day at Crystal Palace athletics. Always a good meet because it's the athletes last chance to practice before the World Championships (in Korea).
Phillips Idowu set a good opening distance in the triple jump but was eclipsed by two others. Lisa Dobrisky won her race and other Brits managed A-rated times in order to help their late selection for the Worlds.

We have a piece of software that insists on generating a type of csv file containing multiple datasets. I'm currently turning this into XML to make it easier to process (funny that, people seem to have a downer on xml most of the time). I want to parse the csv without knowing what the columns will be, plus they can be speechmark delimitted and maybe contain carriage returns.

Anyway, it seems easy to start with and then gets more and more involved. Let's face it, life is too short to write a full parser for myself. I did a bit of research and took the opportunity to try it out in groovy. My latest version uses a groovy wrapper https://github.com/xlson/groovycsv/ of http://opencsv.sourceforge.net/

UPDATE: my good friend and ex-colleague @nugsie suggested a MarkupBuilder....

 @Grab('com.xlson.groovycsv:groovycsv:0.2')
 import com.xlson.groovycsv.CsvParser
 import groovy.xml.MarkupBuilder
 
 def csv = '''field_one,field_two,field_three
 ay,"be""e",sea
 ay,"bee
 multiline",sea'''
 
 def writer = new StringWriter()
 def xml = new MarkupBuilder(writer)
 
 def data = new CsvParser().parse(csv)
 
 xml.records() {
   data.each { line ->
     feature() {
       line.columns.each { columnName, index ->
         "${columnName}"(line.values[index])
       }
     }
   }
 }
 
 writer.toString()
 

output from which is:

<?xml version="1.0" encoding="UTF-8"?>
 <records>
   <Feature>
     <field_one>ay</field_one>
     <field_two>be"e</field_two>
     <field_three>sea</field_three>
   </Feature>
   <Feature>
     <field_one>ay</field_one>
     <field_two>bee
 multiline</field_two>
     <field_three>sea</field_three>
   </Feature>
 </records>

...nice.

 

The foxes were having a fine old time racing round next door's garden

Found this in Snoopers' Paradise. Truly tacky.

So, taking features from OSM for healthcare: "pharmacy","hospital","dentist" and "doctors" (we'll leave "baby_hatch" for now as it's slightly scary...or maybe i'm just not enlightened enough!) I can do two sql statements, one for the ways and one for the nodes then UNION ALL them together to get a result set of ways and nodes.

We are competing with:

nearest hospital = Children's University Hospital 862m

nearest pharmacy = Gordons Chemist, Newry 811km (!!!!)

Results are:

 

way	node	key	value	site_name	dist
79498864	928009126	amenity	hospital	Rotunda Maternity Hospital	256.708557
51776450	660713035	building	hospital	NULL	261.315159
582756469		amenity	pharmacy	Parnell Pharmacy	304.355369
51776463	660713097	building	hospital	NULL	322.308431
631808707		amenity	pharmacy	Health Express	333.650710
471861761		amenity	pharmacy	Hickey's Pharmacy	338.425801
279912239		amenity	pharmacy	Hamilton Long Pharmacy	397.367718
768516996		amenity	doctors	Jervis Medical Centre	469.036586
249430783		amenity	pharmacy	Traceys	574.297052
567713114		amenity	doctors	Dame Street Medical Centre	597.042663
567713109		amenity	pharmacy	City Pharmacy	606.547296
559854612		amenity	pharmacy	Medipharm	653.331539
249430226		amenity	pharmacy	NULL	694.285493
41779231	515098838	amenity	hospital	Children's University Hospital Temple Street	706.971984
33005187	371973866	amenity	hospital	Mater Private Hospital	829.883832

The second object detected is a hospital building inside the grounds of Rotunda Maternity Hospital. Buildings often won't be contained within grounds, so I can't distinguish. Perhaps when there is a stanalone building then it has a name?

 

The sql is:

SELECT wt.way_id as way, osm_way_node_refs.node_id as node, wt.k as key, wt.v as value, name_tag.v AS site_name


, min(Distance("Geometry" ,GeomFromText('POINT(-6.2626 53.34942)'))*100000) AS dist


FROM osm_way_tags wt, osm_way_node_refs, osm_nodes


LEFT OUTER JOIN osm_way_tags name_tag


 ON wt.way_id = name_tag.way_id AND name_tag.k="name"


WHERE wt.way_id = osm_way_node_refs.way_id


AND osm_way_node_refs.node_id = osm_nodes.node_id


AND wt.k in ("amenity","building") AND wt.v in ("pharmacy","hospital","dentist","doctors")


GROUP by way


 


UNION ALL


 


SELECT "" as way, n.node_id as node, wt.k as key, wt.v as value, name_tag.v AS site_name


, Distance("Geometry" ,GeomFromText('POINT(-6.2626 53.34942)'))*100000 AS dist


FROM osm_node_tags wt, osm_nodes n


LEFT OUTER JOIN osm_node_tags name_tag


 ON wt.node_id = name_tag.node_id AND name_tag.k="name"


WHERE wt.node_id = n.node_id


AND wt.k in ("amenity","building") AND wt.v in ("pharmacy","hospital","dentist","doctors")


 


ORDER BY dist

My journey for OSM Points Of Interest has taken me firstly to Enrico Zini's post "Importing OSM nodes into Spatialite"

* as I am using Windows (not by choice, they made me do it!) I needed to download and install libspatialite, proj, geos and libiconv dlls from http://www.gaia-gis.it/spatialite/binaries.html

* I needed pysqlite for my python 2.5 installation from http://code.google.com/p/pysqlite/downloads/list

* I also needed simplejson which I got via easy_install

* The script needed to point to the .dll and not an .so -> self.db.execute("SELECT load_extension('libspatialite-1.dll')")

* dict() doesn't like it when a key doesn't exist, so if there is an OSM node with no tags["name"] the program will error. Instead, I used defaultdict() with: from collections import defaultdict and self.tags = defaultdict(str)

SELECT poi."id", tag."name"


, Distance("geom" ,GeomFromText('POINT(-6.2626 53.34942)'))*100000 AS dist


, poi."name", "data"


FROM "poi", "poitag", "tag"


WHERE poi.id = poitag.poi    


AND poitag.tag = tag.id    


AND tag."value" = "cafe"


ORDER BY dist

Which gives me Dublin Dental Hospital as my first hit. Again, I am faced with the same difficulty in that amenities could either be an OSM node or a circular OSM way. It gets more complicated to process a Way as it references the Nodes that make it up, and Enrivo's script is about parsing the xml input stream.

In Spatialite 2.4 rc4 http://www.gaia-gis.it/spatialite-2.4.0-4/binaries.html there is a new tool called spatialite_osm_raw. This was really easy to use to import OSM XML data direct into a Spatialite database.

 

C:\Users\jez.nicholson.ARGYLLENVIRO\Desktop>spatialite_osm_raw -o Dublin.osm -d dub_osm.sqlite


SQLite version: 3.7.3


SpatiaLite version: 2.4.0    


inserted 8239 nodes


4992 tags


inserted 2207 ways          


7068 tags          


10879 node-refs


inserted184 relations          


688 tags          


903 node-refs          


6573 way-refs          


0 relation-refs

A quick bit of SQL:

SELECT wt.way_id, osm_way_node_refs.node_id, wt.k, wt.v, name.v


, Distance("Geometry" ,GeomFromText('POINT(-6.2626 53.34942)'))*100000 AS dist


FROM osm_way_tags wt, osm_way_tags name, osm_way_node_refs, osm_nodes


WHERE wt.way_id = osm_way_node_refs.way_id


AND osm_way_node_refs.node_id = osm_nodes.node_id


AND wt.k="amenity" AND wt.v="hospital"


AND wt.way_id = name.way_id


AND name.k="name"


ORDER BY dist    

...and I can list hospitals which are defined as ways: Rotunda Maternity Hospital at 256m away at it's nearest point and Children's University Hospital Temple Street 706m, which is better than the result of "Children's University Hospital at 862m" which I got from Navtec.

Next step is to combine Node and Way POIs. I feel that I will need to create a new sql table that mixes point and polygon geometries (if it doesn't do that already).

<h2>see also</h2>

* my previous post First steps towards open source OpenStrretMap Points Of Interest POI data

* my next post OpenStreetMap Points Of Interest - it works!

I'm working with some European data at the moment, part of which is POI data for "nearest hospital", "nearest educational facility", etc. and the data is quite poor. I suspect that it originally sourced from NavTec and hence "Yellow Books". I also suspect that the data in OpenStreetMap will be orders of magnitude more accurate.

My first port of call was to examine the attributes used in OSM. To do this I download an area of central Dublin into JOSM and look at the raw data. Taking hospitals as an example I can quickly see that these are encoded in three different ways:

1. As point data with an attributes of "amenity=hospital" and "name=foo"
2. As an area, again marked "amenity=hospital" and "name=foo"
3. As a building, which is an area also marked with "building=hospital". Hospital buildings are often placed within a hospital area, but not necessarily.

I've then taken a look at http://www.gaia-gis.it/spatialite SpatiaLite which is a really lightweight file-based database server. This would be great for packaging with an app, but is also good for quick experiments. I may need to go up to PostGIS if it isn't up to the job.

I downloaded the spatialite-gui binary for Windows, unzipped it and ran it.

From http://download.geofabrik.de/osm/europe/ GeoFabrik I downloaded ireland.shp.zip

In the SpatialLite GUI I created a new database and then went to "Load Shapefile", I picked a .shp file from my downloaded data and set the SRID to 4326 (which is the id in the spatial_ref_sys table for WGS-84) and the charset to UTF-8. It then imported in a matter of seconds. This gave me a sql table that I could query using extra functions such as Distance()

I got a WGS-84 lat/lon for my search point by right-clicking on Google Maps and doing a "Drop LatLng Marker" (I can't remember whether this is an opt-in feature). I have since worked out that SpatialLite expects the coordinated to be the other way round: lon/lat and not lat/lon.

SELECT ROWID, "PK_UID", "osm_id", "name", "type"


, Distance("Geometry" ,GeomFromText('POINT(-6.2626 53.34942)'))*100000 AS dist


FROM "points"


WHERE "type" = "hospital"


ORDER BY dist

gives me a result set starting with: osmid=641802162, name=Dublin Dental Hospital, distance 1289m

When I query the buildings I get: osmid=51776450, name=NULL, distance 261m...which is the first building of the Rotunda Maternity Hospital.

What I don't know is where to get hold of the area data. Is it in the road shapefile?! I think not. So where would it be?

 

Enrico Zini's post "Importing OSM nodes into Spatialite"

UPDATE 9th June 2011: I was just looking into importing raw osm data and found http://www.enricozini.org/2010/tips/osm-import-nodes/ which looks very like what I wanted

* as I am using Windows (not by choice, they made me do it!) I needed to download and install libspatialite, proj, geos and libiconv dlls from http://www.gaia-gis.it/spatialite/binaries.html

* I needed pysqlite for my python 2.5 installation from http://code.google.com/p/pysqlite/downloads/list

* I also needed simplejson which I got via easy_install

* The script needed to point to the .dll and not an .so -> self.db.execute("SELECT load_extension('libspatialite-1.dll')")

* dict() doesn't like it when a key doesn't exist, so if there is an OSM node with no tags["name"] the program will error. Instead, I used defaultdict() with: from collections import defaultdict and self.tags = defaultdict(str)

<em> </em>
<p style="display: inline !important;"><em>SELECT poi."id", tag."name", Distance("geom" ,GeomFromText('POINT(-6.2626 53.34942)'))*100000 AS dist, poi."name", "data"</em>


<em> </em>


 


<em> </em>


<em>FROM "poi", "poitag", "tag"</em>


<em>


WHERE poi.id = poitag.poi


  AND poitag.tag = tag.id


  AND tag."value" = "cafe"
</em><em>
<p style="display: inline !important;">ORDER BY dist
<p style="display: inline !important;"> 
</em>

 

 

I had a fine old time last weekend in sunny(ish) Berlin with the WhereCampers. I did another talk about Open Plaques but didn't get many punters as it was quite difficult to escape the gravitational pull of the main hall (..or people weren't interested).

One of the hot topics was producing better POI data, with talks from both Google and Yahoo! I'm seeing this myself with producing reports for Europe in that much of current POI data can be traced back toi NavTec and from there probably back to one of the 'yellow books' services. With the rise of the web, the yellow books have become far less important to businesses and I believe have struggled to produce relevant data any more. Google were looking at mining web sites for better info. Yahoo! were investigating mining Flickr photos and tags. Both companies are interested in finding up-to-date POIs and also in using colloquial terms that the real users call them.

There was plenty of talk about open data and some discussion of copyright.

Another popular topic was data visualisation with some inspiring visuals from @oobr

There was a lot more, but i've got work to do now so must go. On the whole, my gut feel was that anything geo is on the way up.

Found two more of the stolpersteine http://www.goethe.de/kue/arc/dos/dos/zdk/en78940.htm on my way back to the hotel.

Any #wceu people who see any commemorative plaque on your travels on Berlin please photograph and add to Flickr with a Creative Commons licence so that we can add to http://openplaques.org just tag it openplaques:todo=add