geo/db/mysql_spatial.inc

<?php // $Id: mysql_spatial.inc,v 1.12 2009/05/30 04:21:15 vauxia Exp $

/**
 * @file
 * MySQL Spatial Extensions backend for the geo field module
 *
 * @package geo
 */

/*** API Functions ***/

/**
 * Adds a geometry column to the specified table.
 * 
 * @param $table
 *   The name of the table to which to add the column. Do not include braces ({}).
 * @param $field_name
 *   The name of the field to use.
 * @param $type
 *   The OpenGIS type of the column.
 *   Valid types are: POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION
 * @param $srid
 *   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.
 * @return boolean
 * Success or failure
 */
function geo_mysql_spatial_add_field(&$ret, $table, $field, $spec) {
  // NOTE for now, all geometries are 2d. deal with it
  $ret[] = db_query("ALTER TABLE {%s} ADD %s %s NOT NULL", $table, $field, $spec['type']);
  db_query("CREATE SPATIAL INDEX {$table}_${field}_idx ON  {". $table ."} ($field)");

  // @@@ Error handling
  return true;
}

/**
 * Remove a geometry column from the specified table.
 *
 * @param $table
 *   The name of the table from which to drop the column.
 * @param $field_name
 *   The name of the field to drop.
 *
 * @return boolean
 *  Success or failure
 */
function geo_mysql_spatial_drop_field($table, $column) {
  db_query("ALTER TABLE {%s} DROP COLUMN %s", $table, $column);
  return true;
}

function geo_mysql_spatial_query_function($func, $field, $value, $srid) {
  return array("$func(GeomFromText('$value', $srid), $field)");
}

/**
 * Calculate the distance using the Pythagorean theorem.  It's cheaper than
 * the spherical versions, but less accurate.
 * TODO only works for points - the X() and Y() functions should be supplanted
 *      by something that's cognizant of other geometries.
 */
function geo_mysql_spatial_query_distance($field, $srid, $point) {
  // Point data.
  $x = $point['lon'];
  $y = $point['lat'];

  // Offsets, in meters.
  $mod_x = GEO_DEGREE_M * cos($y / 57.2958);
  $mod_y = GEO_DEGREE_M; 

  return "SQRT(POW(($mod_x * (X($field) - $x)), 2) + POW(($mod_y * (Y($field) - $y)), 2))";
}

/**
 * Calculate the distance using the Great Circle Distance Formula.
 * TODO only works for points - the X() and Y() functions should be supplanted
 *      by something that's cognizant of other geometries.
 */
function geo_mysql_spatial_query_distance_sphere($field, $srid, $point) {
  // Radius of the earth in meters.
  $r = 6370986;

  // Degree offset.
  $d = 57.2958;

  // Point data.
  $x = $point['lon'];
  $y = $point['lat'];

  // Offsets, in meters.
  $ysin = sin($y / $d);
  $ycos = cos($y / $d);
  $xd   = $x/$d;

  return "($r * ACOS($ysin * SIN(Y($field)/$d) + $ycos * COS(Y($field)/$d) * COS(X($field)/$d - $xd)))";
}

/**
 * Aggregate function that returns the total boundary of a result set.
 */
function mysql_spatial_geo_query_extent($field) {
  return "MIN(X(PointN(ExteriorRing(envelope($field)), 1))) AS minx, 
    MAX(X(PointN(ExteriorRing(envelope($field)), 3))) AS maxx, 
    MIN(Y(PointN(ExteriorRing(Envelope($field)), 1))) AS miny, 
    MAX(Y(PointN(ExteriorRing(Envelope($field)), 3))) AS maxy";
}

/*** Helper functions ***/

/**
 * Return a list of fields, keyed by table, of geo tables.
 * 
 * @param $table
 *  an optional string of the table name to look in
 *
 * @return array
 *  an array of fields, keyed by table
 */
function geo_mysql_spatial_tables($exclude = NULL) {
  static $tables;

  if (!is_array($tables)) {
    $tables = array();

    // Query for all available geometry columns.
    // @@@ Mysql5 + only.
    $res = db_query("SELECT TABLE_NAME AS 'table', COLUMN_NAME AS 'field'
      FROM information_schema.columns
      WHERE TABLE_SCHEMA = database()
      AND DATA_TYPE IN ('GEOMETRY','POINT','LINESTRING','POLYGON','MULTIPOINT',
      'MULTILINESTRING','MULTIPOLYGON','GEOMETRYCOLLECTION')
      ORDER BY TABLE_NAME, COLUMN_NAME");

    while ($row = db_fetch_object($res)) {
      // Ignore exclusions.
      if ($exclude && preg_match($exclude, $row->table)) continue;

      if (!isset($tables[$row->table])) {
        $tables[$row->table] = array();
      }
      $tables[$row->table][] = $row->field;
    }
  }
  return $tables;
}

/**
 * Parse out the table descriptions from the geometry information stored in the table.
 */
function geo_mysql_spatial_table_desc($table) {
  $res = db_query("SELECT COLUMN_NAME AS 'name',
    COLUMN_COMMENT AS 'description',
    DATA_TYPE AS 'type',
    IS_NULLABLE AS 'null_ok',
    COLUMN_DEFAULT AS 'default'
    FROM information_schema.columns
    WHERE TABLE_SCHEMA = database()
    AND TABLE_NAME = '%s' ORDER BY ORDINAL_POSITION", $table);

  $columns = array();
  $geo = array();
  while ($row = db_fetch_array($res)) {
    if ($row['type'] == 'geometry') {
      $geo[] = $row['name'];
    }

    $columns[$row['name']] = $row;
    $columns[$row['name']]['not_null'] = $row['null_ok']=='YES' ? FALSE : TRUE;
    unset($columns[$row['name']]['null_ok']);
  }

  // @@@ I have no clue what this section is for. ~Bdragon

/*  $res = db_query("SELECT c.relname AS table, 
      pg_catalog.pg_get_constraintdef(r.oid, true) AS constraint
    FROM pg_catalog.pg_constraint r
    LEFT JOIN pg_catalog.pg_class c ON r.conrelid = c.oid
    WHERE c.relname = '%s' AND r.contype = 'c'", $table);
*/

  /*
   Find constraints that are similar to the ones here, and set attributes

   CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL)
   CHECK (ndims(the_geom) = 2)
   CHECK (srid(the_geom) = 26915)
  */
  //while ($row = db_fetch_object($res)) {
  //  $table = $row->table;
  //  foreach($geo as $f) {
  //    if (strpos($row->constraint, "ndims($f)" )) {
  //      $columns[$f]['dimensions'] = preg_replace('/\D/','', $row->constraint);
  //    }
  //    elseif (strpos($row->constraint, "srid($f)" )) {
  //      $columns[$f]['srid'] = preg_replace('/\D/','', $row->constraint);
  //    }
  //    elseif (strpos($row->constraint, "geometrytype($f)" )) {
  //      $columns[$f]['geometry_type'] = preg_replace('/.*\'(\w+)\'.*/','$1', $row->constraint);
  //    }
  //  }
  //}

  return $columns;
}

// provide a quick way to get the SRID of a column and use it in the storage
function _geo_fromtext($string, $table, $field) {
  $srid = db_result(db_query("SELECT srid FROM {gis_sources} 
    WHERE table = '%s' AND field = '%s'"));

  return "GeomFromText('$string', $srid)";
}

// Point to latlon
function _geo_latlonfrompoint($wkt, $srid = GEO_SRID_DEFAULT) {
  // Yep. Not efficient.
  return array(
    'lat' => db_result(db_query("SELECT X(GeomFromText('%s', %d))", $wkt, $srid)),
    'lon' => db_result(db_query("SELECT Y(GeomFromText('%s', %d))", $wkt, $srid)),
  );
}

// returns the PostGIS version information
function geo_mysql_spatial_version() {
  // No clue what to do here...
  // return db_result(db_query("SELECT postgis_version()"));
}
1	<?php // $Id: mysql_spatial.inc,v 1.12 2009/05/30 04:21:15 vauxia Exp $
2
3	/**
4	* @file
5	* MySQL Spatial Extensions backend for the geo field module
6	*
7	* @package geo
8	*/
9
10	/* API Functions */
11
12	/**
13	* Adds a geometry column to the specified table.
14	*
15	* @param $table
16	* The name of the table to which to add the column. Do not include braces ({}).
17	* @param $field_name
18	* The name of the field to use.
19	* @param $type
20	* The OpenGIS type of the column.
21	* Valid types are: POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION
22	* @param $srid
23	* 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.
24	* @return boolean
25	* Success or failure
26	*/
27	function geo_mysql_spatial_add_field(&$ret, $table, $field, $spec) {
28	// NOTE for now, all geometries are 2d. deal with it
29	$ret[] = db_query("ALTER TABLE {%s} ADD %s %s NOT NULL", $table, $field, $spec['type']);
30	db_query("CREATE SPATIAL INDEX {$table}_${field}_idx ON {". $table ."} ($field)");
31
32	// @@@ Error handling
33	return true;
34	}
35
36	/**
37	* Remove a geometry column from the specified table.
38	*
39	* @param $table
40	* The name of the table from which to drop the column.
41	* @param $field_name
42	* The name of the field to drop.
43	*
44	* @return boolean
45	* Success or failure
46	*/
47	function geo_mysql_spatial_drop_field($table, $column) {
48	db_query("ALTER TABLE {%s} DROP COLUMN %s", $table, $column);
49	return true;
50	}
51
52	function geo_mysql_spatial_query_function($func, $field, $value, $srid) {
53	return array("$func(GeomFromText('$value', $srid), $field)");
54	}
55
56	/**
57	* Calculate the distance using the Pythagorean theorem. It's cheaper than
58	* the spherical versions, but less accurate.
59	* TODO only works for points - the X() and Y() functions should be supplanted
60	* by something that's cognizant of other geometries.
61	*/
62	function geo_mysql_spatial_query_distance($field, $srid, $point) {
63	// Point data.
64	$x = $point['lon'];
65	$y = $point['lat'];
66
67	// Offsets, in meters.
68	$mod_x = GEO_DEGREE_M * cos($y / 57.2958);
69	$mod_y = GEO_DEGREE_M;
70
71	return "SQRT(POW(($mod_x * (X($field) - $x)), 2) + POW(($mod_y * (Y($field) - $y)), 2))";
72	}
73
74	/**
75	* Calculate the distance using the Great Circle Distance Formula.
76	* TODO only works for points - the X() and Y() functions should be supplanted
77	* by something that's cognizant of other geometries.
78	*/
79	function geo_mysql_spatial_query_distance_sphere($field, $srid, $point) {
80	// Radius of the earth in meters.
81	$r = 6370986;
82
83	// Degree offset.
84	$d = 57.2958;
85
86	// Point data.
87	$x = $point['lon'];
88	$y = $point['lat'];
89
90	// Offsets, in meters.
91	$ysin = sin($y / $d);
92	$ycos = cos($y / $d);
93	$xd = $x/$d;
94
95	return "($r * ACOS($ysin * SIN(Y($field)/$d) + $ycos * COS(Y($field)/$d) * COS(X($field)/$d - $xd)))";
96	}
97
98	/**
99	* Aggregate function that returns the total boundary of a result set.
100	*/
101	function mysql_spatial_geo_query_extent($field) {
102	return "MIN(X(PointN(ExteriorRing(envelope($field)), 1))) AS minx,
103	MAX(X(PointN(ExteriorRing(envelope($field)), 3))) AS maxx,
104	MIN(Y(PointN(ExteriorRing(Envelope($field)), 1))) AS miny,
105	MAX(Y(PointN(ExteriorRing(Envelope($field)), 3))) AS maxy";
106	}
107
108	/* Helper functions */
109
110	/**
111	* Return a list of fields, keyed by table, of geo tables.
112	*
113	* @param $table
114	* an optional string of the table name to look in
115	*
116	* @return array
117	* an array of fields, keyed by table
118	*/
119	function geo_mysql_spatial_tables($exclude = NULL) {
120	static $tables;
121
122	if (!is_array($tables)) {
123	$tables = array();
124
125	// Query for all available geometry columns.
126	// @@@ Mysql5 + only.
127	$res = db_query("SELECT TABLE_NAME AS 'table', COLUMN_NAME AS 'field'
128	FROM information_schema.columns
129	WHERE TABLE_SCHEMA = database()
130	AND DATA_TYPE IN ('GEOMETRY','POINT','LINESTRING','POLYGON','MULTIPOINT',
131	'MULTILINESTRING','MULTIPOLYGON','GEOMETRYCOLLECTION')
132	ORDER BY TABLE_NAME, COLUMN_NAME");
133
134	while ($row = db_fetch_object($res)) {
135	// Ignore exclusions.
136	if ($exclude && preg_match($exclude, $row->table)) continue;
137
138	if (!isset($tables[$row->table])) {
139	$tables[$row->table] = array();
140	}
141	$tables[$row->table][] = $row->field;
142	}
143	}
144	return $tables;
145	}
146
147	/**
148	* Parse out the table descriptions from the geometry information stored in the table.
149	*/
150	function geo_mysql_spatial_table_desc($table) {
151	$res = db_query("SELECT COLUMN_NAME AS 'name',
152	COLUMN_COMMENT AS 'description',
153	DATA_TYPE AS 'type',
154	IS_NULLABLE AS 'null_ok',
155	COLUMN_DEFAULT AS 'default'
156	FROM information_schema.columns
157	WHERE TABLE_SCHEMA = database()
158	AND TABLE_NAME = '%s' ORDER BY ORDINAL_POSITION", $table);
159
160	$columns = array();
161	$geo = array();
162	while ($row = db_fetch_array($res)) {
163	if ($row['type'] == 'geometry') {
164	$geo[] = $row['name'];
165	}
166
167	$columns[$row['name']] = $row;
168	$columns[$row['name']]['not_null'] = $row['null_ok']=='YES' ? FALSE : TRUE;
169	unset($columns[$row['name']]['null_ok']);
170	}
171
172	// @@@ I have no clue what this section is for. ~Bdragon
173
174	/* $res = db_query("SELECT c.relname AS table,
175	pg_catalog.pg_get_constraintdef(r.oid, true) AS constraint
176	FROM pg_catalog.pg_constraint r
177	LEFT JOIN pg_catalog.pg_class c ON r.conrelid = c.oid
178	WHERE c.relname = '%s' AND r.contype = 'c'", $table);
179	*/
180
181	/*
182	Find constraints that are similar to the ones here, and set attributes
183
184	CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL)
185	CHECK (ndims(the_geom) = 2)
186	CHECK (srid(the_geom) = 26915)
187	*/
188	//while ($row = db_fetch_object($res)) {
189	// $table = $row->table;
190	// foreach($geo as $f) {
191	// if (strpos($row->constraint, "ndims($f)" )) {
192	// $columns[$f]['dimensions'] = preg_replace('/\D/','', $row->constraint);
193	// }
194	// elseif (strpos($row->constraint, "srid($f)" )) {
195	// $columns[$f]['srid'] = preg_replace('/\D/','', $row->constraint);
196	// }
197	// elseif (strpos($row->constraint, "geometrytype($f)" )) {
198	// $columns[$f]['geometry_type'] = preg_replace('/.\'(\w+)\'./','$1', $row->constraint);
199	// }
200	// }
201	//}
202
203	return $columns;
204	}
205
206	// provide a quick way to get the SRID of a column and use it in the storage
207	function _geo_fromtext($string, $table, $field) {
208	$srid = db_result(db_query("SELECT srid FROM {gis_sources}
209	WHERE table = '%s' AND field = '%s'"));
210
211	return "GeomFromText('$string', $srid)";
212	}
213
214	// Point to latlon
215	function _geo_latlonfrompoint($wkt, $srid = GEO_SRID_DEFAULT) {
216	// Yep. Not efficient.
217	return array(
218	'lat' => db_result(db_query("SELECT X(GeomFromText('%s', %d))", $wkt, $srid)),
219	'lon' => db_result(db_query("SELECT Y(GeomFromText('%s', %d))", $wkt, $srid)),
220	);
221	}
222
223	// returns the PostGIS version information
224	function geo_mysql_spatial_version() {
225	// No clue what to do here...
226	// return db_result(db_query("SELECT postgis_version()"));
227	}