001: //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/trunk/src/org/deegree/tools/shape/Shp2MySQL.java $
002: /*---------------- FILE HEADER ------------------------------------------
003:
004: This file is part of deegree.
005: Copyright (C) 2001-2008 by:
006: EXSE, Department of Geography, University of Bonn
007: http://www.giub.uni-bonn.de/deegree/
008: lat/lon GmbH
009: http://www.lat-lon.de
010:
011: This library is free software; you can redistribute it and/or
012: modify it under the terms of the GNU Lesser General Public
013: License as published by the Free Software Foundation; either
014: version 2.1 of the License, or (at your option) any later version.
015:
016: This library is distributed in the hope that it will be useful,
017: but WITHOUT ANY WARRANTY; without even the implied warranty of
018: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
019: Lesser General Public License for more details.
020:
021: You should have received a copy of the GNU Lesser General Public
022: License along with this library; if not, write to the Free Software
023: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
024:
025: Contact:
026:
027: Andreas Poth
028: lat/lon GmbH
029: Aennchenstr. 19
030: 53115 Bonn
031: Germany
032: E-Mail: poth@lat-lon.de
033:
034: Prof. Dr. Klaus Greve
035: Department of Geography
036: University of Bonn
037: Meckenheimer Allee 166
038: 53115 Bonn
039: Germany
040: E-Mail: greve@giub.uni-bonn.de
041:
042:
043: ---------------------------------------------------------------------------*/
044: package org.deegree.tools.shape;
045:
046: import java.io.BufferedWriter;
047: import java.io.FileOutputStream;
048: import java.io.OutputStreamWriter;
049: import java.util.ArrayList;
050: import java.util.HashMap;
051:
052: import org.deegree.datatypes.QualifiedName;
053: import org.deegree.datatypes.Types;
054: import org.deegree.framework.log.ILogger;
055: import org.deegree.framework.log.LoggerFactory;
056: import org.deegree.framework.util.StringTools;
057: import org.deegree.io.shpapi.ShapeFile;
058: import org.deegree.model.feature.Feature;
059: import org.deegree.model.feature.schema.FeatureType;
060: import org.deegree.model.feature.schema.PropertyType;
061: import org.deegree.model.spatialschema.Geometry;
062: import org.deegree.model.spatialschema.WKTAdapter;
063:
064: /**
065: *
066: *
067: * @version $Revision: 9346 $
068: * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
069: */
070: public class Shp2MySQL {
071:
072: private static final ILogger LOG = LoggerFactory
073: .getLogger(Shp2MySQL.class);
074:
075: private ArrayList<String> fileList = new ArrayList<String>();
076:
077: private String outDir = null;
078:
079: /**
080: * Creates a new Shp2MySQL object.
081: *
082: * @param file
083: */
084: public Shp2MySQL(String file) {
085: fileList.add(file);
086: int pos = file.lastIndexOf('\\');
087: if (pos < 0) {
088: pos = file.lastIndexOf('/');
089: }
090: if (pos < 0) {
091: outDir = "";
092: } else {
093: outDir = file.substring(0, pos);
094: }
095: }
096:
097: public void run() throws Exception {
098:
099: for (int i = 0; i < fileList.size(); i++) {
100:
101: String outFile = fileList.get(i) + ".sql";
102: int pos = outFile.lastIndexOf('\\');
103: if (pos < 0) {
104: pos = outFile.lastIndexOf('/');
105: }
106: if (pos >= 0) {
107: outFile = outFile.substring(pos + 1);
108: }
109:
110: BufferedWriter fos = new BufferedWriter(
111: new OutputStreamWriter(new FileOutputStream(outDir
112: + "/" + outFile), "ISO-8859-1"));
113:
114: ShapeFile sf = new ShapeFile(fileList.get(i));
115:
116: // delete table if already exists
117: fos.write("drop table "
118: + sf.getFeatureByRecNo(1).getFeatureType()
119: .getName() + ";");
120: fos.newLine();
121:
122: // get createtable sql statement and write it to the file
123: String createTable = getCreateTableStatement(sf
124: .getFeatureByRecNo(1).getFeatureType());
125: fos.write(createTable);
126: fos.newLine();
127:
128: String tableName = sf.getFeatureByRecNo(1).getFeatureType()
129: .getName().getPrefixedName().toUpperCase();
130:
131: LOG.logInfo("write to file: " + outDir + "/" + outFile);
132: // create an insert statement for each feature conained in
133: // the shapefile
134: for (int j = 0; j < sf.getRecordNum(); j++) {
135: if (j % 50 == 0)
136: System.out.print(".");
137:
138: StringBuffer names = new StringBuffer("(");
139: StringBuffer values = new StringBuffer(" VALUES (");
140:
141: Feature feature = sf.getFeatureByRecNo(j + 1);
142: FeatureType ft = feature.getFeatureType();
143: PropertyType ftp[] = ft.getProperties();
144: boolean gm = false;
145: for (int k = 0; k < ftp.length; k++) {
146: Object o = feature.getProperties(ftp[i].getName())[0];
147: if (o != null) {
148: QualifiedName name = ftp[k].getName();
149: String value = null;
150: if (o instanceof Geometry) {
151: value = WKTAdapter.export((Geometry) o)
152: .toString();
153: value = "GeomFromText('" + value + "')";
154: gm = true;
155: } else {
156: value = o.toString();
157: }
158: names.append(name.getPrefixedName());
159: if (ftp[k].getType() == Types.VARCHAR
160: || ftp[k].getType() == Types.CHAR) {
161: value = StringTools.replace(value, "'",
162: "\\'", true);
163: value = StringTools.replace(value, "\"",
164: "\\\"", true);
165: values.append("'" + value + "'");
166: } else {
167: values.append(value);
168: }
169: if (k < ftp.length - 1) {
170: names.append(",");
171: values.append(",");
172: }
173: }
174: }
175:
176: if (!gm) {
177: LOG.logInfo("" + names);
178: continue;
179: }
180: names.append(")");
181: values.append(")");
182:
183: fos.write("INSERT INTO " + tableName + " ");
184: fos.write(names.toString());
185: fos.write(values.toString() + ";");
186: fos.newLine();
187: }
188: sf.close();
189: fos.write("ALTER TABLE " + tableName
190: + " ADD SPATIAL INDEX(GEOM);");
191: fos.write("commit;");
192: fos.newLine();
193: fos.close();
194: }
195:
196: LOG.logInfo("finished!");
197:
198: }
199:
200: /**
201: * creates a create table sql statement from the passed <tt>FeatureType</tt>
202: *
203: * @param ft
204: * feature type
205: * @return the created SQL statement
206: */
207: private String getCreateTableStatement(FeatureType ft) {
208:
209: StringBuffer sb = new StringBuffer();
210: String name = ft.getName().getPrefixedName();
211:
212: PropertyType[] ftp = ft.getProperties();
213:
214: sb.append("CREATE TABLE ").append(name).append(" (");
215: for (int i = 0; i < ftp.length; i++) {
216: sb.append(ftp[i].getName()).append(" ");
217: int type = ftp[i].getType();
218: if (type == Types.VARCHAR) {
219: sb.append(" VARCHAR(255) ");
220: } else if (type == Types.DOUBLE) {
221: sb.append(" DOUBLE(20,8) ");
222: } else if (type == Types.INTEGER) {
223: sb.append(" INT(12) ");
224: } else if (type == Types.FLOAT) {
225: sb.append(" DOUBLE(20,8) ");
226: } else if (type == Types.DATE) {
227: sb.append(" Date ");
228: } else if (type == Types.GEOMETRY || type == Types.POINT
229: || type == Types.CURVE || type == Types.SURFACE
230: || type == Types.MULTIPOINT
231: || type == Types.MULTICURVE
232: || type == Types.MULTISURFACE) {
233: sb.append(" GEOMETRY NOT NULL");
234: }
235: if (i < ftp.length - 1) {
236: sb.append(",");
237: }
238: }
239: sb.append(");");
240:
241: return sb.toString();
242: }
243:
244: /**
245: * prints out helping application-information.
246: *
247: * @param n
248: * an integer parameter, which determines which help-information should be given out.
249: */
250: private static void usage(int n) {
251: switch (n) {
252: case 0:
253: System.out
254: .println("usage: java -classpath .;deegree.jar de.tools.Shp2MySQL "
255: + " [-f shapefile -d sourcedirectory]\n"
256: + " [--version] [--help]\n"
257: + "\n"
258: + "arguments:\n"
259: + " -f shapefile reads the input shapefile. must be set\n"
260: + " if -d is not set.\n"
261: + " -d inputdir name of the directory that contains the.\n"
262: + " source shapefiles. must be set if -f is\n"
263: + " not set.\n"
264: + "\n"
265: + "information options:\n"
266: + " --help shows this help.\n"
267: + " --version shows the version and exits.\n");
268: break;
269: case 1:
270: System.out
271: .println("Try 'java -classpath .;deegree.jar de.tools.Shp2MySQL --help'\n"
272: + "for more information.");
273: break;
274:
275: default:
276: System.out
277: .println("Unknown usage: \n"
278: + "Try 'java -classpath .;deegree.jar de.tools.Shp2MySQL --help'\n"
279: + "for more information.");
280: break;
281: }
282: }
283:
284: /**
285: * @param args
286: * the command line arguments
287: */
288: public static void main(String[] args) throws Exception {
289:
290: if (args == null || args.length < 2) {
291: usage(0);
292: System.exit(1);
293: }
294:
295: HashMap<String, String> map = new HashMap<String, String>();
296:
297: for (int i = 0; i < args.length; i += 2) {
298: map.put(args[i], args[i + 1]);
299: }
300:
301: if (map.get("--help") != null) {
302: usage(0);
303: System.exit(0);
304: }
305:
306: if (map.get("--version") != null) {
307: System.out.println("Shp2MySQL version 1.0.0");
308: System.exit(0);
309: }
310:
311: // one single file shall be transformed
312: if (map.get("-f") != null) {
313: String f = map.get("-f");
314: if (f.toUpperCase().endsWith(".SHP")) {
315: f = f.substring(0, f.length() - 4);
316: }
317: Shp2MySQL shp = new Shp2MySQL(f);
318: shp.run();
319: } else {
320: // the files of a whole directory shall be transformed
321: }
322:
323: }
324: }
|