[postgis-users] get the biggest intersection

[Stephen Woodbridge]

On 6/20/2012 10:43 AM, Denis Rouzaud wrote:
> Hi Stefen, Hi Fred,
>
> Thank you both for your answer.
> There was effectively an invalidity in a geometry.
>
> Now my function is working great.
>
> But still, do you think it is a good idea to use an "order by" and
> "limit 1" to have the desired result?

Yes, I have done similar queries. As an alternative you could split the 
lines into multiple segments and then assign attributes to them based on 
the polygons they fall into.

Imagine the case of a polyline that travels from city A to city B and 5% 
of the polyline is in A and 15% of the polyline is in B and 80% of the 
polyline is not in A or B but the rural arear between the cities. In you 
case you would always report it as being in B and 85% of the time that 
would be wrong. Chopping the linestring into the 3 pieces is a lot more 
work but would give better results.

So the correct answer for you has more to do with what your data 
represents and if your polygons are discrete or form a coverage, etc. 
and how tolerant you are to reporting errors as described above.

-Steve

> Thanks again,
>
> Denis
>
> On 06/20/2012 04:27 PM, Stephen Woodbridge wrote:
>> Try:
>>
>> select count(*) from polygons where not isvalid(geometry);
>>
>> if count > 0 then
>>
>> select count(*) from polygons where not isvalid(geometry) and not
>> isvalid(st_buffer(geometry, 0.0));
>>
>> you can do an update to fix these:
>>
>> update polygons set geometry=st_buffer(geometry, 0.0) where not
>> isvalid(geometry);
>>
>>
>> -Steve W
>>
>> On 6/20/2012 7:54 AM, Denis Rouzaud wrote:
>>> Hi all,
>>>
>>> I have a table of line and a table of polygons. For a given line, I
>>> would like to get the polygon which has the biggest intersection with
>>> the line.
>>> I tried something like this:
>>>
>>> CREATE OR REPLACE FUNCTION distribution.get_zone_id(geometry) RETURNS
>>> integer AS '
>>>      DECLARE
>>>          inputgeom ALIAS FOR $1;
>>>          id_poly integer;
>>>      BEGIN
>>>          SELECT id INTO id_poly
>>>              FROM  polygons
>>>              WHERE ST_Intersects(inputgeom,geometry) IS TRUE
>>>              ORDER BY ST_Length(ST_Intersection(inputgeom,geometry))
>>> DESC
>>>              LIMIT 1;
>>>          RETURN id_poly;
>>>      END
>>> ' LANGUAGE 'plpgsql';
>>>
>>> But I have the following error:
>>> ERROR:  Error performing intersection: TopologyException: side location
>>> conflict at 553524.92178241001 147945.03792368001
>>>
>>> If anyone has any idea, tip or whatever, it is very welcome!
>>>
>>> Greetings,
>>>
>>> Denis
>>> _______________________________________________
>>> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users