Search code examples
postgresqlpostgis

How to compare two tables with coordinates and write true of false in column of first table?

I have two tables. In one of the coordinates of the cities, and in the other the coordinates of the delivery zones.

How can I compare the coordinates of cities in one of the delivery zones and write 1 or 0 in the column with the names of the zones? If the city at least partially falls into the delivery range, then this should be 1.

I am not very good at programming. I think I need to use code like this: boolean && (geometry A, geometry B);

Or some of this: boolean ST_Contains (geometry geomA, geometry geomB);

But I do not understand enter image description herehow to specify another table. And how to write the results in the column of the table with cities.

Please help with the code! Thank!

UPD I need to compare two zones. Is the city zone included in the delivery zone? If it is included or partially included, then specify 1 in the column with the name of the zone. If not included, then 0. Screen text exemple

Screen of tables

Solution

  • It would be a query approximately like this:

    UPDATE cities
   SET green_area = z.ga::integer,
   SET red_area = z.ra::integer
FROM (SELECT place_id,
             bool_or(cities.geom && zones.geom)
                FILTER (WHERE zones.name = 'green_area') AS ga,
             bool_or(cities.geom && zones.geom)
                FILTER (WHERE zones.name = 'red_area') AS ra
      FROM zones
         JOIN cities ON cities.geom && zones.geom
      GROUP BY cities.place_id
     ) AS z
WHERE cities.place_id = z.place_id;

    && is the “overlaps” operator for geometries.