[postgis-users] Problem with subselects
Jan Hartmann
jhart at frw.uva.nl
Wed Oct 23 08:30:55 PDT 2002
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.
>
More information about the postgis-users
mailing list