[postgis-users] Problem with subselects

[Jan Hartmann]

Dave,

I'm testing with two small tables:

continents: an imported shapefile (SRID 4326) with fields the_geom, 
continent, id
population: a regular PostgreSQL table with fields id, y1975, 1990 and y2000

id is the link-field. I want to make a simple map with the population 
distribution of the world.

Note that this is just for testing. I have made lots of PostGIS maps 
with complex and nested subqueries without any problem. This test-map 
was made for a problem of another user who didn't get expected results. 
One of the things I tried out was the order of the tables in the 
subselect. It does make a difference: if you put the regular (non-map) 
table first you get back an empty map. I forced an error with a faulty 
WHERE clause and looked at the SQL generated by mappostgis.c (newest 
version). The problem comes from the bounding box declaration.

Below are three DATA statements: the first one with the map-table 
(continents) first, the second one with the regular table (population) 
first, and the third one like the second, but with "using SRID=4326" 
specified. The first and third work, the second gives back an empty map. 
As you can see mappostgis.c adds a bounding box constraint, for which it 
uses either the SRID of the first table specified, or the SRID specified 
by "using SRID". This means that in the second case it does a 
"find_srid" on the population table , which doesn't have any geometry. I 
just wonder why it doesn't crash, but gives back an empty map.

As I said, this is a peculiar case. I never met it in all my regular 
maps (always put my maps first), but in some situations it can cause 
problems which are very difficult to track down.

Jan

--------------------------------------------------------------------

The DATA statements and resulting SQL

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

msPOSTGISLayerWhichShapes(): Query error. prep_DB:Error executing 
POSTGIS DECLARE statement (0.6 failed - retried 0.5 and it failed too). 
DECLARE mycursor BINARY CURSOR FOR SELECT 
asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text from 
(select c.the_geom,c.oid,pop.y2000 from continents as c,population as 
pop where pop.id = cc.id) as foo WHERE the_geom && setSRID('BOX3D(-180 
-180,180 180)'::BOX3D, find_srid('','continents','the_geom') )

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

msPOSTGISLayerWhichShapes(): Query error. prep_DB:Error executing 
POSTGIS DECLARE statement (0.6 failed - retried 0.5 and it failed too). 
DECLARE mycursor BINARY CURSOR FOR SELECT 
asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text from 
(select c.the_geom,c.oid,pop.y2000 from population as pop,continents as 
c where pop.id = cc.id) as foo WHERE the_geom && setSRID('BOX3D(-180 
-180,180 180)'::BOX3D, find_srid('','population','the_geom') )

3)
the_geom from
     (select c.the_geom,c.oid,pop.y2000
      from population as po, continents as c
      where c.id = pop.id) as foo using SRID=4326

msPOSTGISLayerWhichShapes(): Query error. prep_DB:Error executing 
POSTGIS DECLARE statement (0.6 failed - retried 0.5 and it failed too). 
DECLARE mycursor BINARY CURSOR FOR SELECT 
asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text from 
(select c.the_geom,c.oid,pop.y2000 from population as pop,continents as 
c where pop.id = cc.id) as foo WHERE the_geom && setSRID('BOX3D(-180 
-180,180 180)'::BOX3D, 4326 )





David Blasby wrote:
> Jan,
> 
> The order of the tables shouldnt matter.  I'm a bit confused as to what you're
> trying to do.
> 
> Could you tell me whats in tables "c" and "pop" and what you mean by projected
> and unprojected maps?  Whats your mapfile look like?
> 
> The two DATA statements you sent should return exactly the same results to
> mapserver so I dont know why it wouldnt be working.
> 
> dave
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>