[GIS] Inserting a Shapely Geometry LineString back into PostGIS database with psycopg2

postgispostgresqlpythonshapelysql

I have a results database I am trying to write a LineString geometry value, a total_time, and an id into my Postgres db with PostGIS.

The query I'm trying to do in Python with psycopg2 is this….

    insert_query = 'INSERT INTO public.results(the_geom, total_time, id) VALUES (ST_SetSRID('+str(hex_shortest_route_geom)+'::geometry, 4326), '+str(total_time)+', '+str(id)+');'

Yes I am aware this is some bad practices with SQL and prone to SQL Injections. So for example the query turns into…

INSERT INTO public.results(the_geom, total_time, id) VALUES (ST_SetSRID(010200000052000000F9B605F0BB6352C0961BB1BBE59044405E34BFF5BC6352C05882D60D25914440131736B9CE6352C0B8BE5994239144406A0B19D9DF6352C07EDAF2DB21914440677BF486FB6352C02C442C071F914440B616C15A106452C03912C3691C914440502ED7EC126452C0AA85ED821C914440E95F92CA146452C092CD55F31C914440183A1B97166452C0036F92301D9144405567B5C01E6452C0ACF6FAA424914440F1C1C650296452C0D01D0E5C2F9144400DECE703386452C0EC191DEB3D914440BAE2981B426452C0B1AEC04648914440BFF2203D456452C020C88B964D914440AC1F9BE4476452C016BF29AC549144404AC33A9F4B6452C0BD3A223A5F9144403102CF624E6452C08C1AA77C63914440E82FF488516452C0199B0C7D6691444068244223586452C0106AD0E16B91444061A351265A6452C0AA98EF856E91444097C5C4E6636452C03D27BD6F7C91444043BE9536666452C0E04499B2789144406B798AC1686452C0288705AD769144405DA5BBEB6C6452C09B481FA9749144405C0F15996F6452C0015C7F01739144404EB4AB90726452C05C9F83786E9144404D66BCAD746452C08DB9C6C26B914440DA2B3DE4776452C0F48AA71E69914440B4E4F1B47C6452C0CB87FB23679144405A93B8D87E6452C025636B6C6591444005758016816452C05CB0AFD062914440D5496C2D826452C00BBEC4B35F9144402DE8BD31846452C08E57207A52914440149D1B89866452C004D2B47A3D914440C10E52A6876452C085AC133C2A91444019260978886452C084C2FB05169144403C3E6BC8896452C01E335019FF904440081AD8CF876452C01EE85729F39044400F289B72856452C07A7E07F5E3904440DF8CF568856452C03345CA60D6904440676AC82E876452C039758AFAC99044408B2C3EAA866452C093B71270BE904440E01C6448846452C04D840D4FAF9044408EB4AF97816452C0CACA8A3CA49044402491C71D806452C07307FBC09F9044408591031B7C6452C04611082A9990444057163BD0796452C0F19B6791929044407EF6C88B716452C0959F54FB7490444020E05A376F6452C0B6BC72BD6D904440FE15E8C96B6452C090F980406790444048EEFA16676452C0AB81412D61904440CFD325885F6452C00E7A803859904440EE23B7265D6452C0A5A723DB54904440F152A005576452C0AA2DD0494A9044404E8E4CD15B6452C092109B7E3F9044401AEC9799636452C0D7A7774C38904440488F3A95676452C0C1CD3D7F3590444099AC9B9C6B6452C080400C193490444049B5F40D716452C05D3C17A133904440663ED6427F6452C04600811832904440C3DBDEC9826452C040B912263190444058CFA4A88E6452C0B29FC55224904440BDD17DEFA56452C02DE8BD3104904440A243E048A06452C0D82725A9F18F4440A3BC45169F6452C00F05D8ECED8F4440637A67599C6452C09CBE43F6E68F444022BF23CF9A6452C0E0F3C308E18F4440F9FDF6D0996452C096CA8058DB8F44400310D2AE9D6452C0401B1428BD8F44408076D1A19D6452C0CD920035B58F44405DA9C2FA9A6452C0A89086AE9F8F44405F14F362976452C05F950B957F8F44405CAE7E6C926452C0DEB06D51668F4440E25CC30C8D6452C003A1AB634B8F44407F64DFBA876452C09475EE2C308F4440DC12149A7F6452C093F5E516048F44409D499BAA7B6452C05E1E21B9EB8E4440DBD2B4D5766452C08C1A0236D68E4440AF88505B596452C05B8D36D8E58E444051BCCADA266452C08100BE80038F4440A39F5F4A136452C0BE440603198F44404BE1E6F9F86352C0E0B65B374A8F4440::geometry, 4326), 14.5755758286, 090219AMOctober202017);

The Geometry for the LineString is converted to a hex value to store in Postgres like such with shapely…

hex_shortest_route_geom = LineString(the_geometry).wkb_hex

Yet, I am getting syntax errors in my SQL queries…

psycopg2.ProgrammingError: syntax error at or near "F9B605F0BB6352C0961BB1BBE59044405E34BFF5BC6352C05882D60D25914440131736B9CE6352C0B8BE5994239144406A0B19D9DF6352C07EDAF2DB21914440677BF486FB6352C02C442C071F914440B616C15A106452C03912C3691C914440502ED7EC126452C0AA85ED821C914440E95F92CA146452C092CD55F31C914440183A1B97166452C0036F92301D9144405567B5C01E6452C0ACF6FAA424914440F1C1C650296452C0D01D0E5C2F9144400DECE703386452C0EC191DEB3D914440BAE2981B426452C0B1AEC04648914440BFF2203D456452C020C88B964D914440AC1F9BE4476452C016BF29AC549144404AC33A9F4B6452C0BD3A223A5F9144403102CF624E6452C08C1AA77C63914440E82FF488516452C0199B0C7D6691444068244223586452C0106AD0E16B91444061A351265A6452C0AA98EF856E91444097C5C4E6636452C03D27BD6F7C91444043BE9536666452C0E04499B2789144406B798AC1686452C0288705AD769144405DA5BBEB6C6452C09B481FA9749144405C0F15996F6452C0015C7F01739144404EB4AB90726452C05C9F83786E9144404D66BCAD746452C08DB9C6C26B914440DA2B3DE4776452C0F48AA71E69914440B4E4F1B47C6452C0CB87FB23679144405A93B8D87E6452C025636B6C6591444005758016816452C05CB0AFD062914440D5496C2D826452C00BBEC4B35F9144402DE8BD31846452C08E57207A52914440149D1B89866452C004D2B47A3D914440C10E52A6876452C085AC133C2A91444019260978886452C084C2FB05169144403C3E6BC8896452C01E335019FF904440081AD8CF876452C01EE85729F39044400F289B72856452C07A7E07F5E3904440DF8CF568856452C03345CA60D6904440676AC82E876452C039758AFAC99044408B2C3EAA866452C093B71270BE904440E01C6448846452C04D840D4FAF9044408EB4AF97816452C0CACA8A3CA49044402491C71D806452C07307FBC09F9044408591031B7C6452C04611082A9990444057163BD0796452C0F19B6791929044407EF6C88B716452C0959F54FB7490444020E05A376F6452C0B6BC72BD6D904440FE15E8C96B6452C090F980406790444048EEFA16676452C0AB81412D61904440CFD325885F6452C00E7A803859904440EE23B7265D6452C0A5A723DB54904440F152A005576452C0AA2DD0494A9044404E8E4CD15B6452C092109B7E3F9044401AEC9799636452C0D7A7774C38904440488F3A95676452C0C1CD3D7F3590444099AC9B9C6B6452C080400C193490444049B5F40D716452C05D3C17A133904440663ED6427F6452C04600811832904440C3DBDEC9826452C040B912263190444058CFA4A88E6452C0B29FC55224904440BDD17DEFA56452C02DE8BD3104904440A243E048A06452C0D82725A9F18F4440A3BC45169F6452C00F05D8ECED8F4440637A67599C6452C09CBE43F6E68F444022BF23CF9A6452C0E0F3C308E18F4440F9FDF6D0996452C096CA8058DB8F44400310D2AE9D6452C0401B1428BD8F44408076D1A19D6452C0CD920035B58F44405DA9C2FA9A6452C0A89086AE9F8F44405F14F362976452C05F950B957F8F44405CAE7E6C926452C0DEB06D51668F4440E25CC30C8D6452C003A1AB634B8F44407F64DFBA876452C09475EE2C308F4440DC12149A7F6452C093F5E516048F44409D499BAA7B6452C05E1E21B9EB8E4440DBD2B4D5766452C08C1A0236D68E4440AF88505B596452C05B8D36D8E58E444051BCCADA266452C08100BE80038F4440A39F5F4A136452C0BE440603198F44404BE1E6F9F86352C0E0B65B374A8F4440"
LINE 1: ...al_time, id) VALUES (ST_SetSRID(010200000052000000F9B605F0BB...

I'm unsure what's going on, any help?

Best Answer

Don't put thing directly in strings :

insert_query = 'INSERT INTO public.results(the_geom, total_time, id) VALUES (ST_SetSRID(:geom::geometry, 4326), :total_time, :id)'

your_db_connection.execute(insert_query, geom=hex_shortest_route_geom, total_time=total_time, id=id)

The original problem was quote. When issuing this kind of error you can can copy/paste plain SQL in psql.

In addition, using the PostGIS query ST_SetSRID is wrong. You have a hex value of the LineString, use ST_GeomFromHKB instead like this...

insert_query = """INSERT INTO public.results(the_geom, total_time, id) VALUES (ST_GeomFromWKB(%(geom)s::geometry, 4326), %(total_time)s, %(id)s)"""
Related Question