1

My question is similar to this question How to create a PostgreSQL partitioned sequence?

In My case am converting polygon vertex to points using this code

SELECT geom, gid, path,nextval('seq_oid1') as seq_id,
rank() OVER (PARTITION BY gid ORDER BY gid) AS pos
FROM (
SELECT DISTINCT ON (geom) geom, path, gid
FROM (
SELECT (ST_DumpPoints(geom)).geom, (ST_DumpPoints(geom)).path, gid
FROM edge_snapping.polygon1
) f
ORDER BY geom, path, gid
) f
ORDER BY gid, path;

But this code generates sequence id for all the vertices regardless of polygon id.

I want to index the points based on partitioned by polygon id, and the points generated from each polygons should start with sequence id with 1. I appreciate any help. As i am not sure what to do in this case.

Community
  • 1
  • 1
Deepan Kaviarasu
  • 125
  • 1
  • 10

1 Answers1

1
SELECT geom, gid, path,nextval('seq_oid1') as seq_id,
rank() OVER (PARTITION BY gid ORDER BY gid) AS pos
FROM (
SELECT DISTINCT ON (geom) geom, path, gid
FROM (
SELECT (ST_DumpPoints(geom)).geom, (ST_DumpPoints(geom)).path, gid
FROM edge_snapping.polygon1
) f
ORDER BY geom, path, gid
) f
WHERE pos = 1 ORDER BY gid, path;
lat long
  • 920
  • 6
  • 16
  • ERROR: column "pos" does not exist , It gives an error like this. – Deepan Kaviarasu May 04 '17 at 14:51
  • 1
    SELECT * FROM (SELECT geom, gid, path,nextval('seq_oid1') as seq_id, rank() OVER (PARTITION BY gid ORDER BY gid) AS pos FROM ( SELECT DISTINCT ON (geom) geom, path, gid FROM ( SELECT (ST_DumpPoints(geom)).geom, (ST_DumpPoints(geom)).path, gid FROM edge_snapping.polygon1 ) f ORDER BY geom, path, gid ) f ORDER BY gid, path ) f3 WHERE pos = 1 – lat long May 05 '17 at 13:09