[postgis-users] Is it pipe segment or manhole?

[Carl Anderson]

On 04/07/2004 09:17:49 AM, iheanyi Okeh wrote:

> If it lies on a manhole, only one manhole ID needs to be captured and  
> displayed.
> If the address lies on a pipe or line segment, then two manholes, one  
> on either side of the address like this example below, need to be  
> captured and displayed.
> 
> manholeID---------------*----------------------------------- 
> manholeID.
>

So there are two situations to test for with a preference for one
prefer       closest point is a manhole
2nd choice   closest pipe defined by two manholes


> Problem is it doesn't matter whether the address lies on a line/pipe  
> segment or on a manhole, two manhole ids are always returned.
> Any help will be greatly appreciated.
> here is the current code I am using.
> 
> select ident as manhole_ID,
>    distance(shape,'POINT(2354123.1234 6533241.231)'::geometry) as  
> dist
>    from iLandMan
>    where shape && expand('POINT(2354123.1234 6533241.231)':: 
> geometry,150)    order by dist limit 2
>

select ident_1 as down_invert, indent_2 up up_invert, 2 as priority,
    distance(shape,'POINT(2354123.1234 6533241.231)'::geometry)
   as  dist
    from
     iLandMan
     where shape && expand('POINT(2354123.1234 6533241.231)'::
       geometry,150)
UNION
select null as down_invert, ident_2 as up_invert, 1 as priority,
    distance(startpoint(shape),'POINT(2354123.1234 6533241.231)':: 
geometry)
   as  dist
   from iLandMan
   where shape && expand('POINT(2354123.1234 6533241.231)'::
     geometry,150)
UNION
select ident_1 as down_invert, null as up_invert, 1 as priority,
    distance(endpoint(shape),'POINT(2354123.1234 6533241.231)':: 
geometry)
   as  dist
   from iLandMan
   where shape && expand('POINT(2354123.1234 6533241.231)'::
     geometry,150)
order by 4,3  limit 1;

###############
my assumptions when writing the above

In the past when dealing with pipe idents, they have been a  
concatenation of the upstream and down stream manhole (invert) id's.
the upstream and down stream manhole id are also in the tuple.

so I suspect that you have pipe_indent, up_MH_ident, and down_MH_ident
for each pipe.

so for each segment calc the distance to the pipe, the distance to the
up end and the distance to the down end.

use priority to prefer one answer over another.

NOTE   "ORDER BY"  at end affects the entire collection of unioned  
statements not just the last part.

return the nearest 1 pipe or nearest 1 manhole;
(must test of each end in case of dangling pipes)


C.

Carl Anderson