Groups > comp.databases.postgresql > #890

Postgres geometry type operation error in CrudRepository custom sql query

X-Received	by 2002:ae9:edc4:: with SMTP id c187mr3313634qkg.369.1585389191698; Sat, 28 Mar 2020 02:53:11 -0700 (PDT)
X-Received	by 2002:a25:d9c5:: with SMTP id q188mr4718977ybg.426.1585389191412; Sat, 28 Mar 2020 02:53:11 -0700 (PDT)
Path	csiph.com!xmission!news.snarked.org!border2.nntp.dca1.giganews.com!border1.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups	comp.databases.postgresql
Date	Sat, 28 Mar 2020 02:53:11 -0700 (PDT)
Complaints-To	groups-abuse@google.com
Injection-Info	google-groups.googlegroups.com; posting-host=2a02:587:6e13:2b00:31b1:9c00:8769:f8a6; posting-account=XXd2XQoAAABZv_uFgzP-bWhhBsxTr9gN
NNTP-Posting-Host	2a02:587:6e13:2b00:31b1:9c00:8769:f8a6
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<3371e5b6-53c1-41b4-8bc6-7c6d982fcc69@googlegroups.com> (permalink)
Subject	Postgres geometry type operation error in CrudRepository custom sql query
From	chatzich@gmail.com
Injection-Date	Sat, 28 Mar 2020 09:53:11 +0000
Content-Type	text/plain; charset="UTF-8"
Content-Transfer-Encoding	quoted-printable
Lines	36
Xref	csiph.com comp.databases.postgresql:890

Show key headers only | View raw

I have two WGS 84 points p1(lonul, latul) the up left point and p2(lonbr, latbr) the bottom right point and I want to see which cylinder is intersecting with the rectangle which is defined by these to so I made the following query to a CrudRepository:


CREATE TABLE protected_area
(
    id            serial not null primary key,
    constraint_id integer not null,
    radius        float not null,
    height        float not null,
    coordinates   path not null,
    gtype         geometrytype not null,
    name          varchar(50),
);


@Query("SELECT u FROM ProtectedArea u WHERE u.gtype='Cylinder' AND polygon( box(point(:lonul - (180/pi()) * (u.radius/(6378137*cos(pi()*:lonul/180))), :latul - (180/pi()) * (u.radius/6378137)), point(:lonbr + (180/pi()) * (u.radius/(6378137*cos(pi()*:lonbr/180))),:latbr + (180/pi()) * (u.radius/6378137)))) @> polygon(coordinates)")

it seems that the operator @> which indicates if the geometry is contained to another

but I am taking this error

> Caused by: org.hibernate.QueryException: unexpected char: '@' [SELECT
> u FROM ProtectedArea u WHERE u.gtype='Cylinder' AND polygon(
> box(point(:lonul - (180/pi()) *
> (u.radius/(6378137*cos(pi()*:lonul/180))), :latul - (180/pi()) *
> (u.radius/6378137)), point(:lonbr + (180/pi()) *
> (u.radius/(6378137*cos(pi()*:lonbr/180))),:latbr + (180/pi()) *
> (u.radius/6378137)))) @> polygon(coordinates)]

Back to comp.databases.postgresql | Previous | Next | Find similar

Thread

Postgres geometry type operation error in CrudRepository custom sql query chatzich@gmail.com - 2020-03-28 02:53 -0700

csiph-web