Created
January 19, 2017 15:13
-
-
Save ontologiae/8c3b00555802ebd5e66cdb8a2cfa45a2 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment