postgis table joins in layer DATA and FILTER

[Stephen Woodbridge]

Hi David,

Try this:

DATA "the_geom from (select the_geom, name, checked, gid from sm100k a, 
files b where a.uid=b.uid ) as foo using SRID=4326 using unique gid"

You have to put your joins into a sub-select.

Regards,
  -Steve W

David Niergarth wrote:
> I'm wondering if there is a way to join two postgres tables within a 
> layer object.
> 
> We have a postgres table with a postgis geometry column and other 
> details (name, address columns). Problem is, we need to join that table 
> with a couple of other tables in the same database.
> 
> We have tried getting around the join problem by creating a view that 
> hides the needed joins but performance is terrible. We have also tried 
> using an exists subselect but performance is even worse. Creating a 
> custom table for each map we want to generate is completely impractical 
> due to the size of the database.
> 
> However, writing sql as joins is super quick.
> 
> Here's an example of what we'd like to accomplish.
> 
> DATA "p.the_geom from people_points p, spam_table s, eggs_table e"
> FILTER "p.id=s.id and p.id=e.id and s.spam='foo' and e.eggs='bar'"
> 
> Is there some other way to do this without having to create a (massive 
> and unnormalized) table with all the info we need or a view?
> 
> We're using postgres 8.1, mapserver 4.9, ubuntu server 7.04. We've heard 
> there were improvements to postgres (maybe in 8.2) that improved view 
> performance, but it's not practical for us to upgrade right now.
> 
> Any info or suggestions welcome,
> 
> --David Niergarth