[postgis-users] RE: Select Point or PolyGon Polyline in Box

Obe, Regina robe.dnd at cityofboston.gov
Fri Apr 13 05:48:30 PDT 2007


Hi anhtin, 

In your province table, is your province the_geom a polygon or a point?
That example I gave would work only for points since if the bounding box
of a point is in a box, then the point is in the box.  With a polygon -
you could have a province partly in your box and partly out and also
since && only compares bounding boxes, for a polygon just because the
bounding box of the polygon is in your box doesn't guarantee your
polygon is in your box.  Its hard to tell without having a sample of the
records you have so maybe you can send us the 2 that are coming up in
the results so we could help further.

To figure out if a polygon truly overlaps your box, then you would need
to do

SELECT gid, name, code, ma FROM province
  WHERE  the_geom && setsrid('BOX(472825.36726087
 2140555.40234783,516561.185 2107753.53904348)'::box2d, 42102)
	AND distance(the_geom, setsrid('BOX(472825.36726087
 2140555.40234783,516561.185 2107753.53904348)'::box2d, 42102)) = 0

Now for the case where you have more than one province overlapping your
box - it is questionable if you want to include both or if you just want
to include the one that fits in better - in that case, you might want to
pick the province where the centroid of the province fits in to get
around silly cases like where a sliver of the province is in your box.
E.g.

 SELECT gid, name, code, ma FROM province
  WHERE  centroid(the_geom) && setsrid('BOX(472825.36726087
 2140555.40234783,516561.185 2107753.53904348)'::box2d, 42102)

Hope that helps,
Regina





-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
anhtin
Sent: Friday, April 13, 2007 5:38 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] RE: Select Point or PolyGon Polyline in Box




anhtin wrote:
> 
> thanks reply 
> but have some problem
> i think the command doen't exact. because when i get box on the map
have a
> porvince but when i run in post have 2 result ???
> 
> SELECT gid, name, code, ma FROM province
>  WHERE  the_geom && setsrid('BOX(472825.36726087
> 2140555.40234783,516561.185 2107753.53904348)'::box2d, 42102)
> 
> 
> when u show me ex: with point is excatly
> SELECT *
> FROM province WHERE GeomFromText('POINT(472825.36726087
> 2140555.40234783)', 42102) && the_geom
> AND distance(the_geom, GeomFromText('POINT(472825.36726087
> 2140555.40234783)', 42102)) = 0
> 
> but i try same with Box is error 
> can u show me???
> 
> 
> Obe, Regina     DND\MIS wrote:
>> 
>> Assuming your box is in the same srid as your points.  It would be
>> something like
>> 
>> SELECT *
>> FROM pointdemo
>> WHERE  the_point && setsrid('BOX(295149 2315499, 465992
>> 2163790)'::box2d, -1)
>> 
>> 
>> Note - above I am assuming your point geometry is called the_point
and
>> your SRID is -1
>> If the srid of your point geometry is not -1 then change  -1 to what
it
>> should be.
>> 
>> 
>> -----Original Message-----
>> From: postgis-users-bounces at postgis.refractions.net
>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
>> anhtin
>> Sent: Tuesday, April 10, 2007 6:18 AM
>> To: postgis-users at postgis.refractions.net
>> Subject: [postgis-users] Select Point or PolyGon Polyline in Box
>> 
>> 
>> hi all
>> i have question to postgis
>> i have table Pointdemo
>> I want select all point in my Box("Rectangle") on PostGis
>> my box is corrdinate 
>> ex: full extend is:Box(110638 2587481, 903350 1972446) ---> xmin
ymin,
>> xmax
>> ymax
>> i want select point in Box(295149 2315499, 465992 2163790)  ---> xmin
>> ymin,
>> xmax ymax
>> How will i do that ?????
>> 
>> 
>> -- 
>> View this message in context:
>>
http://www.nabble.com/Select-Point-or-PolyGon-Polyline-in-Box-tf3552167.
>> html#a9916986
>> Sent from the PostGIS - User mailing list archive at Nabble.com.
>> 
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> 
>> -----------------------------------------
>> The substance of this message, including any attachments, may be
>> confidential, legally privileged and/or exempt from disclosure
>> pursuant to Massachusetts law. It is intended
>> solely for the addressee. If you received this in error, please
>> contact the sender and delete the material from any computer.
>> 
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> 
>> 
> 
> 

-- 
View this message in context:
http://www.nabble.com/Select-Point-or-PolyGon-Polyline-in-Box-tf3552167.
html#a9975872
Sent from the PostGIS - User mailing list archive at Nabble.com.

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users



More information about the postgis-users mailing list