<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7652.24">
<TITLE>RE: [postgis-users] Re: SRID, misc queries</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>Brian --<BR>
<BR>
Vacuum cleans out dead tuples from tables; did you perhaps mean you ran "ANALYZE" ? Which is the beast that does the statistics update that the planner needs to make use of indexes.<BR>
<BR>
HTH,<BR>
<BR>
Greg Williamson<BR>
Senior DBA<BR>
GlobeXplorer LLC, a DigitalGlobe company<BR>
<BR>
Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.<BR>
<BR>
(My corporate masters made me say this.)<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of Brian Hamlin<BR>
Sent: Wed 10/17/2007 12:36 PM<BR>
To: postgis-users@postgis.refractions.net<BR>
Subject: [postgis-users] Re: SRID, misc queries<BR>
<BR>
ok, no one bothered to point out my obvious mistake in the order<BR>
lon/lat in the Polygon query...<BR>
<BR>
Aside from that, I just double checked the SRIDs, dropped and reloaded<BR>
the GIST indexes for both the POINT and MULTIPOLYGON tables, both in<BR>
4326, vacuumed and vacuumed again.<BR>
<BR>
The basic question still stands - if I dont SetSRID(), I get a mixed<BR>
SRID error msg<BR>
with the SetSRID(), the Points in Polys takes 10 seconds to execute.<BR>
That seems like a long time.<BR>
<BR>
Is this expected performance?<BR>
Mac OS 10.10, Powerbook 1ghz g4, decent 54gb internal drive<BR>
<BR>
Why the mixed SRIDs error without the explicit calls?<BR>
<BR>
><BR>
> SELECT gectable.name<BR>
> FROM just_ca, gectable<BR>
> WHERE<BR>
> just_ca.city = 'Berkeley'<BR>
> AND<BR>
> st_within( SetSRID(gectable.geom_pts2,4326),<BR>
> SetSRID( just_ca.the_geom,4326))<BR>
><BR>
> this takes 10 seconds on 110,000 points (and 12 polys in city<BR>
> Berkeley).<BR>
> (I added a GIST index on geom_pts2 and the_geom, and the query took<BR>
> the same amount of time as without indexes. just_ca.city = 'Berkeley'<BR>
> alone<BR>
> takes just 4ms)<BR>
><BR>
> The following query gets a mixed SRID error<BR>
><BR>
> SELECT gectable.name<BR>
> FROM just_ca, gectable<BR>
> WHERE<BR>
> just_ca.city = 'Berkeley'<BR>
> AND<BR>
> st_within( gectable.geom_pts2, just_ca.the_geom )<BR>
><BR>
> why??<BR>
<BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>