001: package com.vividsolutions.jump.datastore.postgis;
002:
003: import java.sql.ResultSet;
004: import java.sql.SQLException;
005: import java.util.ArrayList;
006: import java.util.HashMap;
007: import java.util.List;
008: import java.util.Map;
009:
010: import com.vividsolutions.jts.geom.Envelope;
011: import com.vividsolutions.jts.geom.Geometry;
012: import com.vividsolutions.jts.io.WKBReader;
013: import com.vividsolutions.jump.datastore.DataStoreMetadata;
014: import com.vividsolutions.jump.datastore.SpatialReferenceSystemID;
015: import com.vividsolutions.jump.datastore.jdbc.JDBCUtil;
016: import com.vividsolutions.jump.datastore.jdbc.ResultSetBlock;
017:
018: public class PostgisDSMetadata implements DataStoreMetadata {
019:
020: private final WKBReader reader = new WKBReader();
021:
022: private PostgisDSConnection conn;
023:
024: private Map sridMap = new HashMap();
025:
026: public PostgisDSMetadata(PostgisDSConnection conn) {
027: this .conn = conn;
028: }
029:
030: public String[] getDatasetNames() {
031: final List datasetNames = new ArrayList();
032: // Spatial tables only.
033: JDBCUtil
034: .execute(
035: conn.getConnection(),
036: "SELECT DISTINCT f_table_schema, f_table_name FROM geometry_columns",
037: new ResultSetBlock() {
038: public void yield(ResultSet resultSet)
039: throws SQLException {
040: while (resultSet.next()) {
041: String schema = resultSet
042: .getString(1);
043: String table = resultSet
044: .getString(2);
045: if (!schema
046: .equalsIgnoreCase("public")) {
047: table = schema + "." + table;
048: }
049: datasetNames.add(table);
050: }
051: }
052: });
053: return (String[]) datasetNames.toArray(new String[] {});
054: }
055:
056: public Envelope getExtents(String datasetName, String attributeName) {
057: final Envelope[] e = new Envelope[] { null };
058: //
059: // Use find_extent - sometimes estimated_extent was returning null
060: //
061: String sql = "SELECT AsBinary(find_extent( '" + datasetName
062: + "', '" + attributeName + "' ))";
063:
064: JDBCUtil.execute(conn.getConnection(), sql,
065: new ResultSetBlock() {
066: public void yield(ResultSet resultSet)
067: throws Exception {
068: if (resultSet.next()) {
069: byte[] bytes = (byte[]) resultSet
070: .getObject(1);
071: if (bytes != null) {
072: Geometry geom = reader.read(bytes);
073: if (geom != null) {
074: e[0] = geom.getEnvelopeInternal();
075: }
076: }
077: }
078: }
079: });
080: return e[0];
081: }
082:
083: public SpatialReferenceSystemID getSRID(String tableName,
084: String colName) throws SQLException {
085: String key = tableName + "#" + colName;
086: if (!sridMap.containsKey(key)) {
087: // not in cache, so query it
088: String srid = querySRID(tableName, colName);
089: sridMap.put(key, new SpatialReferenceSystemID(srid));
090: }
091: SpatialReferenceSystemID srid = (SpatialReferenceSystemID) sridMap
092: .get(key);
093: return srid;
094: }
095:
096: private String querySRID(String tableName, String colName) {
097: final StringBuffer srid = new StringBuffer();
098: String sql = "SELECT getsrid(" + colName + ") FROM "
099: + tableName + " LIMIT 1";
100:
101: JDBCUtil.execute(conn.getConnection(), sql,
102: new ResultSetBlock() {
103: public void yield(ResultSet resultSet)
104: throws SQLException {
105: if (resultSet.next()) {
106: srid.append(resultSet.getString(1));
107: }
108: }
109: });
110:
111: return srid.toString();
112: }
113:
114: public String[] getGeometryAttributeNames(String datasetName) {
115: final List geometryAttributeNames = new ArrayList();
116: String sql = "SELECT f_geometry_column FROM geometry_columns "
117: + geomColumnMetadataWhereClause("f_table_schema",
118: "f_table_name", datasetName);
119: JDBCUtil.execute(conn.getConnection(), sql,
120: new ResultSetBlock() {
121: public void yield(ResultSet resultSet)
122: throws SQLException {
123: while (resultSet.next()) {
124: geometryAttributeNames.add(resultSet
125: .getString(1));
126: }
127: }
128: });
129: return (String[]) geometryAttributeNames
130: .toArray(new String[] {});
131: }
132:
133: public String[] getColumnNames(String datasetName) {
134: String sql = "SELECT column_name FROM information_schema.columns "
135: + geomColumnMetadataWhereClause("table_schema",
136: "table_name", datasetName);
137: ColumnNameBlock block = new ColumnNameBlock();
138: JDBCUtil.execute(conn.getConnection(), sql, block);
139: return block.colName;
140: }
141:
142: private String geomColumnMetadataWhereClause(String schemaCol,
143: String tableCol, String tableName) {
144: int dotPos = tableName.indexOf(".");
145: return dotPos == -1 ? "WHERE lower(" + tableCol + ") = '"
146: + tableName.toLowerCase() + "'" : "WHERE lower("
147: + schemaCol + ") = '"
148: + tableName.substring(0, dotPos).toLowerCase() + "' "
149: + " AND lower(" + tableCol + ") = '"
150: + tableName.substring(dotPos + 1).toLowerCase() + "'";
151: }
152:
153: private static class ColumnNameBlock implements ResultSetBlock {
154: List colList = new ArrayList();
155: String[] colName;
156:
157: public void yield(ResultSet resultSet) throws SQLException {
158: while (resultSet.next()) {
159: colList.add(resultSet.getString(1));
160: }
161: colName = (String[]) colList.toArray(new String[0]);
162: }
163: }
164: }
|