[postgis-users] Problem with subselects
dblasby at refractions.net
Thu Oct 24 11:25:39 PDT 2002
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
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
> 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.
> name world
> size 500 500
> imagecolor 200 200 200
> imagepath "/***"
> imageurl "/tmp/"
> template "world.html"
> units dd
> extent -180 -90 180 90
> 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
> color 140 150 60
> outlinecolor 100 150 20
> status default
More information about the postgis-users