[postgis-users] subquery on a postgis layer
schabi at logix-tt.com
Mon Mar 13 07:07:13 PST 2006
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.
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
More information about the postgis-users