Sample Header Ad - 728x90

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