1.. _core_tutorial: 2 3Core Tutorial 4============= 5 6(This tutorial is greatly inspired from the `SQLAlchemy SQL Expression Language 7Tutorial`_, which is recommended reading, eventually.) 8 9.. _SQLAlchemy SQL Expression Language Tutorial: 10 http://docs.sqlalchemy.org/en/latest/core/tutorial.html 11 12This tutorial shows how to use the SQLAlchemy Expression Language (a.k.a. 13SQLAlchemy Core) with GeoAlchemy. As defined by the SQLAlchemy documentation 14itself, in contrast to the ORM's domain-centric mode of usage, the SQL 15Expression Language provides a schema-centric usage paradigm. 16 17Connect to the DB 18----------------- 19 20For this tutorial we will use a PostGIS 2 database. To connect we use 21SQLAlchemy's ``create_engine()`` function:: 22 23 >>> from sqlalchemy import create_engine 24 >>> engine = create_engine('postgresql://gis:gis@localhost/gis', echo=True) 25 26In this example the name of the database, the database user, and the database 27password, is ``gis``. 28 29The ``echo`` flag is a shortcut to setting up SQLAlchemy logging, which is 30accomplished via Python's standard logging module. With it is enabled, we'll 31see all the generated SQL produced. 32 33The return value of ``create_engine`` is an ``Engine`` object, which 34respresents the core interface to the database. 35 36Define a Table 37-------------- 38 39The very first object that we need to create is a ``Table``. Here 40we create a ``lake_table`` object, which will correspond to the 41``lake`` table in the database:: 42 43 >>> from sqlalchemy import Table, Column, Integer, String, MetaData 44 >>> from geoalchemy2 import Geometry 45 >>> 46 >>> metadata = MetaData() 47 >>> lake_table = Table('lake', metadata, 48 ... Column('id', Integer, primary_key=True), 49 ... Column('name', String), 50 ... Column('geom', Geometry('POLYGON')) 51 ... ) 52 53This table is composed of three columns, ``id``, ``name`` and ``geom``. The 54``geom`` column is a :class:`geoalchemy2.types.Geometry` column whose 55``geometry_type`` is ``POLYGON``. 56 57Any ``Table`` object is added to a ``MetaData`` object, which is a catalog of 58``Table`` objects (and other related objects). 59 60Create the Table 61---------------- 62 63With our ``Table`` being defined we're ready (to have SQLAlchemy) 64create it in the database:: 65 66 >>> lake_table.create(engine) 67 68Calling ``create_all()`` on ``metadata`` would have worked equally well:: 69 70 >>> metadata.create_all(engine) 71 72In that case every ``Table`` that's referenced to by ``metadata`` would be 73created in the database. The ``metadata`` object includes one ``Table`` here, 74our now well-known ``lake_table`` object. 75 76Reflecting tables 77----------------- 78 79The `reflection system of SQLAlchemy 80<http://docs.sqlalchemy.org/en/latest/core/schema.html#metadata-reflection>`_ can be 81used on tables containing :class:`geoalchemy2.types.Geometry` or 82:class:`geoalchemy2.types.Geography` columns. In this case, the type must be imported to 83be registered into SQLAlchemy, even if it is not used explicitely. 84 85 >>> from geoalchemy2 import Geometry # <= not used but must be imported 86 >>> from sqlalchemy import create_engine, MetaData 87 >>> engine = create_engine("postgresql://myuser:mypass@mydb.host.tld/mydbname") 88 >>> meta = MetaData() 89 >>> meta.reflect(bind=engine) 90 91Insertions 92---------- 93 94We want to insert records into the ``lake`` table. For that we need to create 95an ``Insert`` object. SQLAlchemy provides multiple constructs for creating an 96``Insert`` object, here's one:: 97 98 >>> ins = lake_table.insert() 99 >>> str(ins) 100 INSERT INTO lake (id, name, geom) VALUES (:id, :name, ST_GeomFromEWKT(:geom)) 101 102The ``geom`` column being a ``Geometry`` column, the ``:geom`` bind value is 103wrapped in a ``ST_GeomFromEWKT`` call. 104 105To limit the columns named in the ``INSERT`` query the ``values()`` method 106can be used:: 107 108 >>> ins = lake_table.insert().values(name='Majeur', 109 ... geom='POLYGON((0 0,1 0,1 1,0 1,0 0))') 110 ... 111 >>> str(ins) 112 INSERT INTO lake (name, geom) VALUES (:name, ST_GeomFromEWKT(:geom)) 113 114.. tip:: 115 116 The string representation of the SQL expression does not include the 117 data placed in ``values``. We got named bind parameters instead. To 118 view the data we can get a compiled form of the expression, and ask 119 for its ``params``:: 120 121 >>> ins.compile.params() 122 {'geom': 'POLYGON((0 0,1 0,1 1,0 1,0 0))', 'name': 'Majeur'} 123 124Up to now we've created an ``INSERT`` query but we haven't sent this query to 125the database yet. Before being able to send it to the database we need 126a database ``Connection``. We can get a ``Connection`` from the ``Engine`` 127object we created earlier:: 128 129 >>> conn = engine.connect() 130 131We're now ready to execute our ``INSERT`` statement:: 132 133 >>> result = conn.execute(ins) 134 135This is what the logging system should output:: 136 137 INSERT INTO lake (name, geom) VALUES (%(name)s, ST_GeomFromEWKT(%(geom)s)) RETURNING lake.id 138 {'geom': 'POLYGON((0 0,1 0,1 1,0 1,0 0))', 'name': 'Majeur'} 139 COMMIT 140 141The value returned by ``conn.execute()``, stored in ``result``, is 142a ``sqlalchemy.engine.ResultProxy`` object. In the case of an ``INSERT`` we can 143get the primary key value which was generated from our statement:: 144 145 >>> result.inserted_primary_key 146 [1] 147 148Instead of using ``values()`` to specify our ``INSERT`` data, we can send 149the data to the ``execute()`` method on ``Connection``. So we could rewrite 150things as follows:: 151 152 >>> conn.execute(lake_table.insert(), 153 ... name='Majeur', geom='POLYGON((0 0,1 0,1 1,0 1,0 0))') 154 155Now let's use another form, allowing to insert multiple rows at once:: 156 157 >>> conn.execute(lake_table.insert(), [ 158 ... {'name': 'Garde', 'geom': 'POLYGON((1 0,3 0,3 2,1 2,1 0))'}, 159 ... {'name': 'Orta', 'geom': 'POLYGON((3 0,6 0,6 3,3 3,3 0))'} 160 ... ]) 161 ... 162 163.. tip:: 164 165 In the above examples the geometries are specified as WKT strings. 166 Specifying them as EWKT strings is also supported. 167 168 169Selections 170---------- 171 172Inserting involved creating an ``Insert`` object, so it'd come to no surprise 173that Selecting involves creating a ``Select`` object. The primary construct to 174generate ``SELECT`` statements is SQLAlchemy`s ``select()`` function:: 175 176 >>> from sqlalchemy.sql import select 177 >>> s = select([lake_table]) 178 >>> str(s) 179 SELECT lake.id, lake.name, ST_AsEWKB(lake.geom) AS geom FROM lake 180 181The ``geom`` column being a ``Geometry`` it is wrapped in a ``ST_AsEWKB`` 182call when specified as a column in a ``SELECT`` statement. 183 184We can now execute the statement and look at the results:: 185 186 >>> result = conn.execute(s) 187 >>> for row in result: 188 ... print 'name:', row['name'], '; geom:', row['geom'].desc 189 ... 190 name: Majeur ; geom: 0103... 191 name: Garde ; geom: 0103... 192 name: Orta ; geom: 0103... 193 194``row['geom']`` is a :class:`geoalchemy2.types.WKBElement` instance. In this 195example we just get an hexadecimal representation of the geometry's WKB value 196using the ``desc`` property. 197 198Spatial Query 199------------- 200 201As spatial database users executing spatial queries is of a great interest to 202us. There comes GeoAlchemy! 203 204Spatial relationship 205~~~~~~~~~~~~~~~~~~~~ 206 207Using spatial filters in SQL SELECT queries is very common. Such queries are 208performed by using spatial relationship functions, or operators, in the 209``WHERE`` clause of the SQL query. 210 211For example, to find lakes that contain the point ``POINT(4 1)``, 212we can use this:: 213 214 215 >>> from sqlalchemy import func 216 >>> s = select([lake_table], 217 func.ST_Contains(lake_table.c.geom, 'POINT(4 1)')) 218 >>> str(s) 219 SELECT lake.id, lake.name, ST_AsEWKB(lake.geom) AS geom FROM lake WHERE ST_Contains(lake.geom, :param_1) 220 >>> result = conn.execute(s) 221 >>> for row in result: 222 ... print 'name:', row['name'], '; geom:', row['geom'].desc 223 ... 224 name: Orta ; geom: 0103... 225 226GeoAlchemy allows rewriting this more concisely:: 227 228 >>> s = select([lake_table], lake_table.c.geom.ST_Contains('POINT(4 1)')) 229 >>> str(s) 230 SELECT lake.id, lake.name, ST_AsEWKB(lake.geom) AS geom FROM lake WHERE ST_Contains(lake.geom, :param_1) 231 232Here the ``ST_Contains`` function is applied to ``lake.c.geom``. And the 233generated SQL the ``lake.geom`` column is actually passed to the 234``ST_Contains`` function as the first argument. 235 236Here's another spatial query, based on ``ST_Intersects``:: 237 238 >>> s = select([lake_table], 239 ... lake_table.c.geom.ST_Intersects('LINESTRING(2 1,4 1)')) 240 >>> result = conn.execute(s) 241 >>> for row in result: 242 ... print 'name:', row['name'], '; geom:', row['geom'].desc 243 ... 244 name: Garde ; geom: 0103... 245 name: Orta ; geom: 0103... 246 247 This query selects lakes whose geometries intersect ``LINESTRING(2 1,4 1)``. 248 249The GeoAlchemy functions all start with ``ST_``. Operators are also called as 250functions, but the names of operator functions don't include the ``ST_`` 251prefix. 252 253As an example let's use PostGIS' ``&&`` operator, which allows testing 254whether the bounding boxes of geometries intersect. GeoAlchemy provides the 255``intersects`` function for that:: 256 257 >>> s = select([lake_table], 258 ... lake_table.c.geom.intersects('LINESTRING(2 1,4 1)')) 259 >>> result = conn.execute(s) 260 >>> for row in result: 261 ... print 'name:', row['name'], '; geom:', row['geom'].desc 262 ... 263 name: Garde ; geom: 0103... 264 name: Orta ; geom: 0103... 265 266Processing and Measurement 267~~~~~~~~~~~~~~~~~~~~~~~~~~ 268 269Here's a ``Select`` that calculates the areas of buffers for our lakes:: 270 271 >>> s = select([lake_table.c.name, 272 func.ST_Area( 273 lake_table.c.geom.ST_Buffer(2)).label('bufferarea')]) 274 >>> str(s) 275 SELECT lake.name, ST_Area(ST_Buffer(lake.geom, %(param_1)s)) AS bufferarea FROM lake 276 >>> result = conn.execute(s) 277 >>> for row in result: 278 ... print '%s: %f' % (row['name'], row['bufferarea']) 279 Majeur: 21.485781 280 Garde: 32.485781 281 Orta: 45.485781 282 283Obviously, processing and measurement functions can also be used in ``WHERE`` 284clauses. For example:: 285 286 >>> s = select([lake_table.c.name], 287 lake_table.c.geom.ST_Buffer(2).ST_Area() > 33) 288 >>> str(s) 289 SELECT lake.name FROM lake WHERE ST_Area(ST_Buffer(lake.geom, :param_1)) > :ST_Area_1 290 >>> result = conn.execute(s) 291 >>> for row in result: 292 ... print row['name'] 293 Orta 294 295And, like any other functions supported by GeoAlchemy, processing and 296measurement functions can be applied to 297:class:`geoalchemy2.elements.WKBElement`. For example:: 298 299 >>> s = select([lake_table], lake_table.c.name == 'Majeur') 300 >>> result = conn.execute(s) 301 >>> lake = result.fetchone() 302 >>> bufferarea = conn.scalar(lake[lake_table.c.geom].ST_Buffer(2).ST_Area()) 303 >>> print '%s: %f' % (lake['name'], bufferarea) 304 Majeur: 21.485781 305 306Use Raster functions 307-------------------- 308 309A few functions (like `ST_Transform()`, `ST_Union()`, `ST_SnapToGrid()`, ...) can be 310used on both :class:`geoalchemy2.types.Geometry` and :class:`geoalchemy2.types.Raster` 311types. In GeoAlchemy2, these functions are only defined for 312:class:`Geometry` as it can not be defined for several types at the 313same time. Thus using these functions on :class:`Raster` requires 314minor tweaking to enforce the type by passing the `type_=Raster` argument to the 315function: 316 317 >>> s = select([func.ST_Transform( 318 lake_table.c.raster, 319 2154, 320 type_=Raster) 321 .label('transformed_raster')]) 322 323Further Reference 324----------------- 325 326* Spatial Functions Reference: :ref:`spatial_functions` 327* Spatial Operators Reference: :ref:`spatial_operators` 328* Elements Reference: :ref:`elements` 329