1

I am trying to populate a column in a table from another table using Spatialite. My query will not populate the column, and I can't seem to identify what is wrong. I modeled the query from the following post: http://www.surfaces.co.il/wp-content/uploads/2012/05/SL_update3.png

What I would like to achieve is to have the corresponding values from lu_grid.gridid to populate lu_geophotos.gridid where lu_geophotos intersects with lu_grid. (A column in a point layer should get the values from a polygon where the points intersect the polygon)

Here is my code:

UPDATE lu_geophotos SET gridid = (SELECT GRIDID
FROM lu_grid AS s
 WHERE ST_WITHIN (s.Geometry, lu_geophotos.Geometry)
 AND s.ROWID IN (SELECT ROWID
  FROM SpatialIndex
  WHERE f_table_name = 'lu_grid' AND search_frame = lu_geophotos.Geometry)
);

enter image description here

Improve this question
12
  • What does SELECT GRIDID FROM lu_grid WHERE ST_Within(lu_grid.Geometry, lu_geophotos.Geometry) LIMIT 5; give you? The spatial index probably isn't needed because you probably don't have enough entries (10K+) to make it worth the effort.
    – BradHards
    Commented Apr 7, 2014 at 22:06
  • It returns an error. SQL error: no such column: lu_geophotos.Geometry The table and the geometry exists
    – Ryan Garnett
    Commented Apr 7, 2014 at 22:26
  • Sorry. SELECT lu_geophotos.GRIDID FROM lu_geophotos, lu_grid WHERE ST_Within(lu_grid.Geometry, lu_geophotos.Geometry) LIMIT 5;
    – BradHards
    Commented Apr 7, 2014 at 23:21
  • Thanks Brad. It executed, but nothing is being written to the table. Its the same thing I was noticing with my code
    – Ryan Garnett
    Commented Apr 7, 2014 at 23:42
  • 1
    That wasn't mean to update anything, just to show matching records. What do the matches look like? If none, your data doesn't look the way you think it does. What do SELECT AsEwkt(Geometry) FROM lu_grid LIMIT 5; and SELECT AsEwkt(Geometry) FROM lu_geophotos LIMIT 5; show? You might want to add that to the question.
    – BradHards
    Commented Apr 7, 2014 at 23:48

1 Answer 1

Reset to default
1

I found the issue related to the query. I removed everything after ST_Within, and re-ordered the layers (I had the grid first, and the points second, and they should have been reversed). See the code below:

UPDATE lu_geophotos

SET gridid = (SELECT GRIDID FROM lu_grid

WHERE ST_Within(lu_geophotos.Geometry, lu_grid.Geometry))

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.