[postgis-users] Re: SRID, misc queries

[Per-Olof Norén]

Brian,

Did you tests the query i sent?

I tested the theory of using the && operator on a similar set of data.
One, the big set is 18000 rows with one polygon geometry per row 
(representing map grids).
The other smaller set is multipolygons representing swedish municipalies 
(250 rows)

Attatched is an image describing the three different queries:

1) Your style
select  *
from     ekoblad, sweden_municipality   
where     sweden_municipality.knkod='0136'
     and st_within(ekoblad.the_geom, sweden_municipality.the_geom)
   
This took 400ms

2) Using a subselect to fetch the limiting geometry
select  *
from     ekoblad   
where     st_within(ekoblad.the_geom, (select the_geom from 
sweden_municipality where knkod='0136'))

This took 200 ms

Using a subselect and using the && operator
select  *
from     ekoblad   
where     st_within(ekoblad.the_geom, (select the_geom from 
sweden_municipality where knkod='0136'))
    and ekoblad.the_geom && (select the_geom from sweden_municipality 
where knkod = '0136')

This took 35 ms and was making use of the indices!

The blue polygons in the attached image are actual municipality polygon
The red ones are the result of *only* using the && operator

The black boxes are the ones with both st_within and && operator.

What happens is that the red grids are looked up against index, but are 
not truly within, since they were fetched with bbox, but adding the 
extra, computationally heavier st_with to the mix, you get the expected 
result...

You should be able to rewrite your queries according this

Regards,
Pelle



Brian Hamlin skrev:
> thank you, Per-Olof, what you say sounds reasonable
>
> I think some of my troubles mentioned here, the Topological 
> Exception/CRASH might have to do with MULTIPOLYGON that fails 
> IsValid() in PostGIS, but are ok in other systems..
>
>   Self Intersection at or near Point xxx  for a dozen or so
>
> So I got rid of those, so far, I have no crashes
> but the searches are slower than ever
>
> yes, I obviously need to find ways to make use of bounding box, 
> although what I really want are the POLY contents
>
> ps -It is easy to say 'read the list' , but the list is years long... 
> I have read dozens of pages of posts before I started this thread.. so 
> believe me that i am not just complaining.. I am reading, studying, 
> and complaining ;-)
>
> the combination of crash and long queries, and being new to this on my 
> own, makes for very frustrating entrance to the black magic of PostGIS !!
>
> all for now  -Brian
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
A non-text attachment was scrubbed...
Name: scrshot.png
Type: image/png
Size: 68907 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20071018/9ad8cbc1/attachment.png>