require 'rubygems'
require 'xml' # libxml2 ruby bindings
 
# TABLE SCHEMA:
#create table flickr_shapes (id int not null auto_increment primary key, kind varchar(255), label varchar(255), woe_id int, min_lat decimal(20,17), min_lng decimal(20,17), max_lat decimal(20,17), max_lng decimal(20,17));
# USE:
#select * from flickr_shapes where 51.52830123901367 between min_lat and max_lat AND -0.0916564017534256 between min_lng and max_lng;
 
class MyCallbacks
    include XML::SaxParser::Callbacks
    def on_start_document
        puts "Here goes..."
    end
    def on_start_element_ns(name, attributes, prefix, uri, namespaces)
        if name == 'place'
            @attrs = attributes
        end
        if name == 'polylines'
            @attrs.merge!(attributes)
            fields = [@attrs['woe_id'], @attrs['label'], @attrs['place_type']] + @attrs['bbox'].split(/,/)
            puts "INSERT INTO flickr_shapes (woe_id, label, kind, min_lat, min_lng, max_lat, max_lng) VALUES (#{fields.map { |f| "'#{f.gsub(/'/,"")}'" }.join(",")});"
        end
    end
end
 
parser = LibXML::XML::SaxParser.file("db/flickr_shapefiles_public_dataset_1.0.1.xml")
parser.callbacks = MyCallbacks.new
parser.parse