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@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
