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.
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;