[postgis-users] Computational Efficiency and Query Design Questions

[Nicklas Avén]

Hallo Tom

I will make a try answering as much as I can :-)

On Sat, 2011-01-08 at 12:11 -0600, Tom Kessler wrote:
> Hello to all,
> 
> I'm developing some spatial queries and have these questions:
> 
> 1)  When one makes an Explain Select request, is there any
> consideration given to the computational efficiencies
> of the Postgis functions themselves composing  the query?    I doubt
> it, but want to make sure.  I assume selection and optimization
> of the Postgis query functions is independent of the db query design.

>  
Well, there actually is, but it is not fully used. Every function has a
cost value that the planner uses to try to decide what the cheapest plan
is. The problem is that the cost is constant for the function so the
planner there is nothing telling "This is a huge polygon with hundred of
thousands of vertexes, be aware" The planner only knows that a function
with cost 100 is more expensive than a function with cost 1.

Today there is no costs defined for almost any postgis function, as I
understand it mostly because of the problem described above. The size
and complexity of the geometry makes a bigger difference than the
function.


But it is possible to play with costs yourself.
The below sets the cost to 1 (the default value for closestpoint)
alter function st_closestpoint(geometry, geometry) cost 1;




> 2)  I have a polygon defined by a hundred points  an external point,
> and wish to calculate an approximate point on the polygon
> closest to the external  point.  Assuming the error is acceptable, is
> it more computationally efficient to calculate on the bounding box
> than the polygon itself? 
> 
> a)     papprox  = ST_closestpoint( thegeom(polygon), p_ext)    or
> 
> b)     papprox = ST_closestpoint(box2d(thegeom(polygon), p_ext)    ??

I guess your syntax is just to illustrate...

The answer to your question depends on what geometries you are putting
in. If they have more than 4 vertexes it should be faster when computing
against the box. The box has only 2 corners, but it will be casted to a
4 vertex polygon before getting into the function.

So, for a two vertex line it might be slower when using the box, but for
a geometry with many vertex points it will be cheaper to use the
bounding box.


> 
> 3)  I have a table of geometries and wish to make combinatorial
> queries like:
> 
> SELECT(a.thegeom, b.thegeom, c.thegeom, etc..    WHERE   a.thegeom and
> b.thegeom touch 
> fixed points y and z  and c.thegeom intersects geometries   a and b.
> Which is a better approach:
> 
> a)  build temporary tables for a and b geoms, then find c geoms that
> intersect the entries in the temporary tables    or
> 
> b)  perform one select  and interate through all the combinations.
> The number of combinatorials goes exponential very quickly.  If one
> builds temporary 
> tables, how is the spatial index best transferred from original table
> to the temporary ones ? Does one have to rebuild the index from
> scratch.

I don't think I understand exactly what you want to do, but
comparing every possible combination, with the limitations you give in
join statement and where statement is what a join does. 

The planner will try to find the best order to do things to get away as
cheap as possible.

Just take care of having working spatial indexes, then a very lot of
combinations will be sorted away very fast.

So, if I understand right what you want to do, it could look something
like:

SELECT a.thegeom, b.thegeom, c.thegeom FROM
a.thegeom inner join b.thegeom on st_intersects(a.thegeom, b.thegeom)
inner join c on st_intersects(a.thegeom, c.thegeom) and
st_intersects(b.thegeom, c.thegeom)
where st_intersects(a.thegeom, point_y) and st_intersects(a.thegeom,
pointz) and st_intersects(b.thegeom, point_y) and
st_intersects(b.thegeom, pointz) 

well, it looks very messy, but I think the planner will be able to do a
good job here.

But I can not figure out what it could be useful for :-)


HTH
Nicklas



> thanks,
> 
> Tom
> 
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users