How to create a PostgreSQL partitioned sequence for points generated from polygon vertices?

Question

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.

lat long · Accepted Answer · 2017-05-04 13:35:54Z

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;

answered May 4, 2017 at 13:35

lat long

9306 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Deepan Kaviarasu Over a year ago

ERROR: column "pos" does not exist , It gives an error like this.

lat long Over a year ago

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

Collectives™ on Stack Overflow

How to create a PostgreSQL partitioned sequence for points generated from polygon vertices?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related