I am trying to match a geometry of type Polygon to Point using ST_Contains:
UPDATE events
SET country = countries.geonameid
FROM countries
WHERE ST_Contains(countries.geometry, events.location)
The country table was created as follows:
CREATE TABLE countries (
geonameid INTEGER PRIMARY KEY,
geometry GEOMETRY(Geometry, 4326)
)
INSERT INTO countries (geonameid, geometry VALUES (%s, ST_GeometryFromText(%s, 4326)))
and the event table like this:
CREATE TABLE events
(
id BIGINT PRIMARY KEY,
location GEOMETRY(Point, 4326),
country INT
)
INSERT INTO events (id, location) VALUES (%s, ST_GeomFromText('POINT(%s %s)', 4326)) ON CONFLICT DO NOTHING"
Unfortunately on the update query I obtain the following error:
psycopg2.InternalError: Unknown geometry type: -671088639 - Point
GEOMETRY(Geometry, 4326})
. I suggest using a more specific geometry type, such asgeometry(MultiPolygon, 4326)