[postgis-devel] Optimizing contains/within

Robert W. Burgholzer rburghol at vt.edu
Fri Jul 27 07:18:22 PDT 2007

Can't you simply use function overloading, such as the following (don't know if
geometry[] is really the proper way to indicate and array of geometries):

CREATE FUNCTION contains(geometry[],geometry[])
   RETURNS boolean
   AS 'C:/Program Files/PostgreSQL/8.2/lib/liblwgeom.dll'
   LANGUAGE 'C' IMMUTABLE STRICT; -- WITH (isstrict,iscachable);

to avoid the confusion that would arise from having to create a separate
function like "points_in_polys"?


> Chris Hodgson wrote:
> > Even if geos had a "batch optimized" function like "points_in_ring()"
> > that was designed to use the more optimized strategy, it would be
> > impossible to use it because your SQL query would likely look something
> > like:
> >
> > SELECT point_in_poly(a.geom_column, b.geom_column) from a join b;
> >
> > and so the SQL plan would be to call point_in_poly() many times, not
> > points_in_poly().
> >
> > I think there are two solutions to this problem. The possibly simpler
> > but less generic solution would be to create some new functions in geos
> > and postgis that are designed to work on arrays/lists/sets of objects,
> > so that you could use SQL like this:
> >
> > SELECT points_in_polys( (select geom_col from a), (select geom_col from
> > b) )
> >
> > And be returned an array of results. This would provide a more optimized

Robert W. Burgholzer
Finding the occasional straw of truth awash in a great ocean of confusion and
bamboozle requires intelligence, vigilance, dedication and courage.  But if we
don't practice these tough habits of thought, we cannot hope to solve the truly
serious problems that face us -- and we risk becoming a nation of suckers, up
for grabs by the next charlatan who comes along.
-- Carl Sagan, "The Fine Art of Baloney Detection," Parade, February 1, 1987

Online Workout Editor:

More information about the postgis-devel mailing list