Using PostGIS "contains(geom1, geom2)" inside Views

I have been working on integrating Views and PostGIS, specifically the areas of spatial containment and spatial aggregates.

Recently, I have made some pretty big strides in understanding how this might work in Views, and thought I would share my findings here. If containment has recently entered the core and does not require coding any longer, forgive me. If not, I think that this investigation may lay the foundation for making spatial containment (and later spatial aggregates) automagically created inside the Views UI. For the time being this is hard coded to add containment WHERE clause and ON additions, but the path now seems rather straightforward to me. This approach was developed with the help of some folks over on the forums (https://drupal.org/node/2154761).

Objective: I am doing a "spatial join" using two geometry columns (a point and a polygon) to determine if two separate nodes intersect in the same space. I have the geometries stored in separate tables.

Method: I have created a custom moduleto insert the desired spatial conditions to the queries constructed in a specific View. My module name is "wsp_devel", though this is immaterial.

Background Reading: This approach was based largely on the "Node Example" custom module defined here: http://views-help.doc.logrus.com/help/views/api-example - The information in that tutorial can be used to tell Views how to automatically join tables with geometry columns as well using the spatial conditions demonstrated below.

Tables:

• 'node' - My base table
• 'data_node_point' - a table with a NID that refers and has a point geometry (basically a latitude and longitude)
• 'data_node_coverage' - A table with an entity_id (also a nid reference) and a multi-polygon geometry (a 2 dimensional spatial area)
• 'wsp_sysloc_cache' -

this is another table that has information in it that is joined in views but it is not used in the spatial part of the query (but it shows up in the SQL excerpt below)

• Both 'data_node_point' and 'data_node_coverage' have a dummy column 'status' which is always 1 in order to provide an initial join condition because it seems that the 'join' requires something non-null there (maybe I am wrong, but no harm occurs I suppose).

Code Steps:

1. I join the point table 'data_node_point' to 'node' via matching 'nid' columns - I use the function "$query->addRelationship" to enable me to join to the table 'data_node_point' later.
2. I add columns from 'data_node_point' - 'nid' and 'the_geom'
3. I join the table 'data_node_coverage' to 'data_node_point' via a spatial containment operator - if the lat/lon in 'data_node_point' lies inside of the boundaries of 'data_node_coverage' then I include it.
4. I screen for null geometries which would indicate that the original node has no entry in the point table or no overlapping coverage.

Custom Module Code views_query_alter

function wsp_devel_views_query_alter(&$view, &$query) {
   
   if ($view->name=="wsp_coverage_systems") {       
      $join = new views_join;
      $join->construct('data_node_point','node', 'nid', 'nid');
      $res = $query->add_relationship('data_node_point', $join, 'data_node_point', 'node');
      $res = $query->add_table('data_node_coverage', 'node');
      $res = $query->add_field('data_node_point', 'nid');
      $res = $query->add_field('data_node_point', 'the_geom');
      
      $join = new views_join;
      $geojoin = 'data_node_coverage.the_geom && data_node_point.the_geom';
      $geojoin .= ' AND contains(data_node_coverage.the_geom, data_node_point.the_geom )';
      $join->construct('data_node_coverage', 'data_node_point','status', 'status', $geojoin);
      $res = $query->add_table('data_node_coverage', 'data_node_point', $join, 'data_node_coverage');
      $res = $query->add_field('data_node_coverage', 'entity_id');
      $res = $query->add_field('data_node_coverage', 'the_geom');
      
      if (isset($view->args[0])) {
         $query->add_where_expression(0, 'data_node_coverage.entity_id = ' . $view->args[0]);
      }
      $query->add_where_expression(0, 'data_node_point.the_geom is not null');
      $query->add_where_expression(0, 'data_node_point.the_geom is not null');
   }
   
}

The query that results (when passing in a nid for a single coverage node):

SELECT node.title AS node_title, node.nid AS nid, wsp_sysloc_cache.proj_use_mgy AS wsp_sysloc_cache_proj_use_mgy, node.created AS node_created, data_node_point.nid AS data_node_point_nid, data_node_point.the_geom AS data_node_point_the_geom, data_node_coverage.entity_id AS data_node_coverage_entity_id, data_node_coverage.the_geom AS data_node_coverage_the_geom, 'node' AS field_data_field_annual_mg_node_entity_type
FROM 
node as node
LEFT JOIN wsp_sysloc_cache as wsp_sysloc_cache ON node.nid = wsp_sysloc_cache.system_nid
LEFT JOIN data_node_point as data_node_point ON node.nid = data_node_point.nid
LEFT JOIN data_node_coverage as data_node_coverage ON data_node_point.status = data_node_coverage.status AND (data_node_coverage.the_geom && data_node_point.the_geom AND contains(data_node_coverage.the_geom, data_node_point.the_geom ))
WHERE (( (data_node_point.the_geom is not null) AND (data_node_point.the_geom is not null) )AND(( (node.status = '1') AND (node.type IN  ('wsp_ssu_agricultural', 'wsp_ssu_non_agricultural', 'wsp_ssu_non_ag_under_300k_gal_mo', 'water_supply_plan_data_test')) )))
ORDER BY node_created DESC