#!/usr/bin/env python """ BeginDate: 20040907 CurrentRevisionDate:20040921 Development Version : core 001 Release Version: pre-release Author(s): Mishtu Banerjee, Tyler Mitchell Contact: mishtu@harmeny.com Copyright: The Authors License: Distributed under MIT License [http://opensource.org/licenses/mit-license.html] Environment: Programmed and tested under Python 2.3.3 on Windows 2000. Database Access: Psycopg and Postgres 7.4 Dependencies: Python Interpreter and base libraries. unittest Xayacore_001 ----------------------------------------------------- Test first development of spatial joins where clause Test Cases: 1. Named tables do not exist. 2. Our Test Example ----------------------- FROM: http://postgis.refractions.net/docs/ch04.html#id2512215 4.6.2.4.6.2.1.5. Create a new table with all the roads within the city of Prince George. This is an example of an "overlay", which takes in two tables and outputs a new table that consists of spatially clipped or cut resultants. Unlike the "spatial join" demonstrated above, this query actually creates new geometries. An overlay is like a turbo-charged spatial join, and is useful for more exact analysis work: postgis=# CREATE TABLE pg_roads as SELECT intersection(r.the_geom, m.the_geom) AS intersection_geom, length(r.the_geom) AS rd_orig_length, r.* FROM bc_roads AS r, bc_municipality AS m WHERE r.the_geom && m.the_geom AND intersects(r.the_geom, m.the_geom) AND m.name = 'PRINCE GEORGE'; Our Second Test Example (from postgis-introduction.doc available at http://www.maptools.org//dl/omsug/osgis2004/ ) postgis=# select h.name, p.name from bc_hospitals h, bc_pubs p where distance(h.the_geom, p.the_geom) < 250; Our Third Test Example (modified from postgis-introduction pg 24 by Refractions) postgis=# SELECT bc_pubs.name, bc_pubs.city from bc_pubs, bc_voting_areas WHERE bc_voting_areas.the_geom && bc_pubs.the_geom AND DISTANCE(bc_voting_areas.the_geom, bc_pubs.the_geom) < 500 ; name | city | ----------------+-----------+ Bimini's | Vancouver | Darby D. Dawes | Vancouver | (2 rows) Our Fourth Test Example, paraphrased from the postgis Documentation: SELECT area(the_geom)/10000 AS hectares FROM bc_municipality; """ import unittest import xayadb import os class SQLtests(unittest.TestCase): def setUp(self): self.geomGraph1 = {'roads': { 'SpatialColumn': 'the_geom', 'SpatialType': 'Line'}, 'municipality' : { 'SpatialColumn': 'the_geom', 'SpatialType': 'Polygon'} } self.geomGraph2 = {'FC': { 'SpatialColumn': 'TheGeom', 'SpatialType': 'Polygon'}, 'VRI' : { 'SpatialColumn': 'VRIGeom', 'SpatialType': 'Polygon'} } self.geomGraph3 = {'pubs' : { 'SpatialColumn' : 'the_geom', 'SpatialType' : 'Point'}, 'hospitals' : { 'SpatialColumn' : 'the_geom', 'SpatialType' : 'Point'}} self.geomGraph4 = {'bc_pubs' : { 'SpatialColumn' : 'the_geom', 'SpatialType' : 'Point'}, 'bc_voting_areas' : { 'SpatialColumn' : 'the_geom', 'SpatialType' : 'Point'}} self.spatialJoinsGraph1 = { 'Intersects': ['roads', 'municipality']} self.spatialJoinsGraph2 = { 'Intersects': ['FC', 'VRI']} self.spatialJoinsGraph3 = {'foo': ['roads', 'municipality']} self.spatialJoinsGraph4 = {'Distance': ['hospitals', 'pubs', '<', 250]} self.spatialJoinsGraph5 = {'Distance' : ['bc_voting_areas', 'bc_pubs', '<', 500]} self.columnsGraph1 = {'bc_pubs' : ['name', 'city']} self.pathlist1 = ['bc_pubs', 'bc_voting_areas'] self.SQLString1 = 'SELECT bc_pubs.name,bc_pubs.city FROM bc_pubs , bc_voting_areas WHERE bc_voting_areas.the_geom && bc_pubs.the_geom AND DISTANCE(bc_voting_areas.the_geom, bc_pubs.the_geom) < 500' self.SQLTokenList1 = ['SELECT', 'bc_pubs.name', ',', 'bc_pubs.city', 'FROM', 'bc_pubs', ',', 'bc_voting_areas', 'WHERE', 'bc_voting_areas.the_geom', '&&', 'bc_pubs.the_geom', 'AND', 'DISTANCE', '(', 'bc_voting_areas.the_geom', ',', 'bc_pubs.the_geom', ')', '<', '500'] self.pathList2 = ['bc_municipalities'] self.columnsGraph2 = {'bc_municipalities' : ['name']} self.expressionsList1 = ['area(bc_municipalities.the_geom)/10000 AS hectares'] self.constraintsList1 = [{'Operator': '=', 'ColumnName': 'name', 'TableName': 'bc_municipalities', 'Values': ["Prince George"]}] self.columnsGraph3 = {'bc_municipality' : ['name'], 'bc_roads' : ['road_length']} self.geomGraph5 = {'bc_roads' : { 'SpatialColumn' : 'the_geom', 'SpatialType' : 'Point'}, 'bc_municipality' : { 'SpatialColumn' : 'the_geom', 'SpatialType' : 'Polygon'}} self.spatialJoinsGraph6 = { 'contains': ['bc_municipality', 'bc_roads'] } self.aggregateFunctionsList1 = [{'Aggregate': 'sum', 'ColumnName': 'road_length', 'TableName': 'bc_roads'}] def test_001_MissingInputs(self): """ test_001_MissingInputs tests case where there is missing input data into the spatial where clause function """ self.assertEquals(xayadb._whereSpatialJoinsClause({}, {}), "") self.assertEquals(xayadb._whereSpatialJoinsClause(self.geomGraph2, {}), "") self.assertRaises(xayadb.xayadbSQLError, xayadb._whereSpatialJoinsClause,{}, self.spatialJoinsGraph2) def test_002_NormalCase(self): """ test_002_NormalCase: Tests a typical 'overlay' or intersection example """ self.assertEquals(xayadb.tokenizeSQLString(xayadb._whereSpatialJoinsClause(self.geomGraph1, self.spatialJoinsGraph1)), xayadb.tokenizeSQLString('roads.the_geom && municipality.the_geom AND INTERSECTS(roads.the_geom, municipality.the_geom)')) def test_003_IncorrectSpatialObjects(self): """ test_003_IncorrectSpatialObjects """ self.assertRaises(xayadb.xayadbSQLError, xayadb._whereSpatialJoinsClause,self.geomGraph1, self.spatialJoinsGraph3) def test_004_DistancePredicate(self): """ test_004_DistancePredicate: 'Tests standard use of Distance and a comparison operator """ self.assertEquals(xayadb.tokenizeSQLString(xayadb._whereSpatialJoinsClause(self.geomGraph3, self.spatialJoinsGraph4)), xayadb.tokenizeSQLString('hospitals.the_geom && pubs.the_geom AND DISTANCE(hospitals.the_geom, pubs.the_geom) < 250')) def test_005_genSQL_DistancePredicate(self): """ """ self.assertEquals(xayadb.tokenizeSQLString(xayadb.genSQL( columnsGraph = self.columnsGraph1, spatialJoinsGraph = self.spatialJoinsGraph5, geomGraph = self.geomGraph4)), xayadb.tokenizeSQLString('SELECT bc_pubs.name,bc_pubs.city FROM bc_pubs , bc_voting_areas WHERE bc_voting_areas.the_geom && bc_pubs.the_geom AND DISTANCE(bc_voting_areas.the_geom, bc_pubs.the_geom) < 500')) def test_006_tokenizeSQLString(self): """ """ self.assertEquals(xayadb.tokenizeSQLString(self.SQLString1), self.SQLTokenList1) def test_007_arbitraryExpressionsInSQL(self): """ """ self.assertEquals(xayadb.genSQL(columnsGraph = self.columnsGraph2, pathList = self.pathList2, expressionsList = self.expressionsList1, constraintsList = self.constraintsList1), "SELECT bc_municipalities.name,area(bc_municipalities.the_geom)/10000 AS hectares FROM bc_municipalities WHERE ( (bc_municipalities.name='Prince George') )") def test_008_aggregateFunctions(self): """ """ self.assertEquals(xayadb.genSQL(columnsGraph = self.columnsGraph3, geomGraph = self.geomGraph5, spatialJoinsGraph = self.spatialJoinsGraph6, aggregateFunctionsList = self.aggregateFunctionsList1), "SELECT sum(bc_roads.road_length),bc_municipality.name FROM bc_roads , bc_municipality WHERE bc_municipality.the_geom && bc_roads.the_geom AND CONTAINS(bc_municipality.the_geom, bc_roads.the_geom) GROUP BY bc_municipality.name") if __name__ == '__main__': unittest.main()