01: package com.vividsolutions.jump.datastore.postgis;
02:
03: import com.vividsolutions.jts.geom.Coordinate;
04: import com.vividsolutions.jts.geom.Envelope;
05: import com.vividsolutions.jts.geom.Geometry;
06: import com.vividsolutions.jump.datastore.FilterQuery;
07: import com.vividsolutions.jump.datastore.SpatialReferenceSystemID;
08:
09: /**
10: * Creates SQL query strings for a PostGIS database
11: */
12: public class PostgisSQLBuilder {
13: private SpatialReferenceSystemID defaultSRID = null;
14: private String[] colNames = null;
15:
16: public PostgisSQLBuilder(SpatialReferenceSystemID defaultSRID,
17: String[] colNames) {
18: this .defaultSRID = defaultSRID;
19: this .colNames = colNames;
20: }
21:
22: public String getSQL(FilterQuery query) {
23: return buildQueryString(query);
24: }
25:
26: private String buildQueryString(FilterQuery query) {
27: StringBuffer qs = new StringBuffer();
28: //HACK
29: qs.append("SELECT ");
30: qs.append(getColumnListSpecifier(colNames, query
31: .getGeometryAttributeName()));
32: qs.append(" FROM ");
33: qs.append(query.getDatasetName());
34: qs.append(" t WHERE ");
35: // srid = 1042102
36: qs.append(buildBoxFilter(query.getGeometryAttributeName(),
37: query.getSRSName(), query.getFilterGeometry()));
38:
39: String whereCond = query.getCondition();
40: if (whereCond != null) {
41: qs.append(" AND ");
42: qs.append(whereCond);
43: }
44: //System.out.println(qs);
45: return qs.toString();
46: }
47:
48: private String buildBoxFilter(String geometryColName,
49: SpatialReferenceSystemID SRID, Geometry geom) {
50: Envelope env = geom.getEnvelopeInternal();
51:
52: // Example of Postgis SQL: GEOM && SetSRID('BOX3D(191232 243117,191232 243119)'::box3d,-1);
53: StringBuffer buf = new StringBuffer();
54: buf.append(geometryColName + " && SetSRID('BOX3D(");
55: buf.append(env.getMinX() + " " + env.getMinY() + ","
56: + env.getMaxX() + " " + env.getMaxY());
57: buf.append(")'::box3d,");
58: buf.append(getSRID(SRID) + ")");
59: return buf.toString();
60: }
61:
62: private String getSRID(SpatialReferenceSystemID querySRID) {
63: SpatialReferenceSystemID srid = defaultSRID;
64: if (!querySRID.isNull())
65: srid = querySRID;
66:
67: if (srid.isNull())
68: return "NULL";
69: else
70: return srid.getString();
71: }
72:
73: private String getColumnListSpecifier(String[] colName,
74: String geomColName) {
75: // Added double quotes around each column name in order to read mixed case table names
76: // correctly [mmichaud 2007-05-13]
77: StringBuffer buf = new StringBuffer();
78: buf.append("AsBinary(\"" + geomColName + "\") as "
79: + geomColName + "_wkb");
80: for (int i = 0; i < colName.length; i++) {
81: if (!geomColName.equalsIgnoreCase(colName[i])) {
82: buf.append(",\"");
83: buf.append(colName[i]).append("\"");
84: }
85: }
86: return buf.toString();
87: }
88: }
|