Thread: "IN" in a geometric database using data type "point"

"IN" in a geometric database using data type "point"

From
Romain Billoir
Date:
Hi, i'm trying to request a database using data type "point" using keyword "IN" with a list of point generated by PHP, like this:
'SELECT * FROM map WHERE position IN ((point(-1,-1), (point(1,-1), point(1,1), point(-1,1))'
but this request returns me an error: operator doesn't exist point = point.
so i tried to use a path unstead of list of point like this:
'SELECT * FROM map WHERE position <@ path '(point(-1,-1), (point(1,-1), point(1,1), point(-1,1))'
but this request returns me the point 0,0???

Is anybody have a suggestions?

Thanks.

Re: "IN" in a geometric database using data type "point"

From
Andrew Hunter
Date:
Hi Romain,

Assuming you have PostGIS installed and position is defined as a point or multpoint ADT then you could use the ST_Intersect operator

SELECT *
FROM map
WHERE ST_Intersects(position, 'MULTIPOINT(-1 -1,1 -1,1 1,-1 1)');

Regards

Andrew


On 2010-09-26, at 12:27 PM, Romain Billoir wrote:

Hi, i'm trying to request a database using data type "point" using keyword "IN" with a list of point generated by PHP, like this:
'SELECT * FROM map WHERE position IN ((point(-1,-1), (point(1,-1), point(1,1), point(-1,1))'
but this request returns me an error: operator doesn't exist point = point.
so i tried to use a path unstead of list of point like this:
'SELECT * FROM map WHERE position <@ path '(point(-1,-1), (point(1,-1), point(1,1), point(-1,1))'
but this request returns me the point 0,0???

Is anybody have a suggestions?

Thanks.


Andrew Hunter PEng RPSurv PhD
Assistant Professor
Department of Geomatics Engineering
Schulich School of Engineering
University of Calgary

T: +403.220.7377
F: +403.284.1980
E: ahunter (at) ucalgary (dot) ca
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~





Re: "IN" in a geometric database using data type "point"

From
Tom Lane
Date:
Romain Billoir <billoirr@gmail.com> writes:
> Hi, i'm trying to request a database using data type "point" using keyword
> "IN" with a list of point generated by PHP, like this:
> 'SELECT * FROM map WHERE position IN ((point(-1,-1), (point(1,-1),
> point(1,1), point(-1,1))'
> but this request returns me an error: operator doesn't exist point = point.

For historical reasons the equality operator for points is named ~=
... which IN doesn't understand.  I think your best bet is to spell
it out instead of using the IN shorthand:

SELECT * FROM map WHERE position ~= point(-1,-1) or position ~= point(1,-1)
or position ~= point(1,1) ...

            regards, tom lane