[postgis-users] Problem with subselects

[David Blasby]

I looked at the code, and it was being too smart for its own good.  If you have
sub-select (without specifing using SRID=xxx), it finds the first table mentioned in your
"from" clause and tries to determine its geometry SRID by consulting the geometry_columns
table.  It should actually throw an error.

I'll fix this up soon. This explains why the order of tables in your "from" clause
matters.

dave

Jan Hartmann wrote:

> David Blasby wrote:
> > The find_srid('','population','the_geom') function looks in
> > geometry_columns to find the SRID of the geometry.  If you're using
> > subselects you *must* use the "using SRID=xxxx" clause!
> >
> > My bet is that the find_srid('','population','the_geom') function is
> > returning NULL (it cannot find the SRID in the geometry_columns table).
> > That would make the && function always return null, and you'll get a blank
> > map back.  geometry_columns should be automagically maintained if you use
> > AddGeometryColumn() and DropGeometryColumn().
> >
> > I dont know why mappostgis.c isnt throwing an error when you try to do this
> > type of DATA statement.  Could you send me your actual map file?
> >
> > dave
> >
>
> Dave,
>
> This is the complete mapfile, with the three DATA statements. The middle
> one (uncommented) produces the blank map. The template is just
> <img src =[img]> . Table "continents" is imported from our ArcView
> distribution, and I made "population" myself with some numbers from the
> Net. I didn't add geometry columns, because these are just statistics
> (like most tables I want to link to my maps).
>
> I always use "using SRID" myself. Didn't know it was obligatory in
> subselects, though. Most subselects seem to run fine without it.
>
> Thanks for your trouble.
>
> Jan
>
> -------------------------------------------------------------------------------------
>
> name world
> size 500 500
> imagecolor 200 200 200
> web
>    imagepath "/***"
>    imageurl "/tmp/"
>    template "world.html"
> end
>
> projection
>    "proj=latlong"
>    "ellps=clrk66"
> end
> units dd
> extent -180 -90 180 90
>
> layer
>    name continents
>    connectiontype postgis
>    connection "user=*** dbname=*** password=*** host=***"
> #data "the_geom from (select c.the_geom,c.oid,pop.y2000 from continents
> as c,population as pop where pop.id = c.id) as foo"
> data "the_geom from (select c.the_geom,c.oid,pop.y2000 from population
> as pop,continents as c where pop.id = c.id) as foo"
> #data "the_geom from (select c.the_geom,c.oid,pop.y2000 from population
> as pop,continents as c where pop.id = c.id) as foo using SRID=4326"
>    type polygon
>    class
>       color 140 150 60
>          outlinecolor 100 150 20
>    end
>    status default
> end
>
> end