[postgis-users] Re: SRID, misc queries
Per-Olof Norén
pelle at alma.nu
Wed Oct 17 15:05:23 PDT 2007
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>
More information about the postgis-users
mailing list