[postgis-users] Intersection of 2 large maps overlap at a corner

[Obe, Regina]

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: postgis-users-bounces at postgis.refractions.net 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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20081024/fe1459be/attachment.html>