Find rows with given coordinates nested in a JSON column
0
votes
2
answers
177
views
I have a table
public.polygon_versions
in my PostgreSQL 14.7 database:
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)
);
Example geo_json
data is:
~~~none
{"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":{}}
~~~
I'm trying to query all records where field geo_json
matches some coordinates. For example: 30.504106925781265, 50.43731985018662
I've tried the following SQL query:
SELECT *
FROM polygon_versions
WHERE geo_json LIKE '%30.504106925781265,50.43731985018662%'
It returns an error:
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
Asked by Taras
(167 rep)
Apr 11, 2023, 02:32 PM
Last activity: Apr 12, 2023, 09:29 AM
Last activity: Apr 12, 2023, 09:29 AM