001 //$HeadURL: svn+ssh://rbezema@svn.wald.intevation.org/deegree/base/branches/2.2_testing/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.getLogger( Shp2MySQL.class );
073
074 private ArrayList<String> fileList = new ArrayList<String>();
075
076 private String outDir = null;
077
078 /**
079 * Creates a new Shp2MySQL object.
080 *
081 * @param file
082 */
083 public Shp2MySQL( String file ) {
084 fileList.add( file );
085 int pos = file.lastIndexOf( '\\' );
086 if ( pos < 0 ) {
087 pos = file.lastIndexOf( '/' );
088 }
089 if ( pos < 0 ) {
090 outDir = "";
091 } else {
092 outDir = file.substring( 0, pos );
093 }
094 }
095
096 public void run()
097 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( new OutputStreamWriter( new FileOutputStream( outDir + "/"
111 + outFile ),
112 "ISO-8859-1" ) );
113
114 ShapeFile sf = new ShapeFile( fileList.get( i ) );
115
116 // delete table if already exists
117 fos.write( "drop table " + sf.getFeatureByRecNo( 1 ).getFeatureType().getName() + ";" );
118 fos.newLine();
119
120 // get createtable sql statement and write it to the file
121 String createTable = getCreateTableStatement( sf.getFeatureByRecNo( 1 ).getFeatureType() );
122 fos.write( createTable );
123 fos.newLine();
124
125 String tableName = sf.getFeatureByRecNo( 1 ).getFeatureType().getName().getPrefixedName().toUpperCase();
126
127 LOG.logInfo( "write to file: " + outDir + "/" + outFile );
128 // create an insert statement for each feature conained in
129 // the shapefile
130 for ( int j = 0; j < sf.getRecordNum(); j++ ) {
131 if ( j % 50 == 0 )
132 System.out.print( "." );
133
134 StringBuffer names = new StringBuffer( "(" );
135 StringBuffer values = new StringBuffer( " VALUES (" );
136
137 Feature feature = sf.getFeatureByRecNo( j + 1 );
138 FeatureType ft = feature.getFeatureType();
139 PropertyType ftp[] = ft.getProperties();
140 boolean gm = false;
141 for ( int k = 0; k < ftp.length; k++ ) {
142 Object o = feature.getProperties( ftp[i].getName() )[0];
143 if ( o != null ) {
144 QualifiedName name = ftp[k].getName();
145 String value = null;
146 if ( o instanceof Geometry ) {
147 value = WKTAdapter.export( (Geometry) o ).toString();
148 value = "GeomFromText('" + value + "')";
149 gm = true;
150 } else {
151 value = o.toString();
152 }
153 names.append( name.getPrefixedName() );
154 if ( ftp[k].getType() == Types.VARCHAR || ftp[k].getType() == Types.CHAR ) {
155 value = StringTools.replace( value, "'", "\\'", true );
156 value = StringTools.replace( value, "\"", "\\\"", true );
157 values.append( "'" + value + "'" );
158 } else {
159 values.append( value );
160 }
161 if ( k < ftp.length - 1 ) {
162 names.append( "," );
163 values.append( "," );
164 }
165 }
166 }
167
168 if ( !gm ) {
169 LOG.logInfo( "" + names );
170 continue;
171 }
172 names.append( ")" );
173 values.append( ")" );
174
175 fos.write( "INSERT INTO " + tableName + " " );
176 fos.write( names.toString() );
177 fos.write( values.toString() + ";" );
178 fos.newLine();
179 }
180 sf.close();
181 fos.write( "ALTER TABLE " + tableName + " ADD SPATIAL INDEX(GEOM);" );
182 fos.write( "commit;" );
183 fos.newLine();
184 fos.close();
185 }
186
187 LOG.logInfo( "finished!" );
188
189 }
190
191 /**
192 * creates a create table sql statement from the passed <tt>FeatureType</tt>
193 *
194 * @param ft
195 * feature type
196 * @return the created SQL statement
197 */
198 private String getCreateTableStatement( FeatureType ft ) {
199
200 StringBuffer sb = new StringBuffer();
201 String name = ft.getName().getPrefixedName();
202
203 PropertyType[] ftp = ft.getProperties();
204
205 sb.append( "CREATE TABLE " ).append( name ).append( " (" );
206 for ( int i = 0; i < ftp.length; i++ ) {
207 sb.append( ftp[i].getName() ).append( " " );
208 int type = ftp[i].getType();
209 if ( type == Types.VARCHAR ) {
210 sb.append( " VARCHAR(255) " );
211 } else if ( type == Types.DOUBLE ) {
212 sb.append( " DOUBLE(20,8) " );
213 } else if ( type == Types.INTEGER ) {
214 sb.append( " INT(12) " );
215 } else if ( type == Types.FLOAT ) {
216 sb.append( " DOUBLE(20,8) " );
217 } else if ( type == Types.DATE ) {
218 sb.append( " Date " );
219 } else if ( type == Types.GEOMETRY || type == Types.POINT || type == Types.CURVE || type == Types.SURFACE
220 || type == Types.MULTIPOINT || type == Types.MULTICURVE || type == Types.MULTISURFACE ) {
221 sb.append( " GEOMETRY NOT NULL" );
222 }
223 if ( i < ftp.length - 1 ) {
224 sb.append( "," );
225 }
226 }
227 sb.append( ");" );
228
229 return sb.toString();
230 }
231
232 /**
233 * prints out helping application-information.
234 *
235 * @param n
236 * an integer parameter, which determines which help-information should be given out.
237 */
238 private static void usage( int n ) {
239 switch ( n ) {
240 case 0:
241 System.out.println( "usage: java -classpath .;deegree.jar de.tools.Shp2MySQL "
242 + " [-f shapefile -d sourcedirectory]\n"
243 + " [--version] [--help]\n" + "\n" + "arguments:\n"
244 + " -f shapefile reads the input shapefile. must be set\n"
245 + " if -d is not set.\n"
246 + " -d inputdir name of the directory that contains the.\n"
247 + " source shapefiles. must be set if -f is\n"
248 + " not set.\n" + "\n" + "information options:\n"
249 + " --help shows this help.\n"
250 + " --version shows the version and exits.\n" );
251 break;
252 case 1:
253 System.out.println( "Try 'java -classpath .;deegree.jar de.tools.Shp2MySQL --help'\n"
254 + "for more information." );
255 break;
256
257 default:
258 System.out.println( "Unknown usage: \n" + "Try 'java -classpath .;deegree.jar de.tools.Shp2MySQL --help'\n"
259 + "for more information." );
260 break;
261 }
262 }
263
264 /**
265 * @param args
266 * the command line arguments
267 */
268 public static void main( String[] args )
269 throws Exception {
270
271 if ( args == null || args.length < 2 ) {
272 usage( 0 );
273 System.exit( 1 );
274 }
275
276 HashMap<String, String> map = new HashMap<String, String>();
277
278 for ( int i = 0; i < args.length; i += 2 ) {
279 map.put( args[i], args[i + 1] );
280 }
281
282 if ( map.get( "--help" ) != null ) {
283 usage( 0 );
284 System.exit( 0 );
285 }
286
287 if ( map.get( "--version" ) != null ) {
288 System.out.println( "Shp2MySQL version 1.0.0" );
289 System.exit( 0 );
290 }
291
292 // one single file shall be transformed
293 if ( map.get( "-f" ) != null ) {
294 String f = map.get( "-f" );
295 if ( f.toUpperCase().endsWith( ".SHP" ) ) {
296 f = f.substring( 0, f.length() - 4 );
297 }
298 Shp2MySQL shp = new Shp2MySQL( f );
299 shp.run();
300 } else {
301 // the files of a whole directory shall be transformed
302 }
303
304 }
305 }