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

[Rémi Cura]

Hey,
I fiind it difficult to understand exactly your problem.
If you have a set of point A in python , of about 100 points,
and a set of point B, in postgres, of a few million points,
and that you want to find which point of A are already in B,
you wouldn't need to import your python points into a temp postgres table
and index it.

If your points are simple (for instance, only 2D, each dim an int),
you may skip postgis altogether.
Checking 100 points against few million should be <10ms with indexes.

You can check ta


2016-06-13 20:46 GMT+02:00 Leknín Řepánek <godzilalalala at gmail.com>:

> Python Array -> geometry(POINT, [SRID])[] -> unnest, you can use unnest
> in CTE
> something like
>
> WITH cte AS (
>         SELECT unnest(array['POINT(1 1)'::geometry ......]) pnt
> )
> SELECT * FROM t JOIN cte ON ST_....
>
>
> Milions of points aren`t big data, if it is slow, maybe you should check
> your indexes.
>
> On Mon, Jun 13, 2016 at 08:08:34AM -0700, Michal Seidl wrote:
> > Hello,
> > 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.
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at lists.osgeo.org
> > http://lists.osgeo.org/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160614/a972df5f/attachment.html>