I am looking for a way to flag and reset a row number on rows that repeated before in the table, but only to have one row as the basis of comparison. In the example below, the first row (vertex ID 1) should be the base row for comparison until it finds a match based on the ObjID, Lat, Lon columns (vertex ID 4). Then the next row (vertex ID 5) is the basis for comparison until the next match (vertex ID 8).

Ideally, I would have another column that counts 1...4 for the first polygon and 1...4 for the second as well. I already have the SQL to count off polygon numbers (e.g. four 1s and four 2s).

As you might infer from the example, I have a list of all the points in a multipolygon but without any indicator on when the vertices start and end for each of the multipolygon shapes start other than the fact that the first and last vertices must be the same.

CodePudding user response：

You can do it with a recursion . For the sample data I assume VertexID is unique and it is a continues series of integers. Otherwise first row_number() data accordingly.

with poly as (
   select ObjID, VertexID, Lat, Lon, polyN=1, flag=0, sObjID=ObjID, sLat=Lat, sLon=Lon
   from tbl
   where VertexID =1
   
   union all 
   
   select  t.ObjID, t.VertexID, t.Lat, t.Lon, polyN   flag, 
      case when t.ObjID = p.sObjID and t.Lat = p.sLat and t.Lon = p.sLon then 1 else 0 end,
      case flag when 1 then t.ObjID else sObjID end,
      case flag when 1 then t.Lat else sLat end,
      case flag when 1 then t.Lon else sLon end
 
   from poly p
   join tbl t on t.VertexID = p.VertexID   1
)
select ObjID, VertexID, Lat, Lon, polyN
from poly