| 1 |
<?php // $Id: postgis.inc,v 1.11 2008/09/25 21:51:33 vauxia Exp $
|
| 2 |
|
| 3 |
/**
|
| 4 |
* Postgresql + PostGIS backend for the geo field module
|
| 5 |
*
|
| 6 |
* @author Mark Fredrickson
|
| 7 |
* @version $Id: postgis.inc,v 1.11 2008/09/25 21:51:33 vauxia Exp $
|
| 8 |
* @package geo
|
| 9 |
*/
|
| 10 |
|
| 11 |
/*** API Functions ***/
|
| 12 |
|
| 13 |
/**
|
| 14 |
* Adds a geometry column to the specified table.
|
| 15 |
*
|
| 16 |
* @param $table
|
| 17 |
* The name of the table to which to add the column. Do not include braces ({}).
|
| 18 |
* @param $field_name
|
| 19 |
* The name of the field to use.
|
| 20 |
* @param $type
|
| 21 |
* The OpenGIS type of the column.
|
| 22 |
* Valid types are: POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION
|
| 23 |
* @param $srid
|
| 24 |
* The Spatial Reference ID of this column's projection. The most commonly used SRID is GEO_SRID_DEFAULT, which corresponds to unprojected lat-long in WGS84.
|
| 25 |
* @return boolean
|
| 26 |
* Success or failure
|
| 27 |
*/
|
| 28 |
function geo_postgis_add_field(&$ret, $table, $field, $spec) {
|
| 29 |
// NOTE for now, all geometries are 2d. deal with it
|
| 30 |
// TODO is there a way to get the current schema? until i figure this out, everything is public
|
| 31 |
$ret[] = db_query("SELECT AddGeometryColumn('public', '{". $table ."}', '%s', %d, '%s', 2);", $field, $spec['srid'], strtoupper($spec['type']));
|
| 32 |
// TODO perhaps send the message returned from the db to watchdog?
|
| 33 |
// if successful, add a spatial index on the field
|
| 34 |
$ret[] = db_query("CREATE INDEX {". $table ."}_". $field ."_idx ON {". $table ."} USING GIST ($field GIST_GEOMETRY_OPS)");
|
| 35 |
return true;
|
| 36 |
}
|
| 37 |
|
| 38 |
/**
|
| 39 |
* Remove a geometry column from the specified table.
|
| 40 |
*
|
| 41 |
* @param $table
|
| 42 |
* The name of the table from which to drop the column. Do not include braces ({}).
|
| 43 |
* @param $field_name
|
| 44 |
* The name of the field to drop.
|
| 45 |
*
|
| 46 |
* @return boolean
|
| 47 |
* Success or failure
|
| 48 |
*
|
| 49 |
*/
|
| 50 |
function geo_postgis_drop_field($table, $column) {
|
| 51 |
$res = db_query("SELECT DropGeometryColumn('public', '{%s}', '%s'", $table, $column);
|
| 52 |
// NOTE do I need to drop my spatial index too? should probably check pg docs
|
| 53 |
}
|
| 54 |
|
| 55 |
/*
|
| 56 |
function geo_postgis_field_select($field) {
|
| 57 |
return " srid($field) as srid, asText($field) AS wkt, asText(centroid($field)) AS centroid, asText(envelope($field)) as bbox ";
|
| 58 |
}
|
| 59 |
*/
|
| 60 |
|
| 61 |
function geo_postgis_query_function($func, $field, $value, $srid) {
|
| 62 |
$ret = array();
|
| 63 |
$value = "ST_Transform(GeomFromText('$value', $srid), SRID($field))";
|
| 64 |
|
| 65 |
// These relations should have overlapping bounding boxes, so use an index here.
|
| 66 |
if (in_array($func, array('Equals', 'Within', 'Intersects'))) {
|
| 67 |
$ret[] = "$value && $field";
|
| 68 |
}
|
| 69 |
|
| 70 |
$ret[] = "$func($value, $field)";
|
| 71 |
return $ret;
|
| 72 |
}
|
| 73 |
|
| 74 |
/*** Helper functions ***/
|
| 75 |
|
| 76 |
/**
|
| 77 |
* Return a list of fields, keyed by table, of geo tables.
|
| 78 |
*
|
| 79 |
* @param $table
|
| 80 |
* an optional string of the table name to look in
|
| 81 |
*
|
| 82 |
* @return array
|
| 83 |
* an array of fields, keyed by table
|
| 84 |
*/
|
| 85 |
function geo_postgis_tables($exclude = NULL) {
|
| 86 |
static $tables;
|
| 87 |
|
| 88 |
if (!is_array($tables)) {
|
| 89 |
$tables = array();
|
| 90 |
|
| 91 |
// Query for all available geometry columns.
|
| 92 |
$res = db_query("SELECT c.relname AS table, a.attname AS field
|
| 93 |
FROM pg_catalog.pg_attribute a
|
| 94 |
LEFT JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
|
| 95 |
WHERE pg_catalog.format_type(a.atttypid, a.atttypmod) = 'geometry'
|
| 96 |
AND c.relname != 'geometry_dump'
|
| 97 |
ORDER BY c.relname, a.attnum");
|
| 98 |
|
| 99 |
while ($row = db_fetch_object($res)) {
|
| 100 |
// Ignore exclusions.
|
| 101 |
if ($exclude && preg_match($exclude, $row->table)) continue;
|
| 102 |
|
| 103 |
if (!isset($tables[$row->table])) {
|
| 104 |
$tables[$row->table] = array();
|
| 105 |
}
|
| 106 |
$tables[$row->table][] = $row->field;
|
| 107 |
}
|
| 108 |
}
|
| 109 |
return $tables;
|
| 110 |
}
|
| 111 |
|
| 112 |
/**
|
| 113 |
* Parse out the table descriptions from the geometry information stored in the table.
|
| 114 |
*/
|
| 115 |
function geo_postgis_table_desc($table) {
|
| 116 |
$res = db_query("SELECT a.attname AS name,
|
| 117 |
col_description(c.oid, a.attnum) AS description,
|
| 118 |
format_type(a.atttypid, a.atttypmod) AS type,
|
| 119 |
a.attnotnull AS not_null,
|
| 120 |
d.adsrc AS default
|
| 121 |
FROM pg_class c
|
| 122 |
LEFT JOIN pg_attribute a ON (a.attrelid = c.oid) LEFT OUTER JOIN pg_attrdef d ON (d.adrelid = c.oid AND d.adnum = a.attnum)
|
| 123 |
WHERE c.relname = '%s'
|
| 124 |
AND a.attnum > 0
|
| 125 |
AND NOT a.attisdropped
|
| 126 |
AND a.attrelid = c.oid
|
| 127 |
ORDER BY a.attnum", $table);
|
| 128 |
|
| 129 |
$columns = array();
|
| 130 |
$geo = array();
|
| 131 |
while ($row = db_fetch_array($res)) {
|
| 132 |
if ($row['type'] == 'geometry') {
|
| 133 |
$geo[] = $row['name'];
|
| 134 |
}
|
| 135 |
|
| 136 |
$columns[$row['name']] = $row;
|
| 137 |
$columns[$row['name']]['not_null'] = (bool) str_replace('f', '', $row['not_null']);
|
| 138 |
}
|
| 139 |
|
| 140 |
$res = db_query("SELECT c.relname AS table,
|
| 141 |
pg_catalog.pg_get_constraintdef(r.oid, true) AS constraint
|
| 142 |
FROM pg_catalog.pg_constraint r
|
| 143 |
LEFT JOIN pg_catalog.pg_class c ON r.conrelid = c.oid
|
| 144 |
WHERE c.relname = '%s' AND r.contype = 'c'", $table);
|
| 145 |
|
| 146 |
/*
|
| 147 |
Find constraints that are similar to the ones here, and set attributes
|
| 148 |
|
| 149 |
CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL)
|
| 150 |
CHECK (ndims(the_geom) = 2)
|
| 151 |
CHECK (srid(the_geom) = 26915)
|
| 152 |
*/
|
| 153 |
while ($row = db_fetch_object($res)) {
|
| 154 |
$table = $row->table;
|
| 155 |
foreach ($geo as $f) {
|
| 156 |
if (strpos($row->constraint, "ndims($f)")) {
|
| 157 |
$columns[$f]['dimensions'] = preg_replace('/\D/', '', $row->constraint);
|
| 158 |
}
|
| 159 |
elseif (strpos($row->constraint, "srid($f)")) {
|
| 160 |
$columns[$f]['srid'] = preg_replace('/\D/', '', $row->constraint);
|
| 161 |
}
|
| 162 |
elseif (strpos($row->constraint, "geometrytype($f)")) {
|
| 163 |
$columns[$f]['geometry_type'] = preg_replace('/.*\'(\w+)\'.*/', '$1', $row->constraint);
|
| 164 |
}
|
| 165 |
}
|
| 166 |
}
|
| 167 |
return $columns;
|
| 168 |
}
|
| 169 |
|
| 170 |
// provide a quick way to get the SRID of a column and use it in the storage
|
| 171 |
function _geo_fromtext($string, $table, $field) {
|
| 172 |
$srid = db_result(db_query("SELECT srid FROM {gis_sources}
|
| 173 |
WHERE table = '%s' AND field = '%s'"));
|
| 174 |
|
| 175 |
return "GeomFromText('$string', $srid)";
|
| 176 |
}
|
| 177 |
|
| 178 |
// Point to latlon
|
| 179 |
function _geo_latlonfrompoint($wkt, $srid = GEO_SRID_DEFAULT) {
|
| 180 |
// Yep. Not efficient.
|
| 181 |
return array(
|
| 182 |
'lat' => db_result(db_query("SELECT X(GeomFromText('%s', %d))", $wkt, $srid)),
|
| 183 |
'lon' => db_result(db_query("SELECT Y(GeomFromText('%s', %d))", $wkt, $srid)),
|
| 184 |
);
|
| 185 |
}
|
| 186 |
|
| 187 |
// returns the PostGIS version information
|
| 188 |
function geo_postgis_version() {
|
| 189 |
return db_result(db_query("SELECT postgis_version()"));
|
| 190 |
}
|
Parent Directory
| 
Revision Log
| 
Revision Graph