I have two table name servers and sites. Servers contains timing and workmode and geometry values. Where sites contains geometry values and an empty time_stamp column. I want to fill this timing column. For this, I want to check for all servers data whether it is within 9 unit distant for each sites. Hence, servers data 1 will search for all the sites and if it finds a site within 9 unit then it checks for its own work mode, if it is true then it writes the time in time_stamp column of that sites record. All the servers data with true work mode should find out one site. But there can be more sites. So, all the sites records might not update. The algorithm is like this:

FOR each server data
    FOR each sites
        IF current server data is within 9 m distant of current site AND workmode is true THEN
            sites.time_stamp <- server.timng
            break sites loop

For this I have written the following query. But not sure whether it is serving my purpose properly or not. Can anyone suggest? Should I need to add any loop here in my query?

    UPDATE sites st
SET time_stamp = timing FROM servers sv
WHERE st_distance(st.geom, sv.geom) < 9 AND sv.workmode = TRUE
  • well, did you try your query?? from seeing it I would maybe add ORDER BY timing LIMIT 1 to get only the most recent entry if there are multiple hits within the distance – ThingumaBob Feb 9 '18 at 16:08
  • Well, I tried the query. It executed, updated some rows, but not sure whether the condition worked properly or not – LSG Feb 11 '18 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.