| 123456789101112131415 |
- DROP TABLE IF EXISTS :schema.nodes CASCADE;
- CREATE TABLE :schema.nodes AS
- WITH all_nodes AS (
- SELECT ST_StartPoint(geom) AS geom FROM :schema.links
- UNION
- SELECT ST_EndPoint(geom) AS geom FROM :schema.links
- ),
- distinct_nodes AS (
- SELECT DISTINCT geom FROM all_nodes
- )
- SELECT ROW_NUMBER() OVER () AS id, geom FROM distinct_nodes;
- CREATE INDEX IF NOT EXISTS nodes_geom_idx ON :schema.nodes USING GIST (geom);
- ALTER TABLE :schema.nodes ADD PRIMARY KEY (id);
|
