[postgis-users] get the biggest intersection

[Denis Rouzaud]

Hi,

Thanks again.

Well, I have water pipes and consumption areas. So, the described method 
should be correct. Moreover, the intersection is a first draft, as it 
has to be manually corrected afterwards: a pipe might belong to an area 
which it does not intersect.

I was more thinking in terms of sql efficiency. But I tested, and it 
seems fast enough regarding my db volume.

Best regards,

Denis

On 06/20/2012 05:34 PM, Stephen Woodbridge wrote:
> 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users