[postgis-users] subquery on a postgis layer

[Markus Schaber]

Hi, Toni,

toni hernnadez wrote:
> I have several tables related with the urban planning in my town.
> Two of these tables are:
> Urban Soil 2006, and green areas 1992. Both have a geom field called
> "geom" and some other alfanumeric fields. There is no relation between
> these two tables.
> 
> I would like to show the intersection of these two layers to show which
> green areas have turn into urban soil.
> I haven't either UNION nor JOIN. The thing is I don't know if this is
> possible and if so.. how to do it.

IMHO, this is a typical case for JOIN.

You want something like

SELECT urban.id, green.id, urban.geom, green.geom, whatever_else
  FROM urban JOIN green
    ON urban.geom && green.geom -- speedup bbox check for spatial index
    AND intersects(urban.geom, green.geom);

I don't know whether mapserver can do JOINs, but it can be tricked with
a VIEW afaik.


HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org