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
