[postgis-users] Performance external loop, WHERE IN (values), stored procedure or else to pass data to SQL query

Michal Seidl michal.seidl at gmail.com
Mon Jun 13 08:08:34 PDT 2016

I have probably simple question but I am stuck. I have table with about 1E6
points in table. I have about hundreds of points in Python array. How to
pass these points into Postgis SQL query to check against table (if the same
point exist) with millions points?

I started with Python loop to check every point individually but it is slow.
I have found lots of receipts how to fight with large data but they have to
be already in database.

Which way is good for this situation?
- create temporary table and firstly insert points into DB
- try to manage Python not to close connection and send SQL query with each
point fast
- try to push all points in one query? How?
- write stored procedure that will accept array of data?
- anything else?

May be it is more general SQL questions, but i am working with points.

Thanks for advice Michal

View this message in context: http://postgis.17.x6.nabble.com/Performance-external-loop-WHERE-IN-values-stored-procedure-or-else-to-pass-data-to-SQL-query-tp5010138.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

More information about the postgis-users mailing list