[postgis-users] Help with query

[Paragon Corporation]

Mark,
I guess my skills are a bit rusty in Mapserver.  My first question is if
they are both in PostgreSQL, why are you using OGR JOIN for this?

Would seem better and more efficient to just do a JOIN in SQL which it seems
you are doing already.  You may actually want to separate into 2 layers one
that has your grid and one that returns your species. 

Then what you have in TEMPLATE you put in HEADER instead
And then have a TEMPLATE file with just the attributes as you want them laid
out.

Nowadays I just do Mapserver WFS/WMS calls for this kind of thing and parse
the WFS XML in OpenLayers and overlay the WMS images and skip the whole
template exercise altogether.

Anyrate might be worthwhile to ask this question in Mapserver mailing list.

Hope that helps,
Regina

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Mark
Sent: Thursday, November 27, 2008 2:01 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Help with query

Hi All

I am currently trying to create a query to pass through to mapserver but am
having a problem. Here is my scenario:

Two tables in postgresql

A quarter degree grid with 273600 records (spatial geometry) with GiST index
on the geometry and a btree index on FID

A species table with 10 million records (btree index on FID1) contains
species ids with related grid cell ids (essentially an intersect)

In mapserver I have defined the layer thus

LAYER
 CONNECTIONTYPE postgis
 NAME "SpcDensity"
 CONNECTION "user="" password="" dbname="" host=localhost"
 DATA "the_geom FROM (select spcdensity.fid_1, spcdensity.the_geom AS
the_geom, gid AS gid, spcgrid.spcid from spcdensity, spcgrid where
spcdensity.fid_1 = spcgrid.fid) as myquery using SRID=4326 using unique gid"
 STATUS OFF
 TYPE POLYGON
 TEMPLATE "templates/spc_header.html"
 CLASS
 NAME "density"
  OUTLINECOLOR 128 128 128
 END
 JOIN
    NAME "species"
    CONNECTION "host=localhost port=5432 user="" password="" dbname="""
    CONNECTIONTYPE ogr
    TABLE "spcgrid"
    FROM "fid_1"
    TO "fid"
    TYPE ONE-TO-ONE
  END
END

When I run this it works fine, click in a quarter degree grid cell and it
returns a species ID using the one-to-one join but I need to get it to
return the many species ids. Changing the join type to ONE-TO-MANY results
in the error:

loadJoin(): Premature End-of-File. One-to-many joins must define template
and name properties

If anyone has any suggestions as to how I can get this to work I would be
most grateful.

Thanks in advance

Mark

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users