CODEWITHSUNDEEP
mysqlsqlgeospatialspatial-query
Stev_k
Stev_k

Reputation: 2126

update mysql table with results from spatial query

I am using mysql to count the number of point features (shops) within a polygon feature (postsec).

The select query I have works fine, but I want to update table postsec with the generated results. The query I have is below:

select pc_sec, count(*) as count from shops 
join postsec on st_within(shops.SHAPE,postsec.SHAPE) 
where shops.shop_cat > 0 and shop_cat < 10 group by pc_sec

I have tried a number of methods for doing this e.g. update postsec set new_column = ([select query]) but can't seem to find the right way.

Upvotes: 0

Views: 84

Answers (1)

juergen d
juergen d

Reputation: 204884

Something like this should work

update postsec 
inner join 
(
   select pc_sec, count(*) as pseccount 
   from shops 
   join postsec on st_within(shops.SHAPE,postsec.SHAPE) 
   where shops.shop_cat between 1 and 9
   group by pc_sec
) x on x.pc_sec = postsec.id
set count_column = x.pseccount

Upvotes: 1

Related Questions