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.
Try doing
SELECT
ST_Intersection(GeoB.the_geom,GeoQ.the_geom) as intersect_geom,
GeoB.*,
GeoQ.*
FROM
sj_100k_geopy as GeoB,
sf_quat_geopy as GeoQ
WHERE
GeoB.ptype not in ('Qls','Qls?') AND GeoQ.ptype not in ('br','br?') AND
ST_Intersects(GeoB.the_geom,GeoQ.the_geom)
If that improves things, try setting a cost on the ST_Intersects function to
say 150 and see if your original query performs better.
Hope that helps,
Regina
-----Original Message-----
From: [EMAIL PROTECTED] on behalf of Kevin Neufeld
Sent: Fri 10/24/2008 3:25 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Intersection of 2 large maps overlap at a corner
You're query looks good. Do you have GIST indexes on the geometries in
both tables?
What is the result of EXPLAIN?
-- Kevin
Bob and Deb wrote:
> Hello All,
>
> I'm new to postgis and need help with (what seems to be a simple) query.
>
> I have 2 geology maps that overlap at a corner:
>
> ----------------------------
> | |
> | |
> | ---------|--------------
> | | | |
> ---------------------------- |
> | |
> -----------------------
>
> Below is the query that I used to do the job, but it takes a long time
> to finish. Any suggestions on improving it?
>
> Thanks in advance!
>
> Bob
>
> SELECT
> ST_Intersection(GeoB.the_geom,GeoQ.the_geom) as intersect_geom,
> GeoB.*,
> GeoQ.*
> FROM
> sj_100k_geopy as GeoB,
> sf_quat_geopy as GeoQ
> WHERE
> ST_Intersects(GeoB.the_geom,GeoQ.the_geom)
> AND
> GeoB.ptype not in ('Qls','Qls?')
> AND
> GeoQ.ptype not in ('br','br?')
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> [email protected]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
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._______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
