1

I'll admit I'm a little out of my element with PostGIS and spatial geometries in a DB but here's what I'm after: I need to update a field with the determined intersected US State of a geom of an object if it doesn't yet already have one.

The DB structure is as follows:

Accomplishment

id name phys_state poly_point_line_id (fk Accomplishment_Feature)
1 Test Accomp 1 AK 123
2 Test Accomp 2 456
3 Test Accomp 3 789

Accomplishment_Feature (technically not needed in the query AFAIK but included here just in case since it is a join table between the Accomplishment and its geometry types)

id
123
456
789

Accoomplishment_Poly

id (fk to Accomplishment_Feature) geom
123 [multipolygon geometry]

Accoomplishment_Line

id (fk to Accomplishment_Feature) geom
123 [multiline geometry]

Accoomplishment_Point

id (fk to Accomplishment_Feature) geom
123 [multipoint geometry]

I need to determine the intersected US state of each of the geoms for Accomplishments that don't have a value in the physical_state column.

I currently have a table of US State geometries in another schema that I can use.

I currently have the following but it errors out and I'm obviously misunderstanding how to write the queries.

UPDATE accomplishment a
SET a.phys_state = us_state.abbrev
FROM support_gis.state_g us_state
LEFT JOIN accomplishment_poly poly on a.poly_point_line_id = poly.id
WHERE st_intersects(st_centroid(poly.geom), us_state.geom) AND a.phys_state is null

Any guidance or assistance would be greatly appreciated!

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
Vaesive
  • 105
  • 1
  • 11
  • Hey there. Do you need to check the intersection of the geometry or its centroid? – Jim Jones Sep 24 '21 at 10:01
  • *"I currently have the following but it errors out"* what does the error look like? can you also provide a sample of the geometries? – Jim Jones Sep 24 '21 at 10:11
  • 1
    @JimJones my thought was to use the centroid of the Accomplishment geom to determine the intersection of the US State in case the geom crosses boundaries of more than 1 state. The errors are SQL syntax errors I'm currently working through but this is the one I'm getting now using the query in the post: *SQL Error [42P01]: ERROR: invalid reference to FROM-clause entry for table "a" Hint: There is an entry for table "a", but it cannot be referenced from this part of the query.* – Vaesive Sep 24 '21 at 10:34

1 Answers1

2

How the FROM clause in an UPDATE statement works is slightly different than in a SELECT - go figure. An alternative is to go old school: just put all involved tables in the FROM clause and solve the joins in the WHERE clause instead using JOINs.

UPDATE accomplishment 
SET phys_state = us_state.abbrev
FROM support_gis.state_g us_state, accomplishment_poly poly
WHERE 
  ST_Intersects(ST_Centroid(poly.geom), us_state.geom) AND 
  phys_state IS NULL AND
  poly_point_line_id = poly.id;
Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • 1
    Thank you! Seems to be exactly what I needed! Sorry for the late reply; got pulled off to assist in other issue and then we started having network issues. Only thing I had to add was the accomplishment table to the FROM declaration because the query didn't like it without! Thanks again! – Vaesive Sep 28 '21 at 04:09