mhall  committed ac3c7b0

Added an MSComparator. Improved the documentation and added to the core documentation. Added tests for the MS specific functionality.

Comments (0)

Files changed (5)

File doc/reference/index.rst Modified

View file
  • Ignore whitespace
  • Hide word diff
    mysql
    spatialite
    oracle
+   mssql
    

File doc/reference/mssql.rst Added

View file
  • Ignore whitespace
  • Hide word diff
+geoalchemy.mssql
+================
+
+.. automodule:: geoalchemy.mssql
+   :members:

File doc/usagenotes.rst Modified

View file
  • Ignore whitespace
  • Hide word diff
 Oracle objects as argument in queries, so ``DBSpatialElement`` can not be used for Oracle.
 
 
+Notes for MS Sql Server
+-----------------------
+
+The MS Sql Server spatial support has been tested using MS SQL Server 2008, connecting to it via pyODBC on Windows.
+
+There is one important difference between SQL Server 2008 spatial support and PostGIS in that it is **not** possible
+to restrict the spatial column to a specific type of geometry. All columns will be :class:`geoalchemy.geometry.Geometry`.
+
+:class:`geoalchemy.base.DBSpatialElement` issues
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+In some cases when using a :class:`~geoalchemy.base.DBSpatialElement` to store a geometry in the database-specific
+format between queries an error will appear when the :class:`~geoalchemy.base.DBSpatialElement` is used next.
+The error message is "Explicit conversion from data type image to TNAGazetteer.sys.geometry is not allowed". This
+is a known problem and a fix is being submitted to SqlAlchemy to correct this problem. In the meantime to store
+geometries between queries use :class:`~geoalchemy.base.WKBSpatialElement` or :class:`~geoalchemy.base.WKTSpatialElement`.
+
+Supported functions
+~~~~~~~~~~~~~~~~~~~
+
+Most of the standard functions defined in GeoAlchemy are available and work as expected, but there are a few exceptions:
+
+* g.centroid -- Only returns results for :class:`~geoalchemy.geometry.Polygon`
+  and :class:`~geoalchemy.geometry.MultiPolygon`. Returns 'NULL' for all
+  other :class:`~geoalchemy.geometry.Geometry`
+* g.envelope -- Will always return a :class:`~geoalchemy.geometry.Polygon`
+  regardless of the type of :class:`~geoalchemy.geometry.Geometry` it
+  was called on
+* g.buffer -- Only supports the buffer distance as a parameter
+* g.transform -- Not defined
+* g.within_distance -- Not defined
+* g.covers -- Not defined
+* g.covers_by -- Not defined
+* g.intersection -- Not defined
+
+MS Sql Server specific functions
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+Sql Server provides a number of additional spatial functions, details of which can be found in the documentation of
+:class:`geoalchemy.mssql.ms_functions`. These additional functions can be used like any other function, or via
+`ms_functions.function_name`.
+
+.. code-block:: python
+
+    session.query(Road).filter(Road.road_geom.instance_of('LINESTRING'))
+    
+    from geoalchemy.mssql import ms_functions
+    ms_functions.buffer_with_tolerance('POINT(-88.5945861592357 42.9480095987261)', 10, 2, 0)
+    
+* :class:`~geoalchemy.mssql.ms_functions.text_zm`
+* :class:`~geoalchemy.mssql.ms_functions.buffer_with_tolerance`
+* :class:`~geoalchemy.mssql.ms_functions.filter`
+* :class:`~geoalchemy.mssql.ms_functions.instance_of`
+* :class:`~geoalchemy.mssql.ms_functions.m`
+* :class:`~geoalchemy.mssql.ms_functions.make_valid`
+* :class:`~geoalchemy.mssql.ms_functions.reduce`
+* :class:`~geoalchemy.mssql.ms_functions.to_string`
+* :class:`~geoalchemy.mssql.ms_functions.z`
+
+Creating a spatial index
+~~~~~~~~~~~~~~~~~~~~~~~~
+
+Sql Server requires a bounding box that is equal to the extent of the data in the indexed spatial column.
+As the necessary information is not available when the DDL statements are executed no spatial indexes
+are created by default. To create a spatial index the bounding box must be specified explicitly when the
+:class:`~geoalchemy.geometry.GeometryColumn` is defined:
+
+.. code-block:: python
+
+    class Road(Base):
+       __tablename__ = 'ROADS'
+   
+       road_id = Column(Integer, primary_key=True)
+       road_name = Column(String(255))
+       road_geom = GeometryColumn(Geometry(2, bounding_box='(xmin=-180, ymin=-90, xmax=180, ymax=90)'), nullable=False)
+
+Inserting NULL geometries
+~~~~~~~~~~~~~~~~~~~~~~~~~
+
+Due to a bug in the underlying libraries there is currently no support for inserting NULL geometries that have a `None`
+geometry. The following code will not work:
+
+.. code-block:: python
+
+    session.add(Road(road_name=u'Destroyed road', road_geom=None))
+
+To insert NULL you must use :data:`geoalchemy.mssql.MS_SPATIAL_NULL` to explicitly specify the NULL geometry.
+
+.. code-block:: python
+
+    session.add(Road(road_name=u'Destroyed road', road_geom=MS_SPATIAL_NULL))
+
+This is an issue with pyODBC and is being investigated.
+
+
 Notes on non-declarative mapping
 --------------------------------
 

File geoalchemy/mssql.py Modified

View file
  • Ignore whitespace
  • Hide word diff
 # -*- coding: utf-8 -*-
 
+import warnings
 from sqlalchemy import func, cast, exc
-import warnings
-from sqlalchemy.dialects.mssql import VARBINARY
+from sqlalchemy.dialects.mssql.base import MSVarBinary
 from sqlalchemy.sql.expression import text
+from geoalchemy.base import WKBSpatialElement, WKTSpatialElement, PersistentSpatialElement, DBSpatialElement, SpatialComparator
 from geoalchemy.dialect import SpatialDialect
-from geoalchemy.base import WKBSpatialElement, WKTSpatialElement, PersistentSpatialElement, DBSpatialElement
+from geoalchemy.functions import functions, BaseFunction
 from geoalchemy.geometry import Geometry
-from geoalchemy.functions import functions, BaseFunction
 
 u"""
 :mod:`geoalchemy.mssql` -- MS SQL Server 2008 Spatial Dialect
 use :data:`geoalchemy.mssql.MS_SPATIAL_NULL` to explicitly insert NULL
 geometries.
 
-There are also occasional problems with using :class:`~geoalchemy.base.DBSpatialElement`
-directly in queries. Most will work, but occasionally an error will occur
-indicating that a cast "from image to geometry" is not possible. This bug is
-being investigated, in the meantime only pass WKB or WKT geometries between
-queries.
+It is not possible to restrict the geometry columns to a specific geometry
+type.
 
 .. moduleauthor:: Mark Hall <Mark.Hall@nationalarchives.gov.uk>
 """
 """There is a bug causing errors when trying to insert None values into
 nullable columns. Use this constant instead."""
 
+class MSComparator(SpatialComparator):
+    """Comparator class used for MS SQL Server 2008
+    """
+    def __getattr__(self, name):
+        try:
+            return SpatialComparator.__getattr__(self, name)
+        except AttributeError:
+            return getattr(ms_functions, name)(self)
+
 class MSPersistentSpatialElement(PersistentSpatialElement):
     """Represents a Geometry as loaded from an MS SQL Server 2008 database.
     """
     this function guarantees that SQL Server knows the data is a geometry.
     """
     def function_handler(params, within_column_clause):
+        #return cast(cast(params[0], MSVarBinary('MAX')), Geometry)
         return cast(params[0], Geometry)
     
     return function_handler
         """
         pass
     
-    class instance_of():
-        """g.instance_of(geometry_type_name)
+    class instance_of(BaseFunction):
+        """g.InstanceOf(geometry_type_name)
         
         Tests whether the geometry is of the given geometry type.
         """
         pass
     
-    class m():
+    class m(BaseFunction):
         """p.M
         
         Returns the M value for the given :class:`~geoalchemy.geometry.Point`.
         """
         pass
     
-    class make_valid():
+    class make_valid(BaseFunction):
         """g.MakeValid()
         
         Converts an invalid :class:`~geoalchemy.geometry.Geometry` into a
         """
         pass
     
-    class reduce():
+    class reduce(BaseFunction):
         """g.Reduce(tolerance)
         
         Returns an approximation of the :class:`~geoalchemy.geometry.Geometry`
         """
         pass
     
-    class to_string():
+    class to_string(BaseFunction):
         """g.ToString()
         
         Equivalent to :class:`~geoalchemy.mssql.ms_functions.text_zm` except
         """
         pass
     
-    class z():
+    class z(BaseFunction):
         """p.Z
         
         Returns the M value for the given :class:`~geoalchemy.geometry.Point`.
       was called on
     * g.buffer -- Only supports the buffer distance as a parameter
     
+    Some standard functions are not available:
+    
+    * g.transform
+    * g.within_distance
+    * g.covers
+    * g.covers_by
+    * g.intersection
+    
     For SQL Server 2008 specific functions see :class:`~geoalchemy.mssql.ms_functions`.
     """
     __functions = {
                    functions.covers : None,
                    functions.covered_by : None,
                    functions.intersection : None,
-                   ms_functions.gml : 'AsGml'
+                   functions.is_valid : BooleanFunction(func.STIsValid),
+                   ms_functions.gml : 'AsGml',
+                   ms_functions.text_zm : 'AsTextZM',
+                   ms_functions.buffer_with_tolerance : 'BufferWithTolerance',
+                   ms_functions.filter : BooleanFunction(func.Filter),
+                   ms_functions.instance_of : BooleanFunction(func.InstanceOf),
+                   ms_functions.m : 'M',
+                   ms_functions.make_valid : 'MakeValid',
+                   ms_functions.reduce : 'Reduce',
+                   ms_functions.to_string : 'ToString',
+                   ms_functions.z : 'Z'
                   }
     
     __member_functions = (
                           functions.within,
                           functions.overlaps,
                           functions.gcontains,
-                          ms_functions.gml
+                          functions.is_valid,
+                          ms_functions.gml,
+                          ms_functions.text_zm,
+                          ms_functions.buffer_with_tolerance,
+                          ms_functions.filter,
+                          ms_functions.instance_of,
+                          ms_functions.make_valid,
+                          ms_functions.reduce,
+                          ms_functions.to_string
                          )
     
     __properties = (
                     functions.srid,
                     functions.x,
-                    functions.y
+                    functions.y,
+                    ms_functions.m,
+                    ms_functions.z
                    )
     
     def _get_function_mapping(self):
                 bind.execute("CREATE SPATIAL INDEX [%s_%s] ON [%s].[%s]([%s]) WITH (BOUNDING_BOX = %s)" %
                              (table.name, column.name, table.schema or 'dbo', table.name, column.name, column.type.kwargs["bounding_box"]))
             else:
-                warnings.warn("No bounding_box given for '[%s].[%s].[%s]', no entry in USER_SDO_GEOM_METADATA will be made "\
-                              "and no spatial index will be created." % (table.schema or 'dbo', table.name, column.name), 
+                warnings.warn("No bounding_box given for '[%s].[%s].[%s]' no spatial index will be created." %
+                              (table.schema or 'dbo', table.name, column.name), 
                               exc.SAWarning, stacklevel=3)
             
     def is_member_function(self, function_class):

File geoalchemy/tests/test_mssql.py Modified

View file
  • Ignore whitespace
  • Hide word diff
 from sqlalchemy.orm import sessionmaker, mapper
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy.exceptions import IntegrityError
-from geoalchemy import GeometryColumn, Geometry, GeometryDDL, GeometryExtensionColumn, GeometryCollection, DBSpatialElement, WKTSpatialElement, WKBSpatialElement
+from geoalchemy import GeometryColumn, Geometry, LineString, Polygon, GeometryDDL, GeometryExtensionColumn, GeometryCollection, DBSpatialElement, WKTSpatialElement, WKBSpatialElement
 from geoalchemy.functions import functions
-from geoalchemy.mssql import MS_SPATIAL_NULL
+from geoalchemy.mssql import MS_SPATIAL_NULL, ms_functions, MSComparator
 from unittest import TestCase
 from nose.tools import eq_, ok_, raises, assert_almost_equal
 
 
     road_id = Column(Integer, primary_key=True)
     road_name = Column(String(255))
-    road_geom = GeometryColumn(Geometry(2, bounding_box='(xmin=-180, ymin=-90, xmax=180, ymax=90)'), nullable=False)
+    road_geom = GeometryColumn(LineString(2, bounding_box='(xmin=-180, ymin=-90, xmax=180, ymax=90)'), comparator=MSComparator, nullable=False)
 
 class Lake(Base):
     __tablename__ = 'lakes'
 
     lake_id = Column(Integer, primary_key=True)
     lake_name = Column(String(255))
-    lake_geom = GeometryColumn(Geometry(2))
+    lake_geom = GeometryColumn(Polygon(2), comparator=MSComparator)
 
 spots_table = Table('spots', metadata,
                     Column('spot_id', Integer, primary_key=True),
 
         
 mapper(Spot, spots_table, properties={
-            'spot_location': GeometryColumn(spots_table.c.spot_location)}) 
+            'spot_location': GeometryColumn(spots_table.c.spot_location, comparator=MSComparator)}) 
                          
 class Shape(Base):
     __tablename__ = 'shapes'
         session.rollback()
         #metadata.drop_all()
     
-    
     def test_geometry_type(self):
         r = session.query(Road).get(1)
         l = session.query(Lake).get(1)
         road_null = Road(road_name='Jeff Rd', road_geom=MS_SPATIAL_NULL)
         session.add(road_null)
         session.commit();
+    
+    # Test SQL Server specific functions
+    
+    def test_text_zm(self):
+        engine.execute('INSERT INTO [spots] VALUES(%f, geometry::STGeomFromText(%s, %i))' % (130.23, "'POINT (-88.5945861592357 42.9480095987261 130.23 1)'", 4326))
+        eq_(session.query(Spot.spot_location.text_zm.label('text_zm')).filter(Spot.spot_height==130.23).first().text_zm, u'POINT (-88.5945861592357 42.9480095987261 130.23 1)')
+        eq_(session.query(Spot.spot_location.text_zm.label('text_zm')).filter(Spot.spot_height==420.40).first().text_zm, u'POINT (-88.5945861592357 42.9480095987261)')
+    
+    
+    def test_buffer_with_tolerance(self):
+        r = session.query(Road).filter(Road.road_name=='Graeme Ave').one()
+        assert_almost_equal(session.scalar(functions.area(r.road_geom.buffer_with_tolerance(10.0, 20, 1))), 214.63894668789601)
+        assert_almost_equal(session.scalar(functions.area(r.road_geom.buffer_with_tolerance(10.0, 20, 0))), 214.63894668789601)
+        ok_(session.query(Spot).filter(functions.within('POINT(-88.5945861592357 42.9480095987261)', Spot.spot_location.buffer(10))).first() is not None)
+        assert_almost_equal(session.scalar(functions.area(ms_functions.buffer_with_tolerance('POINT(-88.5945861592357 42.9480095987261)', 10, 2, 0))), 306.21843345678644)
+    
+    def test_filter(self):
+        r1 = session.query(Road).filter(Road.road_name=='Jeff Rd').one()
+        r2 = session.query(Road).filter(Road.road_name=='Graeme Ave').one()
+        r3 = session.query(Road).filter(Road.road_name=='Geordie Rd').one()
+        intersecting_roads = session.query(Road).filter(Road.road_geom.filter(r1.road_geom)).all()
+        ok_(r2 in intersecting_roads)
+        ok_(r3 not in intersecting_roads)
+        eq_(session.scalar(ms_functions.filter('POINT(0 0)', 'LINESTRING ( 2 0, 0 2 )')), False)
+    
+    def test_instance_of(self):
+        ok_(session.query(Road).filter(Road.road_geom.instance_of('LINESTRING')).first() is not None)
+        ok_(session.query(Lake).filter(Lake.lake_geom.instance_of('POLYGON')).first() is not None)
+        ok_(session.query(Spot).filter(Spot.spot_location.instance_of('POINT')).first() is not None)
+    
+    def test_extended_coords(self):
+        engine.execute('INSERT INTO [spots] VALUES(%f, geometry::STGeomFromText(%s, %i))' % (130.23, "'POINT (-88.5945861592357 42.9480095987261 130.23 1)'", 4326))
+        p = session.query(Spot.spot_location.z.label('z'), Spot.spot_location.m.label('m')).filter(Spot.spot_height==130.23).first()
+        eq_(p.z, 130.23)
+        eq_(p.m, 1)
+        p = session.query(Spot.spot_location.z.label('z'), Spot.spot_location.m.label('m')).filter(Spot.spot_height==420.40).first()
+        ok_(p.z is None)
+        ok_(p.m is None)
+    
+    def test_make_valid(self):
+        session.add(Shape(shape_name=u'Invalid Shape', shape_geom=WKTSpatialElement(u'LINESTRING(0 2, 1 1, 1 0, 1 1, 2 2)')))
+        invalid_line = session.query(Shape).filter(Shape.shape_name==u'Invalid Shape').first()
+        eq_(session.scalar(invalid_line.shape_geom.is_valid), 0)
+        invalid_line.shape_geom = DBSpatialElement(session.scalar(invalid_line.shape_geom.make_valid))
+        valid_line = session.query(Shape).filter(Shape.shape_name==u'Invalid Shape').first()
+        eq_(session.scalar(valid_line.shape_geom.is_valid), 1)
+        
+    
+    def test_reduce(self):
+        r = session.query(Road).first()
+        eq_(session.scalar(DBSpatialElement(session.scalar(r.road_geom.reduce(0.5))).wkt),
+            u'LINESTRING (-88.9139332929936 42.5082802993631, -88.3655256496815 43.1402866687898)')
+        eq_(session.scalar(DBSpatialElement(session.scalar(r.road_geom.reduce(0.05))).wkt),
+            u'LINESTRING (-88.9139332929936 42.5082802993631, -88.6113059044586 42.9680732929936, -88.3655256496815 43.1402866687898)')
+        eq_(session.scalar(DBSpatialElement(session.scalar(r.road_geom.reduce(0.0000000000001))).wkt),
+            session.scalar(r.road_geom.wkt))
+        
+    
+    def test_to_string(self):
+        engine.execute('INSERT INTO [spots] VALUES(%f, geometry::STGeomFromText(%s, %i))' % (130.23, "'POINT (-88.5945861592357 42.9480095987261 130.23 1)'", 4326))
+        session.add(Lake(lake_name=u'Vanished lake', lake_geom=MS_SPATIAL_NULL))
+        eq_(session.query(Spot.spot_location.text_zm.label('to_string')).filter(Spot.spot_height==130.23).first().to_string, u'POINT (-88.5945861592357 42.9480095987261 130.23 1)')
+        eq_(session.query(Spot.spot_location.text_zm.label('to_string')).filter(Spot.spot_height==420.40).first().to_string, u'POINT (-88.5945861592357 42.9480095987261)')
+        ok_(session.query(Lake.lake_geom.to_string.label('to_string')).filter(Lake.lake_name==u'Vanished lake').first().to_string is None)