[postgis-users] left outer join not working as expected with ST_Contains

[Nicklas Avén]

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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>