Форумы GIS-Lab.info

Код: Выделить всё

---------------------------------------------------------------
DROP TABLE IF EXISTS linefrompoly;

CREATE TABLE linefrompoly AS
SELECT id, ST_MakeLine(sp,ep) AS the_geom
FROM
   -- extract the endpoints for every 2-point line segment for each linestring
   (SELECT
      id,
      ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) as sp,
      ST_PointN(geom, generate_series(2, ST_NPoints(geom)  )) as ep
    FROM
       -- extract the individual linestrings
      (SELECT id, (ST_Dump(ST_Boundary(the_geom))).geom
       FROM polygon -- existing polygonal layer
       ) AS linestrings
    ) AS segments;
---------------------------------------------------------------
DROP TABLE IF EXISTS pointonline;

CREATE TABLE pointonline AS
SELECT b.id AS ponlid, ST_Closestpoint(ST_Collect(a.the_geom), b.the_geom) as the_geom
FROM linefrompoly a INNER JOIN point b
ON ST_Dwithin(a.the_geom, b.the_geom, 100) -- distance for collect points
GROUP BY b.gid, b.the_geom;
---------------------------------------------------------------
DROP TABLE IF EXISTS distance;

CREATE TABLE distance AS
SELECT 
b.the_geom,
b.id, 
ST_Distance_Sphere(a.the_geom, b.the_geom) AS dist
FROM pointonline a , point b
WHERE a.ponlid = b.id
---------------------------------------------------------------