[postgis-users] Problem with subselects

[Jan Hartmann]

(I cross-posted this to the PostGIS user's list. Perhaps this discussion 
could be continued there)

Düster Horst wrote:
 > Hello Jan
 >
 > After the first enthusiasm some problems appeared. The main problem
 > is, it isn't possible to define two different layers with
 > subselections in the same map file. Do you have any experience with
 > this phenomenon?? Perhaps it is a bug in the MapServer-Postgis
 > interface??
 >

First, I couldn't detect any problem in using more than one PostGIS 
layer in a single Mapfile, with or without subselects. Something seems 
to be wrong with the definition of one of your layers.

However, there is a potential problem with subselects on non-projected 
data (i.e. with SRID = -1).
To test this out I used the following three tables:

continents: the continents with projection latlon (SRID 4326)
continents2: the same, but unprojected (SRID -1)
population: population for 1950, 1975 and 2000

DATA statements:

the_geom from (select oid,* from continents) as foo  : OK
the_geom from (select OID,* from continents) as foo  : OK
the_geom from (select oid,* from continents2) as foo : OK
the_geom from (select c.oid,c.the_geom, pop.y2000
     from continents as c,population as pop
     where c.id = pop.id) as foo                      : OK
the_geom from (select c2.oid,c2.the_geom, pop.y2000
     from continents2 as c2,population as pop
     where c2.id = pop.id) as foo                     : OK
the_geom from (select c.oid,c.the_geom, pop.y2000
     from continents as c,population as pop
     where c.id = pop.id) as foo using SRID=4326      : OK
the_geom from (select c2.oid,c2.the_geom, pop.y2000
     from continents2 as c2,population as pop
     where c2.id = pop.id) as foo using SRID=4326     : Crashes

As you can see:
- oid can be spelled upper- or lowercase
- simple queries work on projected and unprojected maps
- subqueries also work with projected and unprojected maps
   as long as you don't specify the SRID explicitly
- unprojected maps can't be used in a subquery which specifies
   the SRID

This seems logical to me: it doesn't make sense to try to reproject 
non-projected data. Dave Blasby's postings on this subject always 
specify "using SRID" and this makes sense to me too, although it doesn't 
seem obligatory. Personally I always put my data into some world 
projection, even when this is difficult (e.g. hand-digitized field maps, 
raster data or CAD/CAM sources).

Hope this helps you further.

Jan Hartmann
Department of Geography
University of Amsterdam
jhart at frw.uva.nl