0

I am trying to insert POSTGIS values for Linestring, Polygon and other data types into postgres DB using SQLalchemy and came across these errors:

This is the Schema of the table created using postgres db commands:

                 Table "public.test"
    Column    |           Type            | Modifiers 
--------------+---------------------------+-----------
 name         | text                      | 
 wkb_geometry | geometry(LineString,4326) |

The schema for the postgres table has been already created using DDL statements.

I have tried inserting the gml values from a xml using psycopg2 and it was getting inserted successfully using the below:

wkb_geometry_value = """<gml:LineString xmlns:gml="http://www.opengis.net/gml" xmlns="urn:hera:xml:ns:DataTypes:2.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:hera-drex="urn:hera:xml:ns:exchange:2.0" xmlns:hera-sgftad="urn:hera:xml:ns:sft:2.0" xmlns:hera-rifdt="urn:hera:xml:nsftypes:2.0" xmlns:wfs="http://www.opengis.org/wfs: " xmlns:gb="http://www.opengis.org/gb" xmlns:georss="http://www.opengis.netgeorss"><gml:posList>34.216 -210.54 34.46 -119.4 34.65 -139.84</gml:posList>\</gml:LineString>"""

sql = INSERT INTO test('name','wkb_geometry)VALUES('BOB', ST_SetSRID(ST_GeomFromGML('{}',4326)))
cursor.execute(sql)

Output --> Successfully inserted

But when I use the below it fails using sqlalchemy and geoalchemy

import sqlalchemy                                                           
from sqlalchemy import create_engine            
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy import inspect 

sql = """INSERT INTO test (name, wkb_geometry) VALUES ('{}', ST_GeomFromEWKT('{}'))""".format('BOB', wkb_geometry_value)
engine = create_engine('postgresql://username:password@localhost/dbname')

 with engine.connect() as con:
    rs = con.execute(sql)

Output ==> InternalError: (psycopg2.InternalError) parse error - invalid geometry

How can I insert the GIS datatypes into PostgresDB using SQLalchemy and geoalchemy.

Any help would be appreciated Thanks!!!! I

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
Henin RK
  • 298
  • 1
  • 2
  • 14
  • 1
    First of all: never use string formatting for passing values to SQL queries. You'll run to trouble sooner or later, "trouble" being from "my query fails" to "I got owned". Use placeholders. In case of SQLAlchemy read ["Using Textual SQL"](http://docs.sqlalchemy.org/en/latest/core/tutorial.html#using-textual-sql). – Ilja Everilä May 22 '17 at 08:44
  • Your *wkb_geometry_value* does not look like [Well-Known Text](https://en.wikipedia.org/wiki/Well-known_text), but [GML](http://www.opengeospatial.org/standards/gml), so use the [correct function](https://postgis.net/docs/ST_GeomFromGML.html), like you did with plain psycopg2. – Ilja Everilä May 22 '17 at 08:53
  • @IljaEverilä - Do you have an example to show the insertion of gml tags into postgresdb using sqlalchemy without using the native wrapper psycopg2? – Henin RK May 22 '17 at 10:27
  • No, and the query you used before would work as is. Your SQLAlchemy version is trying to use `ST_GeomFromEWKT()` on GML data, where your original psycopg2 version was using the correct `ST_GeomFromGML(...)` call. Hence the "close as simple typo / no repro" vote. – Ilja Everilä May 22 '17 at 10:31

0 Answers0