2

This question already has an answer here:

So I have two tables, one table is called stores which has the name of the store as well as the geom column that contains the coordinates for the stores. The other table is customers which has the coordinates of the customer's address.

I'm trying to create a new column in the customer table with the nearest store given the customer's coordinates. I've tried something like this but it doesn't work:

UPDATE customers SET storename = SELECT storename FROM stores ORDER BY geom <-> ST_GeometryFromText('POINT(customers.geom)',4326) LIMIT 1 );

marked as duplicate by underdark Oct 21 '16 at 22:34

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 1
    Can you elaborate on what doesn't work? Is there any error message? – tilt Jan 15 '16 at 20:43
  • @underdark I'm not sure the duplicate flag on this question is correct. – kttii Oct 26 '16 at 15:24
3

There are a few issues with your statement:

  1. There is a missing parenthesis before your SELECT (it matches the one just before the semi-colon).
  2. It appears you are unnecessarily converting the customers.geom to a point set as 4326 SRID.
  3. Are you wanting to transform or set the SRID for customers.geom? I have two solutions below to cover either answer.

This statement assumes that the customers' geom is in a different SRID than the stores' geom:

UPDATE customers SET storename = (
    SELECT storename 
    FROM stores 
    ORDER BY geom <-> ST_Transform(customers.geom,4326) LIMIT 1
    );

And this statement assumes that the customers' geom is the same as the stores' geom:

UPDATE customers SET storename = (
    SELECT storename 
    FROM stores 
    ORDER BY geom <-> customers.geom LIMIT 1
    );

You also mentioned that you are trying to create a new column... that column must exist before you run an UPDATE statement. Here is an example of how to add a column to your table:

ALTER TABLE customers ADD COLUMN storename CHARACTER VARYING(50);

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