<!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.7653.38">
<TITLE>RE: [postgis-users] Intersection of 2 large maps overlap at a corner</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>If you are running 8.3, I think since we don't have our costs set for spatial functions, the ST_Intersects may be taking higher priority than the other conditions because you have it listed first.<BR>
<BR>
Try doing<BR>
<BR>
SELECT<BR>
ST_Intersection(GeoB.the_geom,GeoQ.the_geom) as intersect_geom,<BR>
GeoB.*,<BR>
GeoQ.*<BR>
FROM<BR>
sj_100k_geopy as GeoB,<BR>
sf_quat_geopy as GeoQ<BR>
WHERE<BR>
GeoB.ptype not in ('Qls','Qls?') AND GeoQ.ptype not in ('br','br?') AND<BR>
ST_Intersects(GeoB.the_geom,GeoQ.the_geom)<BR>
<BR>
<BR>
If that improves things, try setting a cost on the ST_Intersects function to say 150 and see if your original query performs better.<BR>
<BR>
Hope that helps,<BR>
Regina<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of Kevin Neufeld<BR>
Sent: Fri 10/24/2008 3:25 PM<BR>
To: PostGIS Users Discussion<BR>
Subject: Re: [postgis-users] Intersection of 2 large maps overlap at a corner<BR>
<BR>
You're query looks good. Do you have GIST indexes on the geometries in<BR>
both tables?<BR>
<BR>
What is the result of EXPLAIN?<BR>
<BR>
-- Kevin<BR>
<BR>
Bob and Deb wrote:<BR>
> Hello All,<BR>
><BR>
> I'm new to postgis and need help with (what seems to be a simple) query.<BR>
><BR>
> I have 2 geology maps that overlap at a corner:<BR>
><BR>
> ----------------------------<BR>
> | |<BR>
> | |<BR>
> | ---------|--------------<BR>
> | | | |<BR>
> ---------------------------- |<BR>
> | |<BR>
> -----------------------<BR>
><BR>
> Below is the query that I used to do the job, but it takes a long time<BR>
> to finish. Any suggestions on improving it?<BR>
><BR>
> Thanks in advance!<BR>
><BR>
> Bob<BR>
><BR>
> SELECT<BR>
> ST_Intersection(GeoB.the_geom,GeoQ.the_geom) as intersect_geom,<BR>
> GeoB.*,<BR>
> GeoQ.*<BR>
> FROM<BR>
> sj_100k_geopy as GeoB,<BR>
> sf_quat_geopy as GeoQ<BR>
> WHERE<BR>
> ST_Intersects(GeoB.the_geom,GeoQ.the_geom)<BR>
> AND<BR>
> GeoB.ptype not in ('Qls','Qls?')<BR>
> AND<BR>
> GeoQ.ptype not in ('br','br?')<BR>
><BR>
><BR>
> ------------------------------------------------------------------------<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>
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>
<BR>
</FONT>
</P>
</BODY>
</HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
</STRONG></P></BODY></HTML>
<P><hr size=1></P>
<P><STRONG><font size="2" color="339900"> Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper. </p> <p> </font></STRONG></P>