[postgis-users] Re: SRID, misc queries

[Brian Hamlin]

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??