This alternative was a little faster and does not return duplicated rows. It will take 41 hours on my system, but it will have to do for now.
SELECT vciia_main.sitrp,vciia_main.date_time,ST_AsText(vciia_main.geom) from vciia_main, south_vietnam72 where st_within(vciia_main.geom, south_vietnam72.geom) and (st_dwithin(vciia_main.geom, (select the_geom from houses order by st_distance(vciia_main.geom, the_geom) limit 1), 500) OR st_dwithin(vciia_main.geom, (select geom from roads order by st_distance(vciia_main.geom, geom) limit 1), 500) OR st_dwithin(vciia_main.geom, (select geom from rails order by st_distance(vciia_main.geom, geom) limit 1), 500) OR st_dwithin(vciia_main.geom, (select geom from city order by st_distance(vciia_main.geom, geom) limit 1), 800)) and vciia_main.date_time is not null; -Håvard 2011/1/13 Nicklas Avén <nicklas.a...@jordogskog.no> > No, the order is not supposed to make any difference. The planner plans > the query from costs and statistics and shouldn't care about the order > (I think) > > What you should do is to divide the big geometries and build new index. > > That can be done by building a grid with generate_series and cutting the > geometry against that grid by using ST_Intersection between the grid and > your geometry. I have not tried it myself so I don't have any query > written for it. > > Another way that might be simplier is to use some other map than taht > south Vietnam map. If you have some map with smaller regions instead the > index will be more efficiency. > > Also use ST_Dump to tear apart all geometries as much as possible. > > Then build a new index and analyse for the planner to know what you have > done. > > One thing to remember is that you in many cases will get the same > geometry many times because it is within the given distance to many of > your regions like ST_Intersects(Mjøsa, fylker) will give many > fylke-mjøsa combinations. > > Regards > > Nicklas > > > > > On Thu, 2011-01-13 at 21:51 +0100, Håvard Wahl Kongsgård wrote: > > Hi, so one solution is to use the most common feature first (), in > > this case the houses ? > > > > |What version of PostGIS are you using? > > > > 1.5.1 > > > > > > 2011/1/13 Nicklas Avén <nicklas.a...@jordogskog.no> > > Hallo Håvard > > > > The planner is supposed to take care of that. It estimates > > ehat is the > > cheapest part of the OR statements and checks that. If true, > > then > > nothing is done with the others. > > > > Do you have spatial indexes on the geometry columns? > > > > Do you know if they are used by the planner? > > > > Even if the indexes is in place and are used this query will > > probably be > > slow because of how the spatial index works. > > > > What the spatial index does for ST_Within and ST_DWithin is to > > tell if > > the geometries has overlapping bounding boxes (or expanded > > bounding > > boxes in ST_DWithin case). If they do the index is of no more > > help and > > the rest of the calculation has to be done vertex by vertex > > which is > > costly. > > > > So, the problem is when the bounding boxes covers many > > geoemtries, then > > the part of the work that the index can help with is small. > > > > There is techniques to slice the big geometry in smaller > > pieces, build a > > new index and things will go faster. > > > > Long roads often have this problem. If you want to find all > > houses along > > a road the bounding box test will find many more houses than > > those close > > to the road (If the road is not going just north/south or > > east/west) > > > > I don't think it should do any difference for the planner but > > I would > > test to build the query with joins instead. > > > > What version of PostGIS are you using? > > > > > > > > > > > > > > > > Regards > > > > Nicklas Avén > > > > > > > > On Thu, 2011-01-13 at 17:47 +0100, Håvard Wahl Kongsgård > > wrote: > > > Hi, I have a spatial query with multiple OR statements, for > > large > > > tables it's very slow. Is it possible to skip the spatial > > lookup on > > > the other conditions if first(previous) condition equal 1, > > and thereby > > > increase the performance? > > > > > > SELECT vciia_main.sitrp,vciia_main.date_time from > > vciia_main, > > > south_vietnam72, roads, rails, houses, city where > > > st_within(vciia_main.geom, south_vietnam72.geom) and > > date_time is not > > > null and (st_dwithin(vciia_main.geom, roads.geom, 500) OR > > > st_dwithin(vciia_main.geom, rails.geom, 500) or > > > st_dwithin(vciia_main.geom, city.geom, 800) or > > > st_dwithin(vciia_main.geom, houses.the_geom, 500)) > > > > > > -- > > > Håvard Wahl Kongsgård > > > Peace Research Institute Oslo (PRIO) > > > > > > http://havard.security-review.net/ > > > > > > > > > > > > > > > > > -- > > Håvard Wahl Kongsgård > > Peace Research Institute Oslo (PRIO) > > > > http://havard.security-review.net/ > > > > > -- Håvard Wahl Kongsgård Peace Research Institute Oslo (PRIO) http://havard.security-review.net/
