root/framework/server/trunk/mapfish/tests/test_oracle.py @ 3610

Revision 3610, 16.8 kB (checked in by elemoine, 3 years ago)

flatten the module hierarchy (removing lib) p=aabt,me, r=aabt,me (closes #590)
  • Property svn:mime-type set to text/plain
Line 
1""" This module includes unit tests for protocol.py using PostGIS as database"""
2
3import unittest
4from nose.tools import eq_, ok_, raises
5
6from sqlalchemy.ext.declarative import declarative_base
7from sqlalchemy import (create_engine, MetaData, Column, Integer, Numeric)
8from sqlalchemy import orm
9
10from geoalchemy import (Point, Polygon, GeometryColumn, GeometryDDL)
11
12from mapfish.sqlalchemygeom import GeometryTableMixIn
13from mapfish.protocol import Protocol, create_geom_filter,\
14    create_default_filter
15from test_protocol import FakeRequest, FakeResponse
16
17from webob.exc import HTTPNotFound
18from exceptions import Exception
19from geojson import Feature, FeatureCollection, GeoJSON
20import os
21from sqlalchemy.schema import Sequence
22from sqlalchemy.sql.expression import text, select
23
24
25os.environ['ORACLE_HOME'] = '/usr/lib/oracle/xe/app/oracle/product/10.2.0/server'
26os.environ['LD_LIBRARY'] = '/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib'
27
28import cx_Oracle
29
30#engine = create_engine('oracle://gis:gis@localhost:1521/gis', echo=True)
31engine = create_engine('oracle://system:system@172.16.103.134:1521/gis', echo=True)
32
33metadata = MetaData(engine)
34sm = orm.sessionmaker(autoflush=True, autocommit=False, bind=engine)
35session = orm.scoped_session(sm)
36
37
38Base = declarative_base(metadata=metadata)
39
40diminfo = "MDSYS.SDO_DIM_ARRAY("\
41            "MDSYS.SDO_DIM_ELEMENT('LONGITUDE', -180, 180, 0.000000005),"\
42            "MDSYS.SDO_DIM_ELEMENT('LATITUDE', -90, 90, 0.000000005)"\
43            ")"
44
45class Spot(Base, GeometryTableMixIn):
46    __tablename__ = 'spots_mf'
47
48    spot_id = Column(Integer, Sequence('spots_mf_id_seq'), primary_key=True)
49    spot_height = Column(Numeric(precision=10, scale=2, asdecimal=False))
50    spot_location = GeometryColumn(Point(2, diminfo=diminfo))
51
52GeometryDDL(Spot.__table__)
53
54class Lake(Base, GeometryTableMixIn):
55    __tablename__ = 'lakes_mf'
56
57    id = Column(Integer, Sequence('lakes_mf_id_seq'), primary_key=True)
58    depth = Column(Numeric(precision=10, scale=2, asdecimal=False))
59    geom = GeometryColumn(Polygon(2, diminfo=diminfo))
60
61GeometryDDL(Lake.__table__)
62
63class Test(unittest.TestCase):
64
65
66    def setUp(self):
67 
68        metadata.drop_all()
69        metadata.create_all()
70
71        # Insert some points into the database
72        session.add_all([
73            Spot(spot_height=420.40, spot_location='POINT(0 0)'),
74            Spot(spot_height=102.34, spot_location='POINT(10 10)'),
75            Spot(spot_height=388.62, spot_location='POINT(10 11)'),
76            Spot(spot_height=1454.66, spot_location='POINT(40 34)'),
77            Spot(spot_height=54.66, spot_location='POINT(5 5)'),
78            Spot(spot_height=333.12, spot_location='POINT(2 3)'),
79            Spot(spot_height=783.55, spot_location='POINT(38 34)'),
80            Spot(spot_height=3454.67, spot_location='POINT(-134 45)'),
81            Spot(spot_height=6454.23, spot_location='POINT(-135 56)')
82            ])
83       
84        session.commit()
85       
86
87    def tearDown(self):
88        session.rollback()
89        metadata.drop_all()
90       
91
92    def test_protocol_create(self):
93        """Create a new point"""
94        proto = Protocol(session, Spot)
95       
96        request = FakeRequest({})
97        request.body = '{"type": "FeatureCollection", "features": [{"type": "Feature", "properties": {"spot_height": 12.0}, "geometry": {"type": "Point", "coordinates": [45, 5]}}]}'
98       
99        response = FakeResponse()
100        collection = proto.create(request, response)
101        eq_(response.status, 201)
102        eq_(len(collection.features), 1)
103        feature0 = collection.features[0]
104        eq_(feature0.id, 10)
105        eq_(feature0.geometry.coordinates, (45.0, 5.0))
106        eq_(feature0.properties["spot_height"], 12)
107
108        new_spot = session.query(Spot).filter(Spot.spot_height==12.0).one()
109        ok_(new_spot is not None)
110        eq_(session.scalar(new_spot.spot_location.wkt), u'POINT (45.0 5.0)')
111       
112
113    def test_protocol_create_and_update(self):
114        """Create a new point and also update an already existing point"""
115       
116        old_spot = session.query(Spot).filter(Spot.spot_height==102.34).one()
117       
118        proto = Protocol(session, Spot)
119       
120        request = FakeRequest({})
121        request.body = '{"type": "FeatureCollection", "features": [\
122            {"type": "Feature", "properties": {"spot_height": 12.0}, "geometry": {"type": "Point", "coordinates": [45, 5]}},\
123            {"type": "Feature", "id": ' + str(old_spot.spot_id) + ', "properties": {}, "geometry": {"type": "Point", "coordinates": [1, 1]}}]}'       
124       
125        response = FakeResponse()
126        collection = proto.create(request, response)
127        eq_(response.status, 201)
128        eq_(len(collection.features), 2)
129        feature0 = collection.features[0]
130        eq_(feature0.id, 10)
131        eq_(feature0.geometry.coordinates, (45.0, 5.0))
132        eq_(feature0.properties["spot_height"], 12)
133        feature1 = collection.features[1]
134        eq_(feature1.id, old_spot.spot_id)
135        eq_(feature1.geometry.coordinates, (1, 1))
136
137        new_spot = session.query(Spot).filter(Spot.spot_height==12.0).one()
138        ok_(new_spot is not None)
139        eq_(session.scalar(new_spot.spot_location.wkt), u'POINT (45.0 5.0)')
140       
141        updated_spot = session.query(Spot).filter(Spot.spot_height==102.34).one()
142        ok_(updated_spot is not None)
143        ok_(old_spot is updated_spot)
144        eq_(updated_spot.spot_height, 102.34)
145        eq_(session.scalar(updated_spot.spot_location.wkt), u'POINT (1.0 1.0)')
146       
147
148    @raises(Exception)
149    def test_protocol_create_fails(self):
150        """Try to create a feature without geometry"""
151        proto = Protocol(session, Spot)
152       
153        request = FakeRequest({})
154        request.body = '{"type": "FeatureCollection", "features": [{"type": "Feature", "properties": {"spot_height": 12.0}}]}'
155       
156        proto.create(request, FakeResponse())
157       
158
159    def test_protocol_update(self):
160        """Update an existing point"""
161        proto = Protocol(session, Spot)
162        id = 1
163       
164        request = FakeRequest({})
165        request.body = '{"type": "Feature", "id": ' + str(id) + ', "properties": {}, "geometry": {"type": "Point", "coordinates": [1, 1]}}'
166       
167        response = FakeResponse()
168        feature = proto.update(request, response, id)
169        eq_(response.status, 201)
170        eq_(feature.id, 1)
171        eq_(feature.geometry.coordinates, (1.0, 1.0))
172       
173        spot = session.query(Spot).get(id)
174        ok_(spot is not None)
175        eq_(session.scalar(spot.spot_location.wkt), u'POINT (1.0 1.0)')
176       
177       
178    @raises(HTTPNotFound)
179    def test_protocol_update_fails(self):
180        """Try to update a not-existing feature"""
181        proto = Protocol(session, Spot)
182        id = -1
183       
184        request = FakeRequest({})
185        request.body = '{"type": "Feature", "id": ' + str(id) + ', "properties": {}, "geometry": {"type": "Point", "coordinates": [1, 1]}}'
186       
187        response = FakeResponse()
188        proto.update(request, response, id)
189       
190
191    def test_protocol_delete(self):
192        """Delete an existing point"""
193        proto = Protocol(session, Spot)
194        id = 1
195       
196        request = FakeRequest({})
197        response = FakeResponse()
198       
199        proto.delete(request, response, id)
200        eq_(response.status, 204)
201       
202        spot = session.query(Spot).get(id)
203        ok_(spot is None)
204       
205
206    @raises(HTTPNotFound)
207    def test_protocol_delete_fails(self):
208        """Try to delete a not-existing point"""
209        proto = Protocol(session, Spot)
210       
211        proto.delete(FakeRequest({}), FakeResponse(), -1)
212       
213
214    def test_protocol_count(self):
215        """Get the feature count"""
216        proto = Protocol(session, Spot)
217       
218        eq_(proto.count(FakeRequest({})), '9')
219       
220
221    def test_protocol_count_filter_box(self):
222        """Get the feature count with a box as filter"""
223        proto = Protocol(session, Spot)
224        request = FakeRequest({})
225       
226        request.params['bbox'] = '-10,-10,10,10'
227        eq_(proto.count(request), '4')
228       
229        request.params['tolerance'] = '200000'
230        eq_(proto.count(request), '5')
231       
232        # query features that are inside a bbox that uses a different CRS
233        # note that we either have to specify a tolerance ('tol') or
234        # dimension information ('dim1' and 'dim2')
235        filter = create_default_filter(request, Spot, additional_params={'tol': '0.005'})
236        request.params['bbox'] = '-12.3364241712925,-10.0036833569465,7.66304367998925,9.9979519038951'
237        request.params['epsg'] = '2210'
238        request.params['tolerance'] = '0'
239        eq_(proto.count(request, filter=filter), '5')
240       
241        # dimension information array for 54004
242        # see http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11830/sdo_objrelschema.htm#i1010905
243        diminfo = "MDSYS.SDO_DIM_ARRAY("\
244            "MDSYS.SDO_DIM_ELEMENT('LONGITUDE', -20037508, 20037508, 0.005),"\
245            "MDSYS.SDO_DIM_ELEMENT('LATITUDE', -19929239, 19929239, 0.005)"\
246            ")"
247       
248        request.params['bbox'] = '-975862.822682856,-999308.345117013,1027887.98627823,999373.702609189'
249        request.params['epsg'] = '54004' # Oracles SRID number for World Mercator
250        filter = create_default_filter(request, Spot,
251                                       additional_params={'dim1': text(diminfo),
252                                                          'dim2' : text(diminfo)})
253        eq_(proto.count(request, filter=filter), '3')
254       
255
256    def test_protocol_count_filter_within(self):
257        """Get the feature count with a point as filter"""
258        proto = Protocol(session, Spot)
259        request = FakeRequest({})
260       
261        request.params['lat'] = '0'
262        request.params['lon'] = '0'
263        eq_(proto.count(request), '1')
264       
265        request.params['tolerance'] = '400000'
266        eq_(proto.count(request), '2')
267       
268        filter = create_default_filter(request, Spot, additional_params={'params': 'unit=KM'})
269        eq_(proto.count(request, filter=filter), '9')
270       
271
272    def test_protocol_count_filter_geometry(self):
273        """Get the feature count with a geometry as filter"""
274        proto = Protocol(session, Spot)
275        request = FakeRequest({})
276       
277        request.params['geometry'] = '{"type": "Polygon", "coordinates": [[ [-10, -1], [10, -1], [0, 10], [-10, -1] ]]}'
278        eq_(proto.count(request), '2')
279       
280        request.params['tolerance'] = '200000'
281        eq_(proto.count(request), '3')
282       
283
284    def test_protocol_count_queryable(self):
285        """Count all features that match a filter"""
286        proto = Protocol(session, Spot)
287        request = FakeRequest({})
288        request.params['queryable'] = 'spot_height'
289        request.params['spot_height__gte'] = '1454.66'
290       
291        eq_(proto.count(request), '3')
292       
293
294    def test_protocol_count_custom_filter(self):
295        """Count all features that match a custom filter"""
296        session.add_all([
297            Lake(depth=20, geom='POLYGON((-88.7968950764331 43.2305732929936,-88.7935511273885 43.1553344394904,-88.716640299363 43.1570064140127,-88.7250001719745 43.2339172420382,-88.7968950764331 43.2305732929936))'),
298            Lake(depth=5, geom='POLYGON((-88.1147292993631 42.7540605095542,-88.1548566878981 42.7824840764331,-88.1799363057325 42.7707802547771,-88.188296178344 42.7323248407643,-88.1832802547771 42.6955414012739,-88.1565286624204 42.6771496815287,-88.1448248407643 42.6336783439491,-88.131449044586 42.5718152866242,-88.1013535031847 42.565127388535,-88.1080414012739 42.5868630573248,-88.1164012738854 42.6119426751592,-88.1080414012739 42.6520700636943,-88.0980095541401 42.6838375796178,-88.0846337579618 42.7139331210191,-88.1013535031847 42.7423566878981,-88.1147292993631 42.7540605095542))'),
299            Lake(depth=120, geom='POLYGON((-89.0694267515924 43.1335987261147,-89.1078821656051 43.1135350318471,-89.1329617834395 43.0884554140127,-89.1312898089172 43.0466560509554,-89.112898089172 43.0132165605096,-89.0694267515924 42.9898089171975,-89.0343152866242 42.953025477707,-89.0209394904459 42.9179140127389,-89.0042197452229 42.8961783439491,-88.9774681528663 42.8644108280255,-88.9440286624204 42.8292993630573,-88.9072452229299 42.8142515923567,-88.8687898089172 42.815923566879,-88.8687898089172 42.815923566879,-88.8102707006369 42.8343152866242,-88.7734872611465 42.8710987261147,-88.7517515923567 42.9145700636943,-88.7433917197452 42.9730891719745,-88.7517515923567 43.0299363057325,-88.7734872611465 43.0867834394905,-88.7885352038217 43.158678388535,-88.8738057324841 43.1620222929936,-88.947372611465 43.1937898089172,-89.0042197452229 43.2138535031847,-89.0410031847134 43.2389331210191,-89.0710987261147 43.243949044586,-89.0660828025478 43.2238853503185,-89.0543789808917 43.203821656051,-89.0376592356688 43.175398089172,-89.0292993630573 43.1519904458599,-89.0376592356688 43.1369426751592,-89.0393312101911 43.1386146496815,-89.0393312101911 43.1386146496815,-89.0510350318471 43.1335987261147,-89.0694267515924 43.1335987261147))'),
300            Lake(depth=450, geom='POLYGON((-88.9122611464968 43.038296178344,-88.9222929936306 43.0399681528663,-88.9323248407643 43.0282643312102,-88.9206210191083 43.0182324840764,-88.9105891719745 43.0165605095542,-88.9005573248408 43.0232484076433,-88.9072452229299 43.0282643312102,-88.9122611464968 43.038296178344))')
301            ])
302        session.commit();
303       
304        proto = Protocol(session, Lake)
305       
306        from sqlalchemy.sql import and_
307       
308        request = FakeRequest({})
309        request.params['bbox'] = '-90,40,-80,45'
310       
311        filter = create_geom_filter(request, Lake)
312       
313        compare_filter = Lake.geom.area >= 0.1
314        filter = and_(filter, compare_filter)
315           
316        eq_(proto.count(request, filter=filter), '3')
317
318
319    def test_protocol_read_all(self):
320        """Return all features"""
321        proto = Protocol(session, Spot)
322
323        collection = proto.read(FakeRequest({}))
324        ok_(collection is not None)
325        ok_(isinstance(collection, FeatureCollection))
326        eq_(len(collection.features), 9)
327
328
329    def test_protocol_read_one(self):
330        """Return one feature"""
331        proto = Protocol(session, Spot)
332
333        feature = proto.read(FakeRequest({}), id=1)
334        ok_(feature is not None)
335        ok_(isinstance(feature, Feature))
336        eq_(feature.id, 1)
337        eq_(feature.geometry.coordinates, (0.0, 0.0))
338        eq_(feature.properties["spot_height"], 420.39999999999998)
339
340
341    @raises(HTTPNotFound)
342    def test_protocol_read_one_fails(self):
343        """Try to get a single point with a wrong primary key"""
344        proto = Protocol(session, Spot)
345       
346        proto.read(FakeRequest({}), id=-1)
347
348
349    def test_within_distance(self):
350        """Test the Oracle implementation for within_distance
351       
352        Because SDO_WITHIN_DISTANCE requires a spatial index for the geometry used
353        as first parameter, we have to insert out test geometries into tables,
354        unlike to the other databases.
355       
356        Note that Oracle uses meter as unit for the tolerance value for geodetic coordinate
357        systems (like 4326)!
358        """
359        from mapfish.sqlalchemygeom import within_distance
360       
361        # test if SDO_WITHIN_DISTANCE is called correctly
362        eq_(session.query(Spot).filter(within_distance(Spot.spot_location, 'POINT(0 0)', 0)).count(), 1)
363        eq_(session.query(Spot).filter(within_distance(Spot.spot_location, 'POINT(0 0)', 0.1)).count(), 1)
364        eq_(session.query(Spot).filter(within_distance(Spot.spot_location, 'POINT(9 9)', 100000)).count(), 0)
365       
366        eq_(session.query(Spot).filter(within_distance(Spot.spot_location,
367                                                       'Polygon((-5 -5, 5 -5, 5 5, -5 5, -5 -5))', 0)).count(), 3)
368       
369        eq_(session.query(Spot).filter(within_distance(Spot.spot_location,
370                                                       'Polygon((-10 -10, 10 -10, 10 10, -10 10, -10 -10))', 0)).count(), 4)
371       
372        eq_(session.query(Spot).filter(within_distance(Spot.spot_location,
373                                                       'Polygon((-10 -10, 10 -10, 10 10, -10 10, -10 -10))', 200000)).count(), 5)
374
375        # test if SDO_GEOM.WITHIN_DISTANCE is called correctly
376        eq_(session.scalar(select([text('1')], from_obj=['dual']).where(
377                                                    within_distance('POINT(0 0)', 'POINT(0 0)', 0,
378                                                                    {'tol' : 0.00000005}))), 1)
379        eq_(session.scalar(select([text('1')], from_obj=['dual']).where(
380                                                    within_distance('POINT(0 0)', 'POINT(0 0)', 0,
381                                                                    {'dim1' : text(diminfo),
382                                                                     'dim2' : text(diminfo)}))), 1)
Note: See TracBrowser for help on using the browser.