[postgis-users] spatial analysis - contains

[maplabs at light42.com]

>
> From: shreyes shiv <shivshreyes at gmail.com>
> Subject: [postgis-users] spatial analysis - contains
>
> I have two shape files loaded onto Postgis. Both are of the same spatial
> extent. 
> I wan to know wat all polygons of one shape file lies in the polygon of the
> other shape file. How to do u i write the query for this. 
> Please help
>

Say there are two tables from the Natural Earth data set,  found at 
  http://www.naturalearthdata.com/

  10m_lakes 
  10m_admin_1_states_provinces

Say you want to find all lakes that occur in a single state/province 
called "California"

SELECT
  lakes.name1
FROM
  "10m_lakes" as lakes,
  "10m_admin_1_states_provinces" as provs
WHERE
  st_contains( provs.the_geom, lakes.the_geom) AND
  provs.name_1 = 'California'

Is that what you were asking?

==
Brian Hamlin
planetwork.net
OSGEo California Chapter
(415) 717-4462 cell

> -- shreyes shiv
> email: shivshreyes at gmail.com
> phone: 9557975780
> IIRS(Indian Institute of Remote Sensing)
> No. 4, Kalidas Road, Dehradun-248001, Uttarakhand, India