[postgis-users] Re: SRID, misc queries

[Per-Olof Norén]

Brian,

I think i've heard people talk about ways to get the most out of indexes 
on the list earlier.
The gist index used in postgis uses the extents (bounding box) of the 
geometries as the indexed value.
Geospatial functions involving the support libraries (like st_within, I 
imagine) are more expencive (no surprise, given the math involved) than 
vanilla where statements.
To get the most bang for the buck, cpu-wise, one has to try to use of 
procedures, operators and filters in an optimized way.

Try reduce amount of geospatial processing that needs to be done.
There is a trick to help a little here: knowing that index is bounding 
box and that the spatial operators makes heavy use of the indexes.

To med your query looks like what I call a spatial join condition.
General hints on performance can be found here: 
http://postgis.refractions.net/docs/ch05.html

I'd like to see the execution plan of your query, along with this one:

select   name
from     gectable
where  geom_pts2 && (select the_geom from just_ca where  city = 'Berkeley' )
             and st_within(geom_pts2, (select the_geom from just_ca 
where  city = 'Berkeley' ))


http://postgis.refractions.net/docs/ch05.html

Anyways, its a little black magic involved in query tuning, and 
especially so with spatial queries.

Good luck,



Brian Hamlin skrev:
> ok, no one bothered to point out my obvious mistake in the order 
> lon/lat in the Polygon query...
>
> Aside from that, I just double checked the SRIDs, dropped and reloaded 
> the GIST indexes for both the POINT and MULTIPOLYGON tables, both in 
> 4326, vacuumed and vacuumed again.
>
> The basic question still stands - if I dont SetSRID(), I get a mixed 
> SRID error msg
> with the SetSRID(), the Points in Polys takes 10 seconds to execute. 
> That seems like a long time.
>
> Is this expected performance?
> Mac OS 10.10,  Powerbook 1ghz g4, decent 54gb internal drive
>
> Why the mixed SRIDs error without the explicit calls?
>
>>
>> SELECT gectable.name
>> FROM just_ca, gectable
>> WHERE
>>         just_ca.city = 'Berkeley'
>> AND
>>  st_within( SetSRID(gectable.geom_pts2,4326),
>>   SetSRID( just_ca.the_geom,4326))
>>
>> this takes 10 seconds on 110,000 points (and 12 polys in city Berkeley).
>> (I added a GIST index on geom_pts2 and the_geom, and the query took
>> the same amount of time as without indexes.  just_ca.city = 
>> 'Berkeley' alone
>> takes just 4ms)
>>
>> The following query gets a mixed SRID error
>>
>> SELECT gectable.name
>> FROM just_ca, gectable
>> WHERE
>>         just_ca.city = 'Berkeley'
>> AND
>>  st_within(  gectable.geom_pts2, just_ca.the_geom )
>>
>> why??
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users