{"action":"opened","issue":{"url":"https://api.github.com/repos/CartoDB/crankshaft/issues/125","repository_url":"https://api.github.com/repos/CartoDB/crankshaft","labels_url":"https://api.github.com/repos/CartoDB/crankshaft/issues/125/labels{/name}","comments_url":"https://api.github.com/repos/CartoDB/crankshaft/issues/125/comments","events_url":"https://api.github.com/repos/CartoDB/crankshaft/issues/125/events","html_url":"https://github.com/CartoDB/crankshaft/issues/125","id":175942521,"number":125,"title":"Improved _Signed_Dist for PIA","user":{"login":"pnorman","id":1190866,"avatar_url":"https://avatars.githubusercontent.com/u/1190866?v=3","gravatar_id":"","url":"https://api.github.com/users/pnorman","html_url":"https://github.com/pnorman","followers_url":"https://api.github.com/users/pnorman/followers","following_url":"https://api.github.com/users/pnorman/following{/other_user}","gists_url":"https://api.github.com/users/pnorman/gists{/gist_id}","starred_url":"https://api.github.com/users/pnorman/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/pnorman/subscriptions","organizations_url":"https://api.github.com/users/pnorman/orgs","repos_url":"https://api.github.com/users/pnorman/repos","events_url":"https://api.github.com/users/pnorman/events{/privacy}","received_events_url":"https://api.github.com/users/pnorman/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2016-09-09T07:08:30Z","updated_at":"2016-09-09T07:08:30Z","closed_at":null,"body":"[13_PIA.sql](https://github.com/CartoDB/crankshaft/blob/develop/src/pg/sql/13_PIA.sql) includes a function for calculating signed distance between a point and polygon.\r\n\r\nI had to write the same logic, and wrote an improved version as SQL, and thought it might be useful here.\r\n\r\n```sql\r\n-- signed distance point to polygon with holes\r\n-- negative is the point is out the polygon\r\nCREATE OR REPLACE FUNCTION _Signed_Dist(\r\n    IN polygon geometry(MultiPolygon),\r\n    IN point geometry(Point)\r\n)\r\nRETURNS double precision AS $$\r\nWITH lines(geom) AS (SELECT \r\n  ST_ExteriorRing( -- turn all rings into linestrings\r\n    (ST_DumpRings( -- get rings as polygons\r\n      (ST_Dump(polygon)).geom -- turn multipolygons into polygons\r\n    )).geom\r\n  )\r\n)\r\nSELECT min(ST_Distance(point, geom)) * CASE WHEN ST_Within(point,polygon) THEN 1 ELSE -1 END FROM lines;\r\n$$ LANGUAGE SQL IMMUTABLE;\r\n\r\nCREATE OR REPLACE FUNCTION _Signed_Dist(\r\n    IN polygon geometry(Polygon),\r\n    IN point geometry(Point)\r\n)\r\nRETURNS double precision AS $$\r\nWITH lines(geom) AS (SELECT \r\n  ST_ExteriorRing( -- turn all rings into linestrings\r\n    (ST_DumpRings( -- get rings as polygons\r\n      polygon -- as a polygon we don't need to ST_Dump\r\n    )).geom\r\n  )\r\n)\r\nSELECT min(ST_Distance(point, geom)) * CASE WHEN ST_Within(point,polygon) THEN 1 ELSE -1 END FROM lines;\r\n$$ LANGUAGE SQL IMMUTABLE;\r\n```\r\n\r\nA couple of notes\r\n\r\n* There are two versions so that the function signature can restrict the arguments to polygons and multipolygons, also polygons are slightly simpler. You might not need both of them for PIA.\r\n* A `double precision` is returned because that's what ST_Distance returns, so there's no advantage to using numeric\r\n\r\nIf you want the exact same signature as now, you can adapt them, but I wrote something more general purpose.\r\n\r\nI haven't benchmarked the performance. On small polygons it will be faster because SQL is faster than plpgsql, and large polygons should be no slower because both have to call ST_Within and ST_Distance the same number of times. I suspect large polygons will end up slightly faster."}}
{"actor":{"display_login":"pnorman"}}