Alignements.sql

create table threePointsData( tid serial primary key, p1 geometry(Point,4326),  p2 geometry(Point,4326), p3 geometry(Point,4326),n1 text,  n2 text,  n3 text, k1 text,  v1 text,  k2 text,  v2 text, k3 text,  v3 text,
ang double precision, distp1p2 double precision, distp2p3 double precision, distp1p3 double precision, bearing double precision);

insert into threePointsData(p1,p2,p3, n1,n2, n3, k1, v1, k2, v2, k3, v3, ang, distp1p2, distp2p3, distp1p3, bearing)
SELECT p1.point, p2.point, p3.point, p1.nom, p2.nom, p3.nom,  p1.tagk, p1.tagv, p2.tagk, p2.tagv,  p3.tagk, p3.tagv, 
    abs
    (        
       degrees(
            ST_Azimuth( p3.point ,  p2.point) -- Pc, Pb            
            -
            ST_Azimuth (  p1.point,  p2.point) -- Pa, Pb
       )--::decimal % 180.0     
), 
ST_Distance_Sphere(p1.point,p2.point), ST_Distance_Sphere(p2.point,p3.point), ST_Distance_Sphere(p1.point,p3.point),
st_azimuth(p1.point,  p3.point)
from pois p1, pois p2, pois p3 where p1.piid < p2.piid AND p2.piid < p3.piid 
and ST_Distance_Sphere(p1.point,p2.point) < 32000 and ST_Distance_Sphere(p2.point,p3.point) < 32000 AND ST_Distance_Sphere(p1.point,p3.point) < 32000;

select n1, st_y(p1) || ',' || st_x(p1), n2,  st_y(p2) || ',' || st_x(p2), n3, st_y(p3) || ',' || st_x(p3), ang,  distp1p2, distp2p3, distp1p3
, bearing
from threePointsData  where ang < 2.5 
and (distp1p2 > 800 AND distp2p3 > 800 AND distp1p3 > 800) -- > 800m sinon c'est débile et on vire Carnac
and not st_intersects(p1,st_setsrid(ST_MakePolygon(ST_GeomFromText('LINESTRING(-3.085699 47.590912, -3.074155 47.594371, -3.064435 47.595398,-3.056989 47.599074, -3.058255 47.600549, -3.068318 47.596541, -3.074627 47.595702, -3.085742 47.592548, -3.085699 47.590912)')),4326))
and not st_intersects(p2,st_setsrid(ST_MakePolygon(ST_GeomFromText('LINESTRING(-3.085699 47.590912, -3.074155 47.594371, -3.064435 47.595398,-3.056989 47.599074, -3.058255 47.600549, -3.068318 47.596541, -3.074627 47.595702, -3.085742 47.592548, -3.085699 47.590912)')),4326))
and not st_intersects(p3,st_setsrid(ST_MakePolygon(ST_GeomFromText('LINESTRING(-3.085699 47.590912, -3.074155 47.594371, -3.064435 47.595398,-3.056989 47.599074, -3.058255 47.600549, -3.068318 47.596541, -3.074627 47.595702, -3.085742 47.592548, -3.085699 47.590912)')),4326))
-- On vire tous les intrus qu'il va falloir vider dans la base TODO
and n1 not ilike '%moulin%' and n2 not ilike '%moulin%' and n3 not ilike '%moulin%'
and n1 not ilike '%soude%'  and n2 not ilike '%soude%'  and n3 not ilike '%soude%'
and n1 not ilike '%four%'  and n2 not ilike '%four%'  and n3 not ilike '%four%'
and n1 not ilike '%lavoir%'  and n2 not ilike '%lavoir%'  and n3 not ilike '%lavoir%'
order by bearing--, log(distp1p2*distp2p3*distp1p3);