Find nearest point given coordinates from another table

Question

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 );

Answer 1

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);