Query using a subset of a path in Postgres
Given this table:
id | points (path) |
----+------------------------------------------+
1 | ((1,2),(3,4),(5,6),(7,8)) |
Is it possible to achieve the following using a single geometric operator and a path argument (a sequential subset of the containing path), for example ((3,4),(5,6))
?
select * from things where points @> '(3,4)' and points @> '(5,6)';
source to share
Maybe just convert it to string and match with LIKE
(you have to double because the path is closed):
select points::text from things
where (points::text || points::text) like '%(3,4),(5,6)%';
If you have a lot of things, it's worth building an index for the path to be used in a query like this (you need the trgm extension )
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX thing_text_paths ON things USING gin ( (points::text || points::text) gin_trgm_ops);
You can see it by running
SET enable_seqscan = OFF;
EXPLAIN select points::text from things
where (points::text || points::text) like '%(3,4),(5,6)%';
source to share
Postgres does not have a built-in geometric operator that fulfills assumptions. You can use arrays of points instead of paths, however this requires some preparation and superuser access.
You need to create an operator class for a type point
that allows you to compare values of that type. The whole procedure is described in this post: Create a custom "equality operator" for the PostgreSQL type (dot). Here you have a copy of my code that I need in one of my projects: Postgres point_ops class.
The array solution requires a simple function that you can use instead of an operator:
create or replace function is_subpath(point[], point[])
returns boolean language plpgsql as $$
begin
for p in 1..cardinality($1) loop
if $1[p] = $2[1] then
for s in 2..cardinality($2) loop
p:= p+ 1;
if $1[p] <> $2[s] then
return false;
end if;
return true;
end loop;
end if;
end loop;
return false;
end $$;
drop table if exists things;
create table things(
id int,
points point[]
);
insert into things values
(1, '{"(3,4)","(1,2)","(5,6)","(7,8)"}'),
(2, '{"(1,2)","(3,4)","(5,6)","(7,8)"}');
select *
from things
where is_subpath(points, '{"(3,4)","(5,6)"}'::point[]);
id | points
----+-----------------------------------
2 | {"(1,2)","(3,4)","(5,6)","(7,8)"}
(1 row)
source to share
I would say that you have created a database design problem. Not only should you use two tables.
If you followed the third normal form. Your design will have a one to large relationship to the child table.
There will be four records in the child table.
If you followed the Sql code of practice, this would not be a problem. When you put multiple data values in the same field, Sql is not designed to handle that well and this limits your kludge type solutions.
source to share