Hallo Arne As I understand you, you say two different things here.
First: I think you are misunderstanding how left join works. left join is an inner join plus all remaining records of the left table. That means you cannot use the left join in your case to only get one result from many point-polygon combinations. Then you write: > But the query above produces one row for every point/polygon > combination. So it's essentially behaving like a cross join? Do you really mean it returns all combinations of points and polygons, or does it return all combinations that satisfy ST_Contains. The expected result from your query SELECT sample_id, sample_date, ST_AsText(point_layer.the_geom), polygon_value FROM point_layer LEFT OUTER JOIN polygon_layer ON ST_Contains(polygon_layer.the_geom, point_layer.the_geom) WHERE point_layer.active = 1 is all row combinations where a point is inside any polygon plus all the rest of the point rows (in those cases there will be null in the fields from the polygon table. What you should do instead is using distinct on that value from the polygon table you want. Could look something like DISTINCT ON (location) location SELECT DISTINCT ON (sample_id) sample_id, sample_date, ST_AsText(point_layer.the_geom), polygon_value FROM point_layer INNER JOIN polygon_layer ON ST_Contains(polygon_layer.the_geom, point_layer.the_geom) WHERE point_layer.active = 1 you can read about distinct here: http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-DISTINCT HTH Nicklas > Any suggestions on what I am doing wrong or how I can get the result I > am after? Using Postgis 1.3.6 on Postgresql 8.4.4 > > Thank you, > -Arne > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
