
.. DO NOT EDIT.
.. THIS FILE WAS AUTOMATICALLY GENERATED BY SPHINX-GALLERY.
.. TO MAKE CHANGES, EDIT THE SOURCE PYTHON FILE:
.. "gallery/test_length_at_insert.py"
.. LINE NUMBERS ARE GIVEN BELOW.

.. only:: html

    .. note::
        :class: sphx-glr-download-link-note

        Click :ref:`here <sphx_glr_download_gallery_test_length_at_insert.py>`
        to download the full example code

.. rst-class:: sphx-glr-example-title

.. _sphx_glr_gallery_test_length_at_insert.py:


Compute length on insert
========================

It is possible to insert a geometry and ask PostgreSQL to compute its length at the same
time.
This example uses SQLAlchemy core queries.

.. GENERATED FROM PYTHON SOURCE LINES 9-82

.. code-block:: default
   :lineno-start: 9

    from sqlalchemy import bindparam
    from sqlalchemy import Column
    from sqlalchemy import create_engine
    from sqlalchemy import Float
    from sqlalchemy import func
    from sqlalchemy import Integer
    from sqlalchemy import MetaData
    from sqlalchemy import select
    from sqlalchemy import Table

    from geoalchemy2 import Geometry
    from geoalchemy2.shape import to_shape


    engine = create_engine('postgresql://gis:gis@localhost/gis', echo=True)
    metadata = MetaData(engine)

    table = Table(
        "inserts",
        metadata,
        Column("id", Integer, primary_key=True),
        Column("geom", Geometry("LINESTRING", 4326)),
        Column("distance", Float),
    )


    class TestLengthAtInsert():

        def setup(self):
            self.conn = engine.connect()
            metadata.drop_all(checkfirst=True)
            metadata.create_all()

        def teardown(self):
            self.conn.close()
            metadata.drop_all()

        def test_query(self):
            conn = self.conn

            # Define geometries to insert
            values = [
                {"ewkt": "SRID=4326;LINESTRING(0 0, 1 0)"},
                {"ewkt": "SRID=4326;LINESTRING(0 0, 0 1)"}
            ]

            # Define the query to compute distance (without spheroid)
            distance = func.ST_Length(func.ST_GeomFromText(bindparam("ewkt")), False)

            i = table.insert()
            i = i.values(geom=bindparam("ewkt"), distance=distance)

            # Execute the query with values as parameters
            conn.execute(i, values)

            # Check the result
            q = select([table])
            res = conn.execute(q).fetchall()

            # Check results
            assert len(res) == 2

            r1 = res[0]
            assert r1[0] == 1
            assert r1[1].srid == 4326
            assert to_shape(r1[1]).wkt == "LINESTRING (0 0, 1 0)"
            assert round(r1[2]) == 111195

            r2 = res[1]
            assert r2[0] == 2
            assert r2[1].srid == 4326
            assert to_shape(r2[1]).wkt == "LINESTRING (0 0, 0 1)"
            assert round(r2[2]) == 111195


.. rst-class:: sphx-glr-timing

   **Total running time of the script:** ( 0 minutes  0.000 seconds)


.. _sphx_glr_download_gallery_test_length_at_insert.py:


.. only :: html

 .. container:: sphx-glr-footer
    :class: sphx-glr-footer-example



  .. container:: sphx-glr-download sphx-glr-download-python

     :download:`Download Python source code: test_length_at_insert.py <test_length_at_insert.py>`



  .. container:: sphx-glr-download sphx-glr-download-jupyter

     :download:`Download Jupyter notebook: test_length_at_insert.ipynb <test_length_at_insert.ipynb>`


.. only:: html

 .. rst-class:: sphx-glr-signature

    `Gallery generated by Sphinx-Gallery <https://sphinx-gallery.github.io>`_
