java/dbloader/src/main/java/be/bosa/dt/best/dbloader/PostGisLoader.java
/**
	 * Create spatial index and update statistics
	 * 
	 * @throws SQLException 
	 */
Create spatial index and update statistics


Create spatial index and update statistics
private void createIndex() throws SQLException {
		try(Connection conn = getConnection()) {
			Statement stmt = conn.createStatement();
			LOG.info("Set spatial index");			
			stmt.execute("CREATE INDEX idxAddressPoint ON Address USING GIST(point)");
			LOG.info("Set foreign key idexes");			
			stmt.execute("CREATE INDEX idxAddressMunicipality ON Address(mIdentifier)");
			stmt.execute("CREATE INDEX idxAddressPostal ON Address(pIdentifier)");
			stmt.execute("CREATE INDEX idxAddressStreet ON Address(sIdentifier)");
			stmt.execute("CREATE INDEX idxStreetMunicipality ON Street(mIdentifier)");
			LOG.info("Set street text indexes");				
			stmt.execute("CREATE INDEX idxGinStreetNL ON Street USING GIN(nameNL gin_trgm_ops)");
			stmt.execute("CREATE INDEX idxGinStreetFR ON Street USING GIN(nameFR gin_trgm_ops)");
			stmt.execute("CREATE INDEX idxGinStreetDE ON Street USING GIN(nameDE gin_trgm_ops)");
			LOG.info("Set Municipality text indexes");				
			stmt.execute("CREATE INDEX idxGinMunicipalityNL ON Municipality USING GIN(nameNL gin_trgm_ops)");
			stmt.execute("CREATE INDEX idxGinMunicipalityFR ON Municipality USING GIN(nameFR gin_trgm_ops)");
			stmt.execute("CREATE INDEX idxGinMunicipalityDE ON Municipality USING GIN(nameDE gin_trgm_ops)");
			LOG.info("Update statistics");
			stmt.execute("VACUUM FULL ANALYZE");
		}
	}
private void createIndex ( ) throws SQLException { try ( Connection conn = getConnection ( ) ) { Statement stmt = conn . createStatement ( ) ; LOG . info ( "Set spatial index" ) ; stmt . execute ( "CREATE INDEX idxAddressPoint ON Address USING GIST(point)" ) ; LOG . info ( "Set foreign key idexes" ) ; stmt . execute ( "CREATE INDEX idxAddressMunicipality ON Address(mIdentifier)" ) ; stmt . execute ( "CREATE INDEX idxAddressPostal ON Address(pIdentifier)" ) ; stmt . execute ( "CREATE INDEX idxAddressStreet ON Address(sIdentifier)" ) ; stmt . execute ( "CREATE INDEX idxStreetMunicipality ON Street(mIdentifier)" ) ; LOG . info ( "Set street text indexes" ) ; stmt . execute ( "CREATE INDEX idxGinStreetNL ON Street USING GIN(nameNL gin_trgm_ops)" ) ; stmt . execute ( "CREATE INDEX idxGinStreetFR ON Street USING GIN(nameFR gin_trgm_ops)" ) ; stmt . execute ( "CREATE INDEX idxGinStreetDE ON Street USING GIN(nameDE gin_trgm_ops)" ) ; LOG . info ( "Set Municipality text indexes" ) ; stmt . execute ( "CREATE INDEX idxGinMunicipalityNL ON Municipality USING GIN(nameNL gin_trgm_ops)" ) ; stmt . execute ( "CREATE INDEX idxGinMunicipalityFR ON Municipality USING GIN(nameFR gin_trgm_ops)" ) ; stmt . execute ( "CREATE INDEX idxGinMunicipalityDE ON Municipality USING GIN(nameDE gin_trgm_ops)" ) ; LOG . info ( "Update statistics" ) ; stmt . execute ( "VACUUM FULL ANALYZE" ) ; } }
Create spatial index and update statistics
Create spatial index and update statistics