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

[Arne Kepp]

Hi Nicklas,

My understanding of "left join" was the same, but I thought the "outer" 
part would cause it to just run over the left table and try to fill in 
extra columns with the right table. I now see that none of the examples 
I have been looking at really covered this case. I look forward to 
finding out where else I've made this incorrect assumption ;)

You are right that the old query produces the combinations that satisfy 
ST_Contains, I was just not imprecise when describing it.

Thank you for spelling it out in detail, it's very much appreciated.
-Arne


On 1/12/11 12:28 PM, Nicklas Avén wrote:
> 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
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users