0

In QGIS, I have a table of points, and a table of areas. When I place a point in an area, I need the following to happen. Grab the ID of the area (called plat_page) the new point is being placed in. Then I want to insert the next value in a sequence into the new entry in a column called "node_id" based on what points already exist in that area. My issue is that the sequence is alpha numeric, where the first 4 characters are the plat_page, and the last three are the sequence. As well, I am unsure how to discern the area I have placed the new point in. The "node_id" looks something like this:

510C101 where 510C is the plat_page, and 101 is the number that I need to sequence.

Here is an example, I place a point in an area with plat_page 610C. I want to get the plat_page, then check what the highest value in the existing sequence is, lets say 100. Then I want to generate the next value in the sequence 610C101 and insert that into a column called "node_id".

So far I have a way to grab the highest number in the sequence for a given plat_page, but I want to automate this process by having the insertion point decide what that plat page is. As well, I am unsure how to sequence the last number, then attach it to the plat_page value to generate the "node_id". Below is the code for what I have.

SELECT MAX(RIGHT ("node_id",3)::numeric) from sewers.structures
    WHERE(
    ST_WITHIN(
        ST_CENTROID((ST_SetSRID(structures.geom, 4326))), 
        ST_SetSRID((SELECT geom FROM sewers."Qrtr_Qrtr_Sections" WHERE "plat_page" = '510D'),4326)) ) and "node_id" != 'PRIVATE'

I have also attached this fiddle with sample data to help with the understanding.

AThomspon
  • 135
  • 12

1 Answers1

1

Is it what you want? Get a containing plat_page and the next id for the structures. Omitted ST_SetSRID for simplicity assuming all geometries have the same SRID.

select qqs.plat_page,
     (select MAX(RIGHT (s2."node_id",3)::numeric) 
      from structures s2
      where ST_WITHIN(ST_CENTROID(s2.geom), qqs.geom)) + 1 id,
      s.*
from structures s 
join "Qrtr_Qrtr_Sections" qqs on ST_WITHIN(ST_CENTROID(s.geom), qqs.geom)

db<>fiddle

Note I've added an extra row to ensure at least one structure is within "Qrtr_Qrtr_Sections".

Serg
  • 22,285
  • 5
  • 21
  • 48
  • This is closer to what I want, but I am intending for it to be a trigger function. As well, I need the trigger function to auto fill the "node_id" column with an id that is 7 characters long, the first four being the plat_page information, and the last three being the sequenced number. – AThomspon Jul 21 '21 at 19:54