Форумы GIS-Lab.info

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

ogr2ogr -f GML test.gml test.shp 

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

---------------------------------------------------------------------
--1.1
DROP TABLE IF EXISTS "point";
CREATE TABLE stp AS SELECT ST_StartPoint(the_geom) AS the_geom FROM roads;
CREATE TABLE enp AS SELECT ST_EndPoint(the_geom) AS the_geom FROM roads;
CREATE TABLE point AS
SELECT * FROM stp
UNION
SELECT * FROM enp;
ALTER TABLE "point" ADD COLUMN nam TEXT;
DROP TABLE IF EXISTS stp;
DROP TABLE IF EXISTS enp;
DROP SEQUENCE IF EXISTS point_gid_seq;

CREATE SEQUENCE point_gid_seq START 1;
UPDATE "point" SET nam = NEXTVAL('point_gid_seq');
CREATE INDEX sidx_point_the_geom
  ON point
  USING gist
  (the_geom);
---------------------------------------------------------------------
--1.2
SELECT r.the_geom, r.gid , p.nam INTO roads_with_names FROM roads r, point p WHERE ST_Intersects(r.the_geom,p.the_geom) IS TRUE GROUP BY r.gid, p.nam;
CREATE INDEX sidx_roads_with_names_the_geom
  ON roads_with_names
  USING gist
  (the_geom);
---------------------------------------------------------------------
--1.3
CREATE TABLE roadsdupl AS
SELECT the_geom, array_to_string(array_agg(nam), ',') AS name
FROM roads_with_names
GROUP BY the_geom;
---------------------------------------------------------------------
--1.4
CREATE INDEX sidx_roadsdupl_the_geom
  ON roadsdupl
  USING gist
  (the_geom);
CREATE TABLE poiwithduplname AS
SELECT
poi.the_geom,
poi."nam",
array_to_string(array_agg(name), ',') AS pointsum
FROM
point AS poi,
roadsdupl AS lin
WHERE ST_Intersects(lin.the_geom, poi.the_geom)
GROUP BY poi."nam", lin.name, poi.the_geom;
---------------------------------------------------------------------
--1.5
CREATE INDEX sidx_poiwithduplname_the_geom
  ON poiwithduplname
  USING gist
  (the_geom);
CREATE TABLE poidupl AS
SELECT the_geom, nam, array_to_string(array_agg(pointsum), ',') AS fullname
FROM poiwithduplname
GROUP BY nam, the_geom;
--------------------------------------------------------------------
--1.6
CREATE INDEX sidx_poidupl_the_geom
  ON poidupl
  USING gist
  (the_geom);
DROP TABLE IF EXISTS "poifin";
CREATE TABLE poifin AS
SELECT the_geom, nam, REPLACE(REPLACE(fullname, nam||',', ''), ','||nam, '') AS sosedi FROM poidupl;
---------------------------------------------------------------------
--1.7
DROP TABLE IF EXISTS "roads_with_names";
DROP TABLE IF EXISTS "poiwithduplname";
DROP TABLE IF EXISTS "roadsdupl";
DROP TABLE IF EXISTS "poidupl";
---------------------------------------------------------------------