Reverse geocode with sql 2008

I’m trying to parse the osm xml file and create a sql 2008 database for the purposes of reverse geocoding. So far I have a Node table, way table and Tag table which contains tags for nodes and ways. Since I only am after, the city,state,country type of data in my parsing I only insert ways that are closed (4 or more points where the last point is the same ID. My results aren’t all that great which leads me to believe I’m missing something from parsing the files, maybe the references or ways that aren’t closed. I did look through the files and found some ways entered that were an administrative boundary, yet the last node wasn’t the same ID therefore isn’t a closed way.

Any input or suggestions are appreciated.

Unfortunately boundary ways can be a lot more complicated than simple closed ways. Administrative borders, given that they are usually very long, are often broken into many separate ways and then rejoined with relations ( ) So you will need to parse the relations and reconstruct a closed polygon from that.

However, boundary data isn’t perfect in OSM either, and some may even when taking relations into account not form closed ways (yet). To get a feeling for the completeness, you can have a look at, which shows which boundaries form closed areas. (Currently only available for Europe)

Otherwise, you could have a look at the code of Nominatim ( ) to see how it does it’s indexing, although that is written for Postgresql and not sql 2008.