[postgis-users] retrieving overlapping geometries ???

[Paragon Corporation]

 You probably want to use UNION ALL in this case because with plain UNION
you incur an implicit DISTINCT penalty.

SELECT foo,bar 
FROM texas 
WHERE st_intersects(the_geom, 'POLYGON()') 
UNION ALL
SELECT foo,bar 
FROM virginia 
WHERE st_intersects(the_geom, 'POLYGON()')

Leo

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paul
Ramsey
Sent: Saturday, May 16, 2009 4:22 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] retrieving overlapping geometries ???

Leaving aside that's kind of an ugly data model and you might consider
putting everything into one table:

select foo,bar from texas where st_intersects(the_geom, 'POLYGON()') union
select foo,bar from virginia where st_intersects(the_geom, 'POLYGON()')

P

On Sat, May 16, 2009 at 7:23 AM, Smith Roman <autisi at yahoo.com> wrote:
> Hi,
> My database has street maps of different states. Each state is 
> represented by a table. How can i fetch the map of an area (assuming I 
> have the bbox of the area) whose geometries overlap two or more states
(tables) .
> How would the SQL query be written ?
> Smith.
>
>
> _______________________________________________
> 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


__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4080 (20090515) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com