CODEWITHSUNDEEP
postgresqlpython-2.7geometrypostgispsycopg2
Michael Burghart
Michael Burghart

Reputation: 11

How inserting LineStrings to a PostGIS-Database with Python, psycopg2 and ppygis

i am trying to insert LineStrings to a local Postgres/PostGIS-database. I use Python 2.7, psycopg2 and ppygis.

Every time when i make a loop-input, only a few records were inserted into the table. I tried to find out the problem with mogrify, but i see no failure.

polyline = []
for row in positions:
   lat = row[0]
   lon = row[1]
   point = ppygis.Point(lon, lat, srid=4326)
   polyline.append(point)
linestring = ppygis.LineString(polyline, srid=4326)

self.cursor.execute("BEGIN")
self.cursor.execute("INSERT INTO gtrack_4326 (car, polyline) VALUES (%s,%s);", ("TEST_car", linestring))
self.cursor.execute("COMMIT")

The use of execute.mogrify results in Strings like this:

INSERT INTO gtrack_4326 (car,polyline) VALUES ('TEST_car', '0102000020e610000018000000aefab72638502940140c42d4d899484055a4c2d84250294056a824a1e3994840585b0c795f50294085cda55df1994840edca78a57650294069595249f8994840ec78dd6cbd502940828debdff5994840745314f93f5129407396fecaef994840e1f6bafbd25129404eab329de7994840da588979565229407a562d44e2994840ebc9fca36f522940c2af4797ed9948403bd164b5af5229407a90f9dbf99948407adbf1cb05532940818af4ec039a484062928087585329402834ff9e0e9a4840e8bb5b59a2532940b1ec38341b9a4840dcb28d89de532940afe94141299a484084d3275e0a54294019b1aab9379a484080ca42853454294053509b82469a48408df8043f6054294063844b22569a48406d3e09c7875429406dfbc33b659a4840aa5a77989b542940c20e08196d9a48401b56a7b9cb542940a0a0b9f3699a4840cf2d742502552940192543e9669a484045ac0f351b552940fdb0efd46d9a48406891ed7c3f552940450a0a28799a4840d0189c77525529405f7b6649809a4840');

But if i look into the Database, i see a lot of records without geometry-data in the second column. I did not understand why mogrify shows data in each column and in the DB there is in nearly 50 % of the table no data in the geometry-column.

Upvotes: 1

Views: 2402

Answers (2)

Mike T
Mike T

Reputation: 43672

How did you determine that 50% of the rows have no geometry data? I'll warn anyone using clients like pgAdminIII show a blank cell if it contains too much data, so it appears to be NULL, when it isn't. You can also directly view the GIS data in a program like Quantum GIS.

With an SQL client, a better diagnostic to determine if a linestring is really there is to get the number of points in the linestring:

SELECT car, ST_NumPoints(polyline) FROM gtrack_4326;

If the numbers are empty, then your assessment is correct that they are empty. Otherwise, the data are too large to display in your client application.

Upvotes: 1

Craig Ringer
Craig Ringer

Reputation: 324671

First, psycopg2 does its own transaction management, so you should generally write:

self.cursor.execute(
    "INSERT INTO gtrack_4326 (car, polyline) VALUES (%s,%s);",
    ("TEST_car", linestring)
)
self.conn.commit()

See the psycopg2 docs.

Second, consider loading data in batches with COPY. See COPY in the psycopg2 docs.

Also consider setting log_statement = 'all' in postgresql.conf and a suitable log_line_prefix then restarting the PostgreSQL server. Examine the logs and see if you can tell what's doing the bogus inserts.

If practical, add a CHECK and/or NOT NULL constraint to the geometry column so that any incorrect INSERTs will fail and report an error to the program doing the insert. This might help you diagnose the problem.

Upvotes: 1

Related Questions