[postgis-users] Problem with subselects

[Jan Hartmann]

Hello Dave,

Yes, I used an older version, and the new version solves some of the 
problems in using both projected and unprojected tables in one query. 
There remains a problem in the new version however: the order of the 
tables in the subselect seems to matter. Consider these two data 
statements (with c as the continents map and pop as the population table):

the_geom from (select c.oid,c.the_geom,pop.y2000
                from c,pop
                where c.id = pop.id
                ) as foo

and:

the_geom frm (select c.oid,c.the_geom,pop.y2000
                from pop,c
                where c.id = pop.id
                ) as foo

The first one now works with both projected and unprojected maps (that 
is the difference with the older mappostgis.c, in which only projected 
maps were allowed). The second, however, gives back an empty picture, 
i.e. not an error message but a map with nothing in it. The only 
difference is the order in which the tables are declared. If I add 
"using SRID=4326" in 2 it works with a projected map, but not with an 
unprojected one, not even with SRID = -1. In that case it gives back an 
error.

I got a mail with this kind of problem (getting back an empty map from a 
subquery) from Alex Pucher in Vienna and we both did a lot of searching 
on it. I thought it had to do with projections, but that's partly solved 
with the new mappostgis.c. Could it be that in a subselect you have to

either:
    declare the map table as the first one in the subselect,
    in which case "using SRID" is not obligatory (though recommended)
or:
    use "using SRID", but only with projected maps?

Jan

David Blasby wrote:
> Jan,
> 
> Are you using the most up-to-date mappostgis.c from the CVS?
> 
> If you're not, there are a few known problems with sub-selects and using
> more-than-one-subselect-in-a-mapfile that this version fixes.
> 
> Also, it will give you much more detailed error messages.
> 
> dave
> ps. I've encluded the  mappostgis.c file.  Replace your version with
> this then recompile and install.
>