import javax.xml.parsers.SAXParserFactory; 
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser; 

import java.util.*;
import java.sql.*;
import java.io.*;

import org.xml.sax.*;
import org.xml.sax.helpers.DefaultHandler;

/**
	Pump Wikipedia-GeoData from XML via SQL to DB
	written by Stefan Wagner, 2007
	License GPLv3 http://www.gnu.org/licenses/gpl-3.0.html
	
	You can find the geodata here::
	@link http://www.webkuehn.de/hobbys/wikipedia/geokoordinaten/index.htm
	which I found via Wikipdia here (download/kml):
	@link http://de.wikipedia.org/wiki/Wikipedia:WikiProjekt_Georeferenzierung
	
	The table is created for postgresql by this command:
	Postgresql:
	CREATE TABLE ort (id serial, name varchar, h float, v float);
*/
public class GeoXml2sql extends DefaultHandler
{
/* your adjustments from here ... */
	private String driver = "org.postgresql.Driver";
	private String hostDB = "jdbc:postgresql://192.168.0.1/geo";
	private String user = "stefan";
	private String password = "dummy";
	private String insert = "INSERT INTO ort (name, h, v) VALUES (?, ?, ?)";
/* .. to here. No modifications should be neccessary below this line */
	
	public static void main (String argv [])
	{
		if (argv.length != 1)
		{
			System.err.println ("Usage: java GeoXml2sql geofilename.xml");
			System.exit (1);
		}
		DefaultHandler handler = new GeoXml2sql ();
		SAXParserFactory factory = SAXParserFactory.newInstance ();
		try
		{
			SAXParser saxParser = factory.newSAXParser ();
			saxParser.parse (new File (argv [0]), handler);
		}
		catch (Throwable t)
		{
			t.printStackTrace ();
		}
		System.exit (0);
	}
	/**
	Placemark description: 
		+ := used 
		- := unused
	<Placemark>
		-<description>
			<a href="http://de.wikipedia.org/wiki/Cafe Hummel">Wikipedia</a></description>
		+<name>Cafe Hummel</name>
		-<visibility>0</visibility>
		+ <LookAt>
			+ <longitude>16.3436111111111</longitude>
			+ <latitude>48.2108333333333</latitude>
			- <range>20000</range></LookAt>
		- <styleUrl>#without</styleUrl>
		- <Point>
			<coordinates>16.3436111111111,48.2108333333333,0</coordinates></Point>
	</Placemark>	
	*/	
	class Placemark
	{
		String name;
		float longitude;
		float latitude;
		
		public String toString ()
		{
			return name + "\t" + longitude +  "\t" + latitude;
		}
	}

	private StringBuffer textBuffer;
	private Placemark placemark;
	private List <Placemark> placemarks = new ArrayList <Placemark> (500000);
	
	public void startElement (String namespaceURI, String simple, String qualified, Attributes attrs) throws SAXException
	{
		String eName = qualified; // not namespace-aware
		if (eName.equals ("Placemark"))
			placemark = new Placemark ();
	}
	
	public void endElement (String namespaceURI, String simple, String qualified) throws SAXException
	{
		String s = getText ();
		String eName = qualified; // not namespace-aware
		if (eName.equals ("Placemark"))
		{
			if (placemark.name != null && (placemark.latitude != 0.0 ||placemark.longitude != 0.0 ))
			{
				// System.out.println (placemark);
				placemarks.add (placemark);
			}
		}
		else 
			if (placemark != null)
			{			
				if (eName.equals ("name"))
					placemark.name = s;
				else if (eName.equals ("latitude"))
					placemark.latitude =  Float.parseFloat (s);
				else if (eName.equals ("longitude"))
					placemark.longitude = Float.parseFloat (s);
			}
	}

	public void characters (char buf [], int offset, int len) throws SAXException
	{
		String s = new String (buf, offset, len);
		if (textBuffer == null) 
		{
			textBuffer = new StringBuffer (s);
		} 
		else 
		{
			textBuffer.append (s);
		}
	}

	private String getText () throws SAXException
	{
		if (textBuffer == null) 
			return null;
		String s = "" + textBuffer;
		textBuffer = null;
		return s;
	}

	public void endDocument () throws SAXException
	{
		try
		{
			Class.forName (driver);
			Connection con= DriverManager.getConnection (hostDB, user, password);
			PreparedStatement st = con.prepareStatement (insert);
			for (Placemark p : placemarks)
			{
				st.setString (1, p.name);
				st.setFloat (2, p.longitude);
				st.setFloat (3, p.latitude);
				st.executeUpdate ();
				// System.out.println (p);
			}
			con.close ();
		}
		catch (ClassNotFoundException cnfe)
		{
			System.err.println (cnfe.getMessage ());
			cnfe.printStackTrace ();
		}
		catch (SQLException sqle)
		{
			System.err.println (sqle.getMessage ());
			sqle.printStackTrace ();
		}
		catch (Exception e)
		{
			System.err.println (e.getMessage ());
			e.printStackTrace ();
		}
	}
}