oraclequerybuilder.java

实现网络GPS定位车辆的位置

package MapperClientApplet;
/**
 * Copyright 2000-2002 MapInfo Corporation
 * You are free to use this code in your applications, but you may not
 * redistribute the source code without this notice.
 *
 * 	$Revision: 5 $
 *  $Date: 5/28/02 4:58p $
 */


import java.io.Serializable;
import java.util.Vector;
import java.util.ArrayList;
import java.util.TreeSet;
import java.util.Iterator;
import java.util.Properties;
import java.util.List;

import com.mapinfo.util.DoubleRect;
import com.mapinfo.util.DoublePoint;
import com.mapinfo.dp.QueryParams;
import com.mapinfo.dp.SearchType;
import com.mapinfo.dp.VectorGeometry;
import com.mapinfo.dp.PointList;
import com.mapinfo.dp.Attribute;
import com.mapinfo.dp.PrimaryKey;
import com.mapinfo.dp.TableInfo;
import com.mapinfo.dp.jdbc.SpatialQueryDef;
import com.mapinfo.dp.jdbc.SpatialQueryMetaData;
import com.mapinfo.dp.RenditionType;
import com.mapinfo.dp.AttOperator;
import com.mapinfo.dp.AttTuple;
import com.mapinfo.dp.jdbc.QueryBuilder;
import com.mapinfo.util.GeometryUtils;
import com.mapinfo.coordsys.oraso.OracleSRID;
import com.mapinfo.coordsys.CoordSys;
import com.mapinfo.mapj.MapJ;
import com.mapinfo.mapj.Layer;
import com.mapinfo.unit.LinearUnit;
import com.mapinfo.unit.AngularUnit;
import com.mapinfo.unit.Unit;
import com.mapinfo.unit.UnitUtil;


/**
 *
 * Sample implementation of a QueryBuilder for an Oracle databases version 8.1.6 through 9.0.1.x.x
 * <p>
 * This QueryBuilder builds queries in the following form: <br>
 * SELECT desiredColumns FROM (originalQuery) WHERE conditionIsTrue <br>
 * The original query is executed and the new SELECT and WHERE conditions are
 * applied to the result of the original query.
 * <p>
 * This sample implementation will not necessarily work on all possible valid queries,
 * e.g queries containing user defined functions or generated geometries that
 * are not indexed. This implementation also assumes the SpatialQueryMetaData for
 * the original query will also appply to the new query, e.g. it assumes the name of the
 * spatial column in the result set is the same as the original query.
 * <p>
 * Radius searches are implemented by creating a polygon approximation of the circle and then
 * constructing a region search. Region
 * searches are limited to MAXPOINTS (defined below) vertices in the region. If the region has
 * more, an approximation is made of the original region by just using every Nth point on the
 * region boundary where N is ceiling[totalPoints/MAXPOINTS].
 * <p>
 * This class uses two special workarounds for limitations in Oracle9i, see getSearchRect9().
 * <p>
 * This class is for sample demonstration use only.
 */

public class OracleQueryBuilder implements QueryBuilder, Serializable  {

	private static final String USE_QUOTES = "UseQuotes";
	private static final String QUOTE_CHAR = "QuoteChar";
	private static final String DB_VERSION = "DBVersion";
	private static final String EMPTY_STRING = "";

	private transient SpatialQueryMetaData m_outMetaData = null;      //metadata for new query
	private String m_quoteChar;
	private String m_dbVersion;
	private boolean m_bVerbose = false;
	private String m_mask;

	//Casual testing showed that 499 region points may be the max allowed in a SQL statement
	//but to be sure made MAXPOINTS smaller. A larger number could be attempted.
	private static final int MAXPOINTS = 250;                        //see queryInRegion()

	//Needed to work around bug with geodetic coordinate systems in Oracle 9i.
	//Geodetic coordinate system extents we will allow in Oracle 9i.
	//Experiments showed that five decimal places was the most precision that worked.
	private static final double GXMAX =  179.99999;
	private static final double GYMAX =   89.99999;
	private static final double GXMIN = -179.99999;
	private static final double GYMIN =  -89.99999;
	private static final DoubleRect GEXTENTS = new DoubleRect(GXMAX, GYMAX, GXMIN, GYMIN);

	/**
	 * @param bUseQuotes should identifiers in the query string be quoted
	 * @param quote character to use if bUseQuotes==true
	 * @param dbVersion version of the Oracle database, if null assume 8.1.x
	 */
	public OracleQueryBuilder(boolean bUseQuotes, String quoteChar, String dbVersion) {
		init(bUseQuotes, quoteChar, dbVersion);
	}

	/**
	 * Construct this class from a set of Properties. Used by OracleQueryBuilderXMLHandler.
	 *
	 * @param props the PropertySet returned from getProperties
	 */
	public OracleQueryBuilder(Properties props)
	throws IllegalArgumentException {
		boolean bUseQuotes;
		String quoteChar;
		String dbVersion;
		try {
		  bUseQuotes = Boolean.valueOf(props.getProperty(USE_QUOTES, "false")).booleanValue();
			quoteChar = props.getProperty(QUOTE_CHAR, EMPTY_STRING);
		  dbVersion = props.getProperty(DB_VERSION, null);
			init(bUseQuotes, quoteChar, dbVersion);
		}
		catch (Exception e) {
			throw new IllegalArgumentException("Invalid Properties: " + e.getMessage());
		}
	}

	/**
	 * Get the Properties needed to construct this class. Used by OracleQueryBuilderXMLHandler.
	 *
	 * @return PropertySet to be provided to the constructor OracleQueryBuilder(Properties props);
	 */
	public Properties getProperties() {
		Properties props = new Properties();
		if (m_quoteChar != EMPTY_STRING) {
			props.setProperty(USE_QUOTES, String.valueOf(true));
			props.setProperty(QUOTE_CHAR, m_quoteChar);
		}
		if (m_dbVersion != null) {
		  props.setProperty(DB_VERSION, m_dbVersion);
		}
		return props;
	}

	/**
	 * Enable the objects trace output. For debugging use only.
	 */
	public void setVerbose(boolean bVerbose) {
		m_bVerbose = bVerbose;
	}

	/**
	 * Initialize this object.
	 */
	private void init(boolean bUseQuotes, String quoteChar, String dbVersion){
		if (bUseQuotes) {
			m_quoteChar = quoteChar;
		}
		else {
			m_quoteChar = EMPTY_STRING;
		}
		m_dbVersion = dbVersion;
	}

	/**
	 * Construct a query string to be used when executing a query in rectangle operation.
	 *
	 * @return The SpatialQueryDef defining the new query and its metadata
	 */
	public SpatialQueryDef queryInRectangle(MapJ mapj, Layer layer, SpatialQueryDef queryDef, String[] columnNames,
	                                  QueryParams queryParams, DoubleRect rect)
	throws Exception {

		StringBuffer newQuery = new StringBuffer();
		 //build SELECT clause
		TreeSet selectCols = findRequiredColumns(queryDef, columnNames, queryParams);
		newQuery.append(buildSelectClause(selectCols));
		//add FROM
		newQuery.append(" FROM (" + queryDef.getQuery() + ") ");
		//get SRID
		CoordSys csys = queryDef.getSpatialQueryMetaData().getCoordSys();
		//get WHERE
		newQuery.append(" WHERE ");
		newQuery.append(getSearchMethod(queryParams));
		newQuery.append(m_quoteChar + queryDef.getSpatialQueryMetaData().getGeometryColumn() + m_quoteChar);
		newQuery.append(",");
		//different treatment for geometry conditions for Oracle 8 & 9
		if (isOracle8()) {
			newQuery.append(getSearchRect8(csys, rect));
		}
		else {
			newQuery.append(getSearchRect9(csys, rect));
		}
		newQuery.append(", ");
		//add querytype and mask
		newQuery.append("'QUERYTYPE=WINDOW");
		if (m_mask != null) {
			newQuery.append(" MASK=" + m_mask);
		}
		newQuery.append("')='TRUE')");

		SpatialQueryDef result = new SpatialQueryDef(newQuery.toString(), m_outMetaData);
	  if(m_bVerbose) {
			System.out.println("QueryBuilder new queryInRectangle: " + newQuery);
		}
		return result;
	}

	/**
	 * Construct a query string to be used when executing a query in region operation.
	 *
	 * Regions that have too many vertices to put in a SQL string are approximated.
	 *
	 * @return The SpatialQueryDef defining the new query and its metadata
	 */
	public SpatialQueryDef queryInRegion(MapJ mapj, Layer layer, SpatialQueryDef queryDef, String[] columnNames,
																	QueryParams queryParams, VectorGeometry region)
																	throws Exception {

		StringBuffer newQuery = new StringBuffer();
		 //build SELECT clause
		TreeSet selectCols = findRequiredColumns(queryDef, columnNames, queryParams);
		newQuery.append(buildSelectClause(selectCols));
		//add FROM
		newQuery.append(" FROM (" + queryDef.getQuery() + ") ");
		//get SRID
		CoordSys csys = queryDef.getSpatialQueryMetaData().getCoordSys();
		int srid = OracleSRID.getSRIDFromCS(csys);   //returns 0 if not found
		//get WHERE
		newQuery.append(" WHERE ");
		newQuery.append(getSearchMethod(queryParams));
		newQuery.append(m_quoteChar + queryDef.getSpatialQueryMetaData().getGeometryColumn() + m_quoteChar);
		newQuery.append(",");
		newQuery.append("MDSYS.SDO_GEOMETRY(2003,");
    if (srid != 0) {
      newQuery.append(srid);
    }
    else {
      newQuery.append("null");         //might work?
    }
    newQuery.append(",null,");
		newQuery.append("MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),");
		newQuery.append("MDSYS.SDO_ORDINATE_ARRAY(");

		PointList pl = region.getNextPointList();
		int numPoints = pl.getPointCount();
		int numCoords = numPoints*2;
		double[] coords = new double[numCoords];
		pl.getNextPoints(coords, 0, numPoints);

		//if point list is too large, use approximation
		if (numPoints > MAXPOINTS) {
			int div = numPoints/MAXPOINTS;                         //divisor of original points
			div = numPoints % MAXPOINTS != 0 ? div + 1 : div;      //round up if any remainder
			int numApproxPoints = numPoints/div;
			int numApproxCoords = numApproxPoints * 2;
			double[] approxCoords = new double[numApproxCoords];
			int index = 0;
			//use points at index 0, div, 2*div, 3*div, ...
			for (int k=0; k<numApproxCoords-2;) {
				approxCoords[k++] = coords[index];
				approxCoords[k++] = coords[index + 1];
				index += 2 * div;
			}
			//close approximated region
			approxCoords[numApproxCoords-2] = coords[0];
			approxCoords[numApproxCoords-1] = coords[1];
			coords = approxCoords;
			numCoords = numApproxCoords;
		}

		for (int k=0; k <numCoords; ) {
			//handle first comma
			if (k==0) {
				newQuery.append(coords[k++]).append(",").append(coords[k++]);
			}
			else {
				newQuery.append(",").append(coords[k++]).append(",").append(coords[k++]);
			}

		}
		newQuery.append("))");
		newQuery.append(", ");