我的 PostgreSQL 14.7 数据库中有一个表public.polygon_versions
:
CREATE TABLE public.polygon_versions (
id bigint NOT NULL DEFAULT nextval('polygon_versions_id_seq'::regclass),
entity_id bigint NOT NULL,
creation_transaction_id bigint NOT NULL,
obsolescence_transaction_id bigint,
geo_json json NOT NULL,
CONSTRAINT polygon_versions_pkey PRIMARY KEY (id),
CONSTRAINT polygon_versions_creation_transaction_id_foreign FOREIGN KEY (creation_transaction_id)
REFERENCES public.transactions (id),
CONSTRAINT polygon_versions_entity_id_foreign FOREIGN KEY (entity_id)
REFERENCES public.polygons (id),
CONSTRAINT polygon_versions_obsolescence_transaction_id_foreign FOREIGN KEY (obsolescence_transaction_id)
REFERENCES public.transactions (id)
);
示例geo_json
数据是:
{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[30.3626584543099,50.43004834913466],[30.37021155489584,50.4281893337737],[30.36969657076498,50.433110097683176],[30.369009925257167,50.433984846616404],[30.3626584543099,50.43004834913466]]]},"properties":{}}
我正在尝试查询字段与geo_json
某些坐标匹配的所有记录。例如:30.504106925781265、50.43731985018662 我试过以下 SQL 查询:
SELECT *
FROM polygon_versions
WHERE geo_json LIKE '%30.504106925781265,50.43731985018662%'
它返回一个错误:
ERROR: operator does not exist: json ~~ unknown
LINE 1: SELECT * FROM polygon_versions WHERE geo_json LIKE '%30.5041...
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 47